浅析innodb事务的隔离级别

写在前面

之前对innodb的事务隔离级别还停留在教科书知识的层面，直到最近工作中，遇到了

mysql错误 Lock wait timeout exceeded; try restarting transaction

测试版本及默认选项

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.40    |
+-----------+
1 row in set

mysql> show variables like "%iso%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

幻读的重点在于对新增的幻读，不可重复读在于（同一个事务中对另一个事务的的改变或者删除）多次读取。

====================================================================

测试1：read-uncommitted

mysql>  set tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set

A连接【开启事务->更新数据->未提交】

mysql> update ad_put_history set ad_id=888 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B连接【读到了A未提交的数据】

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |   888 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

A连接【开启事务->更新数据->未提交】

mysql> update ad_put_history set ad_id=8888 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B连接【读到了A未提交的数据】

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |  8888 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

A连接【开启事务->更新数据->已提交】

mysql> update ad_put_history set ad_id=999 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected

B连接【读到了A提交之后的数据】

mysql> select * from ad_put_history where id =24;

+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |   999 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+----

测试结果：
A连接在开启事务，但未提交的情况下，B可以随时看到A修改但未提交的数据，可想而知，如果A进行commit 或rollback，看到的数据会和修改后或回滚的值一致。

==================================================================

测试2 read-committed

mysql> set tx_isolation = 'read-committed';
Query OK, 0 rows affected

A连接【开启事务->更新数据->未提交】

mysql> start transaction;
Query OK, 0 rows affected

mysql> update ad_put_history set ad_id=11 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B连接【未读到A未提交的数据】

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |   999 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

A连接【开启事务->更新数据->未提交】

mysql> update ad_put_history set ad_id=22 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B连接【未读到A未提交的数据】

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |   999 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

A连接【开启事务->更新数据->已提交】

mysql> commit;
Query OK, 0 rows affected

B连接【刚刚读到了Acommit的事务】

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |    22 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

测试结果：
测试结果：A连接在开启事务，但未提交的情况下，B不能看到A修改但未提交的数据，只有在A提交之后，B才能看到A提交或者回滚后的数据。此时的隔离状态为： 读已经提交的

===============================================================

测试3：重要！repeatable-read

mysql> set tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected

A连接【A开启事务，并不提交】

mysql> start transaction;
Query OK, 0 rows affected

mysql> update ad_put_history set ad_id=11 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B连接【A开启事务，并不提交】

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |    11 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

A连接【A事务未提交】

mysql> update ad_put_history set ad_id=22244 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B连接 【B事务未提交】

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |    11 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+

A连接 提交事务成功，数据已经更改

mysql> commit;
Query OK, 0 rows affected

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 | 22244 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

B连接【未提交事务】，仍然看不到A连接事务提交后的结果

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 |    11 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

B连接 【B提交事务后，方才看到A提交事务后的结果】

mysql> commit;
Query OK, 0 rows affected

mysql> select * from ad_put_history where id =24;
+----+-------+------------+---------------------+
| id | ad_id | day        | create_time         |
+----+-------+------------+---------------------+
| 24 | 22244 | 2017-01-03 | 2017-01-03 17:10:24 |
+----+-------+------------+---------------------+
1 row in set

测试结果：
repeatable 隔离级别 在开启事务的情况下（此例子为B连接），同一个查询条件（在B中，且在B的事务中）同一条件的查询返回结果永远是一致的，无论A连接事务中是否提交事务。当然如果A事务在提交后，B如果没有在事务中，那么查询结果肯定是随着A的提交与否的改变而改变的。可重复读的意思就是这样。

工作中遇到问题复现

 **Lock wait timeout exceeded; try restarting transaction**

A事务

mysql> set tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected

mysql> begin;
Query OK, 0 rows affected

mysql> update ad_put_history set ad_id=110 where id =24;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B事务

mysql> set tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected

mysql> begin;
Query OK, 0 rows affected

mysql> update ad_put_history set ad_id=120 where id =24;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql>

测试结果
B事务会阻塞在A的事务请求，直到A commit，B才会执行update语句，再此之后B才可以被commit。

更多详细参考
Innodb中的事务隔离级别和锁的关系
GTID