MySQL 4 种隔离级别的区别
## 测试环境 mysql> select version(); +------------+ | version() | +------------+ | 5.7.11-log | +------------+
数据库事务特性 ACID，即
A（Atomicity） -原子性
C（Consistency）- 一致性
I（Isolation） - 隔离性
D（Durability） - 持久性
MySQL 提供了 4 种不同的隔离级别，用来支持多版本并发控制（MVCC，Multi-Version Concurrency Control）。
默认的事务隔离级别是 REPEATABLE-READ（可重读）：
mysql> select @@global.tx_isolation, @@session.tx_isolation; +-------------------------+---------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-------------------------+---------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +-------------------------+---------------------------+
在该事务级别下，一个事务期间内，该事务不考虑其他提交语句。
0x00、测试准备
1. 创建测试表
mysql> CREATE TABLE `transaction_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(20) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
2. 开启两个 MySQL 客户端进行测试
0x01、REPEATABLE-READ（可重读）
step 1:
在 Client 1 下开启事务，查询测试表中的数据：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from transaction_test; Empty set (0.00 sec)
step 2:
在 Client 2 下开启事务，并且往测试表中插入数据，但不提交事务：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into transaction_test (val) values ('x'),('y'),('z'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from transaction_test; +----+-----+---------------------+ | id | val | created | +----+-----+---------------------+ | 1 | x | 2017-02-06 00:20:59 | | 2 | y | 2017-02-06 00:20:59 | | 3 | z | 2017-02-06 00:20:59 | +----+-----+---------------------+ 3 rows in set (0.00 sec)
step 3:
在 Client 1 下查看表中数据：
mysql> select * from transaction_test; Empty set (0.00 sec)
仍然是空表。
step 4:
Client 2 提交事务：
mysql> commit; Query OK, 0 rows affected (0.12 sec)
step 5:
Client 1 下查看表中数据：
mysql> select * from transaction_test; Empty set (0.00 sec)
任然是空表。
step 6:
Client 1 提交事务，查看表中数据：
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from transaction_test; +----+-----+---------------------+ | id | val | created | +----+-----+---------------------+ | 1 | x | 2017-02-06 00:20:59 | | 2 | y | 2017-02-06 00:20:59 | | 3 | z | 2017-02-06 00:20:59 | +----+-----+---------------------+ 3 rows in set (0.00 sec)
当 Client 1 完成事务后，才能看到其他事务提交的数据。
0x02、READ-COMMITTED（读取提交内容）
step 1:
Client 1 中清空表，改变数据库隔离级别：
mysql> truncate table transaction_test; Query OK, 0 rows affected (0.10 sec) mysql> set @@session.tx_isolation = 'READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation, @@session.tx_isolation; +-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | READ-COMMITTED | +-----------------------+------------------------+ 1 row in set (0.00 sec)
step 2:
Client 1 开启事务，查询表中数据：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from transaction_test; Empty set (0.00 sec)
step 3:
Client 2 开启事务，向表中插入数据，但不提交事务：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into transaction_test (val) values ('x'),('y'),('z'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from transaction_test; +----+-----+---------------------+ | id | val | created | +----+-----+---------------------+ | 1 | x | 2017-02-06 00:31:00 | | 2 | y | 2017-02-06 00:31:00 | | 3 | z | 2017-02-06 00:31:00 | +----+-----+---------------------+ 3 rows in set (0.00 sec)
step 4：
Client 1 下查看表中数据：
mysql> select * from transaction_test; Empty set (0.00 sec)
仍然是空表。
step 5:
Client 2 提交事务：
mysql> commit; Query OK, 0 rows affected (0.13 sec)
step 6:
Client 1 下查看表中数据：
mysql> select * from transaction_test; +----+-----+---------------------+ | id | val | created | +----+-----+---------------------+ | 1 | x | 2017-02-06 00:31:00 | | 2 | y | 2017-02-06 00:31:00 | | 3 | z | 2017-02-06 00:31:00 | +----+-----+---------------------+ 3 rows in set (0.00 sec)
与 REPEATABLE-READ 不同的是，Client 1 没有结束事务也能看到其他事务提交的数据。
0x03、READ-UNCOMMITTED（读取未提交内容）
step 1:
Client 1 下清空表，设置隔离级别：
mysql> truncate table transaction_test; Query OK, 0 rows affected (0.10 sec) mysql> set @@session.tx_isolation = 'READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation, @@session.tx_isolation; +-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | READ-UNCOMMITTED | +-----------------------+------------------------+ 1 row in set (0.00 sec)
step 2:
Client 1 下开启事务，查询表数据：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from transaction_test; Empty set (0.00 sec)
step 3:
Client 2 下开启事务，向表中插入数据，但是不提交事务：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into transaction_test (val) values ('x'),('y'),('z'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from transaction_test; +----+-----+---------------------+ | id | val | created | +----+-----+---------------------+ | 1 | x | 2017-02-06 00:43:59 | | 2 | y | 2017-02-06 00:43:59 | | 3 | z | 2017-02-06 00:43:59 | +----+-----+---------------------+ 3 rows in set (0.00 sec)
step 4:
Client 1 中查询数据：
mysql> select * from transaction_test; +----+-----+---------------------+ | id | val | created | +----+-----+---------------------+ | 1 | x | 2017-02-06 00:43:59 | | 2 | y | 2017-02-06 00:43:59 | | 3 | z | 2017-02-06 00:43:59 | +----+-----+---------------------+ 3 rows in set (0.00 sec)
与 READ-COMMITTED 不同的是，在 Client 2 不提交事务的情况下，Client 1 也能读到其他事务插入的数据，即脏数据或者说产生了“脏读”。在一个事务期间读到了另一个事务在未提交之前产生的数据，那么第一个事务就读到了脏数据，产生了对第二个事务未提交数据的依赖，如果第二个事务回滚，那么第一个事务读到的数据是错误的脏数据。
“脏读”与“幻读”、“不可重复读”的区别是：幻读是读取结果集条数的对比，一个事务按相同的查询条件查询之前检索过的数据，发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。
不可重复读是读取的数据本身的对比，一个事务在读取某些数据后的一段时间后，再次读取这个数据，发现其读取出来的数据内容已经发生了改变，就是不可重复读。
step 5:
Client 2 回滚事务：
mysql> rollback; Query OK, 0 rows affected (0.04 sec) mysql> select * from transaction_test; Empty set (0.00 sec)
step 6:
Client 1 查询表数据：
mysql> select * from transaction_test; Empty set (0.00 sec)
空表。
0x04、SERIALIZABLE（序列化）
step 1:
Client 1 下清空表，设置隔离级别：
mysql> truncate table transaction_test; Query OK, 0 rows affected (0.21 sec) mysql> set @@session.tx_isolation ='SERIALIZABLE'; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation, @@session.tx_isolation; +-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | SERIALIZABLE | +-----------------------+------------------------+ 1 row in set (0.00 sec)
step 2:
Client 1 开启事务，查询表：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from transaction_test; Empty set (0.00 sec)
step 3:
Client 2 开启事务，向表中插入数据：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into transaction_test (val) values ('x'),('y'),('z');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
此时 Client 2 插入数据（INSERT 操作）会被阻塞，直到第一个（Client 1）事务提交后，Client 2 的插入操作才能完成。
step 4：
Client 1 提交事务：
mysql> commit; Query OK, 0 rows affected (0.00 sec)
step 5:
Client 2 插入数据：
mysql> insert into transaction_test (val) values ('x'),('y'),('z'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from transaction_test; +----+-----+---------------------+ | id | val | created | +----+-----+---------------------+ | 4 | x | 2017-02-06 00:54:17 | | 5 | y | 2017-02-06 00:54:17 | | 6 | z | 2017-02-06 00:54:17 | +----+-----+---------------------+ 3 rows in set (0.00 sec)
参考：
