MySQL InnoDB四个事务级别 与 脏读、不重复读、幻读

MySQL InnoDB事务隔离级别脏读、可重复读、幻读

希望通过本文，可以加深读者对ySQL InnoDB的四个事务隔离级别，以及脏读、不重复读、幻读的理解。

有四级，默认是“可重复读”（REPEATABLE READ）。

·        未提交读（READUNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。

·        提交读（READCOMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。

·        可重复读（REPEATABLEREAD）。在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象（稍后解释）。

·        串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥。

四个级别逐渐增强，每个级别解决一个问题。

·        脏读，最容易理解。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。

·        不重复读。解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。

·        幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

MySQL InnoDB事务隔离级别可设置为global和session级别。

事务隔离级别查看

查看当前session的事务隔离级别：

mysql> show variables like '%tx_isolation%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+

查看全局的事务隔离级别。

mysql> show global variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
 

设置global事务隔离级别：

 set global isolation level read committed;

注意一点的设置global并不会对当前session生效。

设置session事务隔离级别sql脚本：

set session transaction isolation level read uncommitted;      
set session transaction isolation level read committed;      
set session transaction isolation level REPEATABLE READ;   
set session transaction isolation level SERIALIZABLE;

上面的文字，读起来并不是那么容易让人理解，以下用几个实验对InnoDB的四个事务隔离级别做详细的解释，希望通过实验来加深大家对InnoDB的事务隔离级别理解。

CREATE TABLE `t` (
	`a` INT (11) NOT NULL PRIMARY KEY
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO t (a) VALUES (1),(2),(3);

实验一：解释脏读、可重复读问题

实验二：测试READ-COMMITTED与REPEATABLE-READ

当然数据的可见性都是对不同事务来说的，同一个事务，都是可以读到此事务中最新数据的。

start transaction;
insert into t(a) values (4);
select  * from t;
1,2,3,4;
insert into t(a) values (5);
select  * from t;
1,2,3,4,5;

实验三：测试SERIALIZABLE事务对其他的影响

实验四：幻读

mysql>show create table t_bitfly\G; CREATE TABLE `t_bitfly` ( `id` bigint(20) NOT NULL default '0', `value` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk mysql>select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+

SessionA Session B START TRANSACTION; START TRANSACTION; SELECT * FROM t_bitfly; empty set INSERT INTO t_bitfly VALUES (1, 'a'); SELECT * FROM t_bitfly; empty set COMMIT; SELECT * FROM t_bitfly; empty set INSERT INTO t_bitfly VALUES (1, 'a'); ERROR 1062 (23000): Duplicate entry '1' for key 1

SessionA Session B START TRANSACTION; START TRANSACTION; SELECT * FROM t_bitfly; +------+-------+ | id | value | +------+-------+ | 1 |a | +------+-------+ INSERT INTO t_bitfly VALUES (2, 'b'); SELECT * FROM t_bitfly; +------+-------+ | id | value | +------+-------+ | 1 |a | +------+-------+ COMMIT; SELECT * FROM t_bitfly; +------+-------+ | id | value | +------+-------+ | 1 |a | +------+-------+ UPDATE t_bitfly SET value='z'; Rows matched: 2 Changed:2 Warnings: 0 (怎么多出来一行) SELECT * FROM t_bitfly; +------+-------+ | id | value | +------+-------+ | 1 |z | | 2 |z | +------+-------+

Session A Session B START TRANSACTION; START TRANSACTION; SELECT * FROM t_bitfly WHERE id<=1 FOR UPDATE; +------+-------+ | id | value | +------+-------+ | 1 | a | +------+-------+ INSERT INTO t_bitfly VALUES (2, 'b'); Query OK, 1 row affected SELECT * FROM t_bitfly; +------+-------+ | id | value | +------+-------+ | 1 | a | +------+-------+ INSERT INTO t_bitfly VALUES (0, '0'); (waiting for lock ... then timeout) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction SELECT * FROM t_bitfly; +------+-------+ | id | value | +------+-------+ | 1 | a | +------+-------+ COMMIT; SELECT * FROM t_bitfly; +------+-------+ | id | value | +------+-------+ | 1 | a | +------+-------+

SessionA Session B START TRANSACTION; START TRANSACTION; SELECT * FROM t_bitfly; +----+-------+ | id | value | +----+-------+ | 1 |a | +----+-------+ INSERT INTO t_bitfly VALUES (2, 'b'); COMMIT; SELECT * FROM t_bitfly; +----+-------+ | id | value | +----+-------+ | 1 |a | +----+-------+ SELECT * FROM t_bitfly LOCK IN SHARE MODE; +----+-------+ | id | value | +----+-------+ | 1 |a | | 2 |b | +----+-------+ SELECT * FROM t_bitfly FOR UPDATE; +----+-------+ | id | value | +----+-------+ | 1 |a | | 2 |b | +----+-------+ SELECT * FROM t_bitfly; +----+-------+ | id | value | +----+-------+ | 1 |a | +----+-------+

如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。

本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据。

Ifyou want to see the “freshest” state of the database, you should use either theREAD COMMITTED isolation level or a locking read:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

------

结论：MySQLInnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-keylocks。

==================== 结尾 ====================

文章幻读部分直接转载了bitfly的文章： http://blog.bitfly.cn/post/mysql-innodb-phantom-read/ 

转载请说明出处，包括参考文章出处。

MySQL InnoDB四个事务级别 与 脏读、不重复读、幻读,布布扣,bubuko.com