MySQL InnoDB事务隔离级别

InnoDB有4中事务隔离级别，分别是REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、SERIALIZABLE。

只看官方文档很难理解其中的差异，通过实验逐步测试得出结论是最好的学习方法。

1.四种事务隔离级别测试

1.1 READ COMMIT

创建测试表

root@localhost [test]>create table tx_t1(id int,name varchar(100)); 

会话2：先开启事务

root@localhost [test]>START TRANSACTION; 

会话1：向测试表插入数据，不提交事务

root@localhost [test]>set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]>START TRANSACTION;  
root@localhost [test]>insert into tx_t1 values(1,'Tom');
Query OK, 1 row affected (0.12 sec)


root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | Tom  |
+------+------+
1 row in set (0.00 sec) 

会话2：看不到测试表有数据

root@localhost [test]>select * from tx_t1;
Empty set (0.00 sec) 

会话1：事务提交

root@localhost [test]>commit;
Query OK, 0 rows affected (0.00 sec)

会话2：可以看到记录了
root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | Tom  |
+------+------+
1 row in set (0.00 sec) 

测试update 
会话2：先开启事务

root@localhost [test]>START TRANSACTION; 

会话1：更新数据，不提交事务

root@localhost [test]>START TRANSACTION; 
root@localhost [test]>update tx_t1 set name='JAY' WHERE ID=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [test]>
root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | JAY  |
+------+------+
1 row in set (0.00 sec) 

会话2：

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | Tom  |
+------+------+
1 row in set (0.00 sec) 

会话1：提交事务

root@localhost [test]>commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | JAY  |
+------+------+
1 row in set (0.00 sec) 

会话2：会话1提交事务后，能够看到会话1更新的数据

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | JAY  |
+------+------+
1 row in set (0.00 sec) 

tx_isolation = READ-COMMITTED事务隔离级别下，A会话事务提交后，B会话事务才能看到数据的变化。 
B会话事务即使先于A会话事务，A会话事务提交后，B会话事务可以立即看到A事务数据的改变。

1.2 REPEATABLE READ 

当前测试表数据

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N1   |
|2     | Lily |
+------+------+
2 rows in set (0.00 sec) 

会话2：先开始事务

root@localhost [test]>start transaction; 

会话1：更新测试表

root@localhost [test]>start transaction;

root@localhost [test]>update tx_t1 set name='N2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N1   |
|2     | N2   |
+------+------+
2 rows in set (0.00 sec) 

会话2：测试表数据无变化

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N1   |
|2     | Lily |
+------+------+
2 rows in set (0.00 sec) 

会话1：提交事务

root@localhost [test]>commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N1   |
|2     | N2   |
+------+------+
2 rows in set (0.00 sec) 

会话2：会话1事务提交后，会话2的事务查询不到会话1更新的数据。

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N1   |
|2     | Lily |
+------+------+
2 rows in set (0.00 sec) 

REPEATABLE-READ事务隔离级别下，B会话事务的开始点，会产生数据的快照，无论A会话事务如何更新数据，B事务读取到的都是快照数据。 
上面的实验很好的证明了这点。

REPEATABLE-READ 这种事务隔离级别，保障数据的强一致性。Oracle也采用这种事务隔离方式。

1.3 READ UNCOMMITTED

查询当前数据

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N1   |
|2     | N2   |
+------+------+
2 rows in set (0.02 sec) 

会话2：先开始事务

root@localhost [test]>start transaction; 

会话1：更新测试表，先不提交事务

root@localhost [test]>start transaction;

root@localhost [test]>update tx_t1 set name='N11' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N11  |
|2     | N2   |
+------+------+
2 rows in set (0.00 sec) 

会话2：看到更新了

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N11  |
|2     | N2   |
+------+------+
2 rows in set (0.00 sec) 

会话1：提交事务

root@localhost [test]>commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N11  |
|2     | N2   |
+------+------+
2 rows in set (0.00 sec) 

会话2：

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N11  |
|2     | N2   |
+------+------+
2 rows in set (0.00 sec) 

tx_isolation = READ UNCOMMITTED事务隔离级别下，A会话事务更新数据，B会话事务可以立马看到数据变化，无需A会话事务提交。

1.4 SERIALIZABLE

查询当前数据

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N111 |
|2     | N2   |
+------+------+
2 rows in set (0.03 sec) 

会话2：先开始事务

root@localhost [test]>start transaction; 

会话1：更新测试表，先不提交事务

root@localhost [test]>start transaction;

root@localhost [test]>update tx_t1 set name='N22' where id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N111 |
|2     | N22  |
+------+------+
2 rows in set (0.00 sec) 

会话2：查询测试表数据，会话hang住，无法得到结果。

root@localhost [test]>select * from tx_t1; 

会话1：提交事务

root@localhost [test]>commit;
Query OK, 0 rows affected (0.00 sec)

会话2：会话hang住结束，输出结果
root@localhost [test]>select * from tx_t1;
+------+------+
| id   | name |
+------+------+
|1     | N111 |
|2     | N22  |
+------+------+
2 rows in set (3.54 sec) 

如果会话1不及时提交事务，会话2会如下错误，锁等待超时，请重新开始事务。

root@localhost [test]>select * from tx_t1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

tx_isolation = SERIALIZABLE 事务隔离级别下，SELECT操作会加上共享锁，不及时提交会造成大量事务超时和锁竞争。

2.事务隔离级别总结

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（SERIALIZABLE）	不可能	不可能	不可能

脏读 :一个事务读取到另一事务未提交的更新数据

不可重复读 : 在同一事务中,多次读取同一数据返回的结果有所不同, 换句话说, 后续读取可以读到另一事务已提交的更新数据. 相反, “可重复读”在同一事务中多次读取数据时, 能够保证所读数据一样, 也就是后续读取不能读到另一事务已提交的更新数据。

幻读 :一个事务读到另一个事务已提交的insert数据

参考：http://blog.csdn.net/chengshiep/article/details/51779681

转载请注明： 
十字螺丝钉 
QQ:463725310 
site:  (有更多更新的内容，欢迎访问) 
http://blog.chinaunix.net/uid/23284114.html 
E-MAIL:houora#gmail.com(#请自行替换为@)