一路向前!deadhorse.blog.chinaunix.net

从事Oracle架构设计、SQL优化、MySQL维护工作。欢迎交流 qq:463725310 我的站点:www.dbhelp.net

  • 博客访问: 1707711
  • 博文数量: 208
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3020
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
  • 认证徽章:
个人简介

作者:十字螺丝钉。多年Oracle、MySQL数据库架构设计、管理及维护经验。擅长SQL优化、排错等。 我的站点:www.dbhelp.net

文章分类

全部博文(208)

文章存档

2017年(7)

2016年(35)

2015年(16)

2014年(25)

2013年(25)

2012年(56)

2011年(44)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
MySQL InnoDB事务隔离级别 2017-03-31 17:56:32

分类: Mysql/postgreSQL


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: 
www.dbhelp.net (有更多更新的内容,欢迎访问) 
http://blog.chinaunix.net/uid/23284114.html 
E-MAIL:houora#gmail.com(#请自行替换为@)


阅读(237) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册