session B:
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update tb_book set book_name = '绝代双雄' where book_id = 5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into tb_book values (6, '圆月弯刀', '古龙'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb_book; +---------+-----------------------+--------+ | book_id | book_name | author | +---------+-----------------------+--------+ | 1 | 多情刀客无情刀 | 古龙 | | 2 | 笑傲江湖 | 金庸 | | 3 | 倚天屠龙记 | 金庸 | | 4 | 射雕英雄传 | 金庸 | | 5 | 绝代双雄 | 古龙 | | 6 | 圆月弯刀 | 古龙 | +---------+-----------------------+--------+ 6 rows in set (0.00 sec)结果:事务B已提交的修改记录(即绝代双骄修改为绝代双雄)在事务A中是不可见的,说明该事务隔离级别下解决了上面不可重复读的问题,但魔幻的是一开始事务A中虽然读不到事务B中的新增记录,却可以更新这条新增记录,执行更新(update)后,在事务A中居然可见该新增记录了,这便产生了所谓的幻读问题。
为什么会出现这样莫名其妙的结果? 别急,后文会慢慢揭开这个神秘的面纱。先看如何解决幻读问题。
串行化(serializable)serializable事务隔离级别可以避免幻读问题,但会极大的降低数据库的并发能力。
SERIALIZABLE: the isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished.
操作:
session A事务隔离级别设置为serializable并开启事务,并查询book列表,不提交事务;
然后session B中分别执行insert、delete、update操作
session A:
mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb_book; +---------+-----------------------+--------+ | book_id | book_name | author | +---------+-----------------------+--------+ | 1 | 多情刀客无情刀 | 古龙 | | 2 | 笑傲江湖 | 金庸 | | 3 | 倚天屠龙记 | 金庸 | | 4 | 射雕英雄传 | 金庸 | | 5 | 绝代双雄 | 古龙 | | 6 | 圆月弯刀 | 古龙 | +---------+-----------------------+--------+ 6 rows in set (0.00 sec)session B:
mysql> insert into tb_book values (7, '神雕侠侣', '金庸'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> delete from tb_book where book_id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tb_book set book_name = '绝代双骄' where book_id = 5; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction结果:只要session A中的事务一直不提交,session B中尝试更改数据(insert、delete、update)的事务都会被阻塞至超时(timeout)。显然,该事务隔离级别下能有效解决上面幻读、不可重复读、脏读等问题。
注意:除非是一些特殊的应用场景需要serializable事务隔离级别,否则很少会使用该隔离级别,因为并发性极低。
事务隔离级别小结 事务隔离级别 脏读 不可重复读 幻读read uncommitted 可能 可能 可能
read committed 不可能 可能 可能
repeatable read 不可能 不可能 可能
serializable 不可能 不可能 不可能
MVCC机制
上面在演示幻读问题时,出现的结果让人捉摸不透。原来InnoDB存储引擎的默认事务隔离级别可重复读(repeatable read),是通过 "行级锁+MVCC"一起实现的。这就不得不去了解MVCC机制了。
相关文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html
参考:
《MySQL中MVCC的正确打开方式(源码佐证)》 https://blog.csdn.net/Waves___/article/details/105295060
《InnoDB事务分析-MVCC》的事务分析-MVCC/
《Innodb中的事务隔离级别和锁的关系》 https://tech.meituan.com/2014/08/20/innodb-lock.html
MVCC概念