通过pymysql程序debug学习数据库事务、隔离级别

问题

今天在使用pymysql连数据库的时候,出现了一个bug,查询数据库某个数据,但是在我在数据库中执行sql语句改变数据后,pymsql的查询依然没有发生改变。
代码如下:

# 5.6.10
conn = pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PSWD, db=DB)


def fetch():
    cursor = conn.cursor()
    sql = "SELECT * FROM hello"
    try:
        res = cursor.execute(sql)
    except:
        pass
    finally:
        cursor.close()
    for data in cursor.fetchall():
        print(*data)


while True:
    fetch()
    time.sleep(2)

解决问题

首先,我们还是找出问题原因,并解决它,查阅相关文档后可知,因为我们的查询语句执行后,没有commit(),这会导致查询事务没有提交,mysql数据库会返回上次查询到的结果。
所以,不管是增删查改,最好都以事务的形式提交!

    try:
        res = cursor.execute(sql)
        conn.commit()
    except:
        pass
    finally:
        cursor.close()

分析

接下来我们来仔细分析,为什么查询也需要提交事务

  • 数据库的事务
  • 脏读、不可重复读、幻读
  • 数据库事务隔离级别
  • 数据库的锁

1.数据库的事务特性

先简单了解数据库事务的特性

  1. 原子性:原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生要么都不发生。
  2. 一致性:如果事务执行之前数据库是一个完整性的状态,那么事务结束后,无论事务是否执行成功,数据库仍然是一个完整性状态.
    数据库的完整性状态:当一个数据库中的所有的数据都符合数据库中所定义的所有的约束,此时可以称数据库是一个完整性状态.
  3. 隔离性:事务的隔离性是指多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间事务要隔离
  4. 持久性:持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

2.脏读、不可重复读、幻读

脏读(读取未提交的数据)

转账 取钱
事务开始
事务开始
查看余额为2000
取钱1000
查余额为1000
未知错误,事务回滚,余额为2000
转账2000,余额为3000(脏读1000+2000)
事务提交

所以莫名其妙就少了1000块钱

不可重复读(两次读取结果不一致)

拿购物和取钱说事,有天小A去取钱,看余额有2000块(事务开始),很开心,此时她老婆看到喜欢的东西,手速极快的下单,付款2000(其他事务提交完成),这时小A到ATM取1000块钱,ATM提示余额不足!小A感到很疑惑,刚才明明还有2000的啊?

取钱 购物
事务开始
查看余额为2000
事务开始
消费2000
事务提交
再次查询余额为0
事务结束

幻读(多次读取,总量不一样)

这天,小A查自己这个月的账单(事务开始),发现5笔购物总计消费1000块,这时,他老婆又眼疾手快的下单付款买了一件衣服2000块(其他事务结束),这时,小A再看他的账单,总计消费变成了3000块,就像产生幻觉一样(事务结束)

查账 购物
事务开始
查看账单为1000
事务开始
增加一笔账单2000
事务提交
再次查看账单为3000
事务结束

不可重复读和幻读的区别

可能到这,大概了解了3种读取数据会出现的异常情况了,但可能对不可重复读和幻读有疑问,似乎差不多啊。
可以这么理解,不可重复读是针对于数据库表的某条记录而言,也就是针对update一些。解决办法例如:我们可以在读取事务进行的时候对该条记录加锁,以避免重复读不一致的问题。
幻读是针对多条记录而言,针对insert,delete一些,在同一事务两次查询结果数目不一致。解决办法例如:我们可以在读取事务进行的时候对整个表加锁,以避免。

3.数据库的隔离级别

数据库的隔离级别,由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ,这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题。
√可以避免,×不能避免

脏读 不可重复读 幻读
Read uncommitted × × ×
Read committed × ×
Repeatable read ×
Serializable

而mysql默认为 Repeatable read,Sql Server , Oracle默认为 Read committed
到这里,基本可以完结今天的bug原因了,mysql可以避免重复读的问题的,它并不是通过前面提到的加锁来控制的,而是,同一事务的查询结果都是事务开始的时候保存的快照,所以如果不commit,查询结果不会改变!

4.数据库的锁

还想继续深究数据库是如何加锁来保证事务的四大特性的。有时间一定去了解,到时候来更新。。。哈哈
InnoDB锁机制

posted @ 2019-02-14 15:51  happy_codes  阅读(415)  评论(0编辑  收藏