简介
本文介绍MySQL的事务隔离级别的含义,并用示例说明各个级别会出现的问题:脏读、不可重复读、幻读。
MySQL有四种隔离级别:未提交读,提交读,可重复读,序列化。
事务的隔离级别是Java后端面试题中经常会问到的问题。
概述
隔离级别说明
高一级的级别提供更强的隔离性。标准允许事务运行在更强的事务隔离级别上。(如在可重复读(REPEATABLE READ)隔离级别上执行提交读(READ COMMITTED)的事务是没有问题的)。
以上除了序列化读以外,其他3种隔离级别都允许对同一条记录进行读-读、读-写、写-读的并发操作,如果我们不允许读-写、写-读的并发操作,可以使用SERIALIZABLE隔离级别。
隔离级别含义
√ 为会发生,×为不会发生。各个级别详细讲解见下方
隔离级别 | 英文名称 | 含义 | 脏读 | 不可重复读 | 幻读 |
未提交读 | READ UNCOMMITTED | 可读取其它事务未提交的结果 | √ | √ | √ |
提交读 | READ COMMITTED | 一个事务开始时,只能读到其他事务已经提交的修改。 例:如果A事务已经修改了XX,但还没提交,则B事务读XX时还是未修改的值。 (Oracle等多数数据库默认是该级别)。 | × | √ | √ |
可重复读 | REPEATABLE READ | 在开启事务时,同一条件的查询返回的结果是一样的。 例:A事务开启,查询一条记录;B事务更新这条记录并提交,A事务再次查询这条记录(查到的结果跟第一次查到的一样,而不是B修改过的结果)。 (MySQL默认级别) | × | × | √ |
可序列化 | SERIALIZABLE | 和repeatable-read类似。 不同点:若auto commit为false,那么每一条SELECT语句会自动被转化为SELECT ... LOCK IN SHARE MODE,这样会出现阻塞(如果别的事务已经修改了本事务要读的行,则本SELECT会阻塞(写加x锁,读加s锁))。 (也有的地方说:直接加表锁) 英文:This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled,the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.) | × | × | × |
说明:对于Spring,这些级别基本对应,其级别英文为:ISOLATION_DEFAULT(用底层数据库默认的隔离级别)、ISOLATION_READ_UNCOMMITTED、ISOLATION_READ_COMMITTED、ISOLATION_REPEATABLE_READ、ISOLATION_SERIALIZABLE
脏读、不可重复读、幻读的含义
名词 | 含义 |
脏读 | 一个事务正在访问数据,并且对数据进行了修改,而这修改还没有提交到数据库中,这时,另外一个事务也访问这个修改过的数据,然后使用了这个修改过的数据。 |
不可重复读 | 一个事务内两个相同的查询却返回了不同数据。 数据不同原因:数据被更改(修改或删除(UPDATE或DELETE))。 解决方法:对操作的数据添加行级锁,防止操作中的数据发生变化 |
幻读 | 指事务在插入事先检测不存在的记录时,惊奇的发现这些数据已经存在了(其他事务插入了)。 有的地方说:若是修改过来的,不算幻读。(PhantomRow)。 解决方法:InnoDB下:重复读或序列化级别+加锁读(使用的锁是:Next-Key Lock(行锁与间隙锁的结合)) 即:使用FOR UPDATE或者LOCK IN SHARE MODE。 (Oracle需要在SERIALIZABLE的事务隔离级别下才能解决 Phantom Problem。) |
隔离级别命令
注意:下边是mysql8之前的命令。mysql8及之后,tx_isolation改为了transaction_isolation
作用 | 命令 | 示例 |
查看当前数据库事务状态 | select @@隔离级别相关的系统变量 //其他变量见:官方文档:server-system-variables 或者: show variables like 隔离级别相关的系统变量 | 全局隔离级别: SELECT @@global.tx_isolation; 当前会话隔离级别: 或者 SELECT @@tx_isolation; 或者select variables like "tx_isolation"; |
查看自动提交状态 | select @@autocommit; //mysql默认是自动提交事务的 | set autocommit = 0; //使不自动提交事务 |
修改Mysql的事务隔离级别 | SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 或者: set tx_isolation="隔离级别" //设置本会话隔离级别 隔离级别:"read-uncommitted","read-committed","repeatable-read","serializable" | 修改Mysql的事务隔离级别为Read uncommitted 法1:SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 法2:set tx_isolation = 'read-uncommitted'; |
实例
未提交读(导致脏读示例)
简要说明
通过结果发现:A连接,开启事物,修改数据,并未提交,B连接在read-uncommitted的隔离模式下仍然可以看到已更新的数据。
这就是脏读。
详细示例
开两个MySQL对话框进行测试:
会话A | 会话B |
mysql> create table tb1(id int unsigned not null)Engine=InnoDB; | |
mysql> insert into tb1 values(1); Query OK, 1 row affected (0.05 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> update tb1 set id='2'; Query OK, 1 row affected (0.13 sec) Rows matched: 1 Changed: 1 Warnings: 0 | |
mysql> select * from tb1; +------+ | id | +------+ | 2 | +------+ 1 row in set (0.00 sec) | |
mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec) | |
mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.00 sec) | |
mysql> select * from t1; +------+ | id | +------+ ---------------出现脏读 | 2 | +------+ 1 row in set (0.00 sec) |
通过结果发现。A连接,开启事物,修改数据,并未提交,B连接在read-uncommitted的隔离模式下仍然可以看到已更新的数据。
如果会话B开启事务,然后会话A开启事务并修改数据,会话B读取数据,得到的也是已经更新的数据。
A连接窗口输入rollback回滚数据,此时A,B连接所查询的数据,和初始时的数据是一样的。
提交读(导致不可重复读示例)
简要说明
从结果可以看出B连接读取到A连接事务内提交的更新数据。这就是所谓的:不可重复读。
详细示例
开两个MySQL对话框进行测试:
会话A | 会话B |
mysql> set tx_isolation = 'read-committed'; Query OK, 0 rows affected, 1 warning (0.00 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> update tb1 set id='2'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) | |
mysql> commit; Query OK, 0 rows affected (0.13 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) |
由此可见,会话B在会话A提交前后读到的数据是不一样的,这就是不可重复读。
提交读(导致的幻读示例)
一个事务在执行时,另一个事务可以对同一个表进行插入操作。
会话A | 会话B |
mysql> create table tb1(id int unsigned not null)Engine=InnoDB; mysql> insert into tb1(id) values(1), (3), (5); | |
mysql> set tx_isolation = 'read-committed'; Query OK, 0 rows affected, 1 warning (0.00 sec) | |
start transaction; | |
mysql> select * from tb1 where id > 3 for update; +----+ | id | +----+ | 5 | +----+ 1 row in set (0.00 sec) | |
start transaction; | |
insert into tb1 values(4); | |
mysql> select * from tb1 where id > 2 for update; -------在此阻塞,只有光标闪烁 | |
commit | |
本处是上一个命令退出了阻塞 mysql> select * from tb1 where id > 2 for update; | |
再执行一次也是同样的结果 mysql> select * from tb1 where id > 2 for update; |
可重复读(导致的可重复读示例)
说明
一个事务只能读到另一个已经提交的事务修改过的数据,但是第一次读过某条记录后,即使其他事务修改了该记录的值并且提交,该事务之后再读该条记录时,读到的仍是第一次读到的值,而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读 。
详细示例
会话A | 会话B |
mysql> insert into tb1(id) values(1); Query OK, 1 row affected (0.12 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) | mysql> select * from tb1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> update tb1 set id=2 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 | |
mysql> select * from tb1; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) | mysql> select * from tb1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) | |
mysql> commit; Query OK, 0 rows affected (0.05 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) | |
mysql> commit; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) |
可重复读(导致的幻读示例)
插入导致幻读
会话A | 会话B |
mysql> create table tb1(id int unsigned not null, primary key(id))Engine=InnoDB; mysql> insert into tb1(id) values(1), (5), (9); | |
mysql> set tx_isolation = 'repeatable-read'; Query OK, 0 rows affected, 1 warning (0.00 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | | 5 | | 9 | +----+ 3 rows in set (0.00 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> insert into tb1 values(2); Query OK, 1 row affected (0.00 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | | 5 | | 9 | +----+ 3 rows in set (0.00 sec) | |
mysql> insert into tb1 values(2); ---------此处阻塞 | |
mysql> commit; Query OK, 0 rows affected (0.05 sec) | |
mysql> insert into tb1 values(2); --------此处没有执行新命令,是上边阻塞的退出了 | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | | 5 | | 9 | +----+ 3 rows in set (0.00 sec) | |
mysql> commit; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | | 2 | | 5 | | 9 | +----+ 3 rows in set (0.00 sec) |
可见:导致了幻读(本来对话A查询说没有id=2这一项的,但是对话A插入却阻塞了,对话B提交后对话A直接报错)。
修改导致的幻读
会话A | 会话B |
mysql> create table tb1(id int unsigned not null, primary key(id))Engine=InnoDB; mysql> insert into tb1(id) values(1), (5), (9); | |
mysql> set tx_isolation = 'repeatable-read'; Query OK, 0 rows affected, 1 warning (0.00 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | | 5 | | 9 | +----+ 3 rows in set (0.00 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> update tb1 set id=6 where id=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | | 5 | | 9 | +----+ 3 rows in set (0.00 sec) | |
mysql> update tb1 set id=7 where id=5; Query OK, 0 rows affected (22.87 sec) Rows matched: 0 Changed: 0 Warnings: 0 | |
mysql> commit; Query OK, 0 rows affected (0.05 sec) | |
mysql> select * from tb1; | |
mysql> commit; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1; +----+ | id | +----+ | 1 | | 6 | | 9 | +----+ 3 rows in set (0.00 sec) |
可重复读(导致的没有幻读示例)
其他网址
Innodb锁机制:Next-Key Lock 浅谈 - jyzhou - 博客园
下边这个示例"for update"换为"lock in share mode"也是同样的结果。
会话A | 会话B |
mysql> create table tb1(id int, index idx_id(id))Engine=InnoDB; mysql> insert into tb1(id) values(1), (3), (5), (8), (11); | |
mysql> set tx_isolation = 'repeatable-read'; Query OK, 0 rows affected, 1 warning (0.00 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1 where id = 8 for update; +----+ | id | +----+ | 8 | +----+ 1 row in set (0.00 sec) | |
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) | |
mysql> select * from tb1; | |
mysql> insert into tb1 values(2); mysql> insert into tb1 values(4); mysql> insert into tb1 values(6); --------------------阻塞住,只有光标闪烁 mysql> insert into tb1 values(7); --------------------阻塞住,只有光标闪烁 mysql> insert into tb1 values(9); --------------------阻塞住,只有光标闪烁 mysql> insert into tb1 values(10); --------------------阻塞住,只有光标闪烁 mysql> insert into tb1 values(12); |
说明:如果上边创建表时没有加入索引,则会话B无论插入什么,都是阻塞。
分析:
InnoDB对于行的查询都是采用了Next-Key Lock的算法,锁定的不是单个值,而是一个范围(GAP)。上面索引值有1,3,5,8,11,其记录的GAP的区间如下:是一个左开右闭的空间(原因是默认主键的有序自增的特性,结合后面的例子说明),InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。 GAP区间:(-∞,1],(1,3],(3,5],(5,8],(8,11],(11,+∞)
select * from tb1 where id = 8 for update;
该SQL语句锁定的范围是(5,8],下个下个键值范围是(8,11],所以插入5~11之间的值的时候都会被锁定,要求等待。即:插入5,6,7,8,9,10 会被锁住。插入非这个范围内的值都正常。
可序列化
简要说明
详细示例
会话A | 会话B |
mysql> start transaction; Query OK, 0 rows affected (0.13 sec) | |
mysql> update tb1 set id='6'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 | |
mysql> set tx_isolation = 'serializable'; Query OK, 0 rows affected, 1 warning (0.00 sec) | |
开启事务或者将autocommit设置为0 mysql> start transaction; | |
mysql> select * from tb1; --------本处无输出,只有光标在闪烁 |
结果表明B连接被阻塞住了, 同样的,如果B开启事务先查询, 未提交, A再去更新一样会被锁住。
参考测试发现:如果细心观察会发现,会发现很奇怪,按理来说lock in share mode应该是行锁定, 但是在我的机器上却成了表锁定,B只更新了id为4的记录,A查询ID为2的记录却仍然被锁定, 经过我测试在服务器上lock in share mode 确实是行锁定。经过多次测试发现如果查询数据中通过索引查询那么lock in share mode会根据索引锁行,如果查询条件中没有索引那么就直接锁表。
如何选择事务隔离级别
如果对一致性要求较高:选择可重复读。
如果对性能要求较高,或者读多写少:选择提交读。
如果对性能要求极高:选择未提交读。
其他网址
参考书籍:《高性能MySQL》=> 1.3.1 隔离级别