MySQL InnoDB隔离级别
来源:互联网 发布:淘宝沟通软件 编辑:程序博客网 时间:2024/06/02 07:28
上篇文章讨论了事务隔离级别,隔离级别这个东西在不同的数据库产品上,是有一些区别的,本篇重点讲讲mysql数据库。
四种标准的隔离级别MySQL数据库都支持,下面我们一个一个看过来先。
首先我们先创建一个简单的测试表。
Read Uncommitted读未提交
首先,会话S1查询tb1表,没有记录返回。
接着会话S2往tb1表写入一条记录,但不提交。
接着在会话S1中再次查询tb1表,可以看到,会话S2未提交的脏数据被会话S1查询出来了。
由此可见,在这个级别下,是会发生脏读的。
Read Committed读已提交
首先,会话S1查询tb1表,没有记录返回。
接着,会话S2往tb1表写入一条记录,但不提交。
这时,在会话S1中再次查询tb1表,依然没有记录返回,说明在这个级别下,未提交的数据是不会被查询出来的,能避免脏读。
这时,在会话S2中提交事务。
再次看看会话S1中的情况。
在会话S2中提交事务后,会话S1中的事务可以看到新的记录了,说明该级别不能防止不可重复读的问题。
Repeatable Read
再来看看Repeatable Read,首先会话S1查询tb1表,返回记录(1, 100)。
接着会话S2更新记录(1, 100)-> (1, 101)并提交事务。
在会话S1中看看情况。
会话S1中查询到的还是原来的结果,如果提交或回滚事务后再次查询,看到的就是被会话S2更新后的数据了。
所以说这个级别可以防止不可重复读,但是对于幻读呢?我们来看看。
首先,会话S1查询tb1表,返回记录(1, 101)。
接着会话S2插入一条新的记录(2, 200)并提交事务。
再看看会话S1中的情况。
从结果看到,幻读并没有发生,这个本人开始的认识有出入,因为在标准的事务隔离级别定义下,Repeatable Read是不能防止幻读产生的。这里是因为InnoDB使用了2种技术手段(MVCC AND GAP LOCK)实现了防止幻读的发生。
Serializable序列化
既然Repeatable Read已经可以防止幻读的发生了,那Serializable存在的意义何在呢?我们还是来看一个例子吧。
首先,会话S1(在Repeatable Read隔离级别下)查询tb1表。
接着,会话S2在tb1中插入一条新数据(2, 200)并提交事务。
回到会话S1中再次查询,从结果从看只有(1, 101)这条数据,但在尝试插入新数据(2, 200)时确提示主键重复错误了。
如果隔离级别是Serialiable的话,上面的情况就不会发生了。来看看在Serialiable下的情况:
首先,会话S1查询tb1表。
接着会话S2尝试在tb1中插入一条新的记录。
会话S2的插入操作将会被挂起,直到会话S1中的事务结束,所以就不存在Repeatable级别下的问题了,但是Serialiable级别下相关于串行化执行事务了,并行性能太差,一般不会在生产环境使用。
隔离级别与锁的探讨
在我的上一篇文章《事务、事务并发》中已经提到S锁与X锁的概念,但是在测试中发现MySQL与其它数据库存在差异,比如在可重复读这个隔离级别下,查询操作并不会对数据记录加S锁,但更新操作还是会加X锁的。个人猜想,MySQL内部可能为每个数据行都维护了一个版本的概念,通过版本以及X锁来共同实现各种隔离级别的。
以下两种方式,可以显示地指定查询记录时加S锁或X锁。
四种标准的隔离级别MySQL数据库都支持,下面我们一个一个看过来先。
首先我们先创建一个简单的测试表。
CREATE TABLE tb1( idINTNOT NULL, valueDECIMALNOT NULL, PRIMARY KEY (id))ENGINE=INNODB;
Read Uncommitted读未提交
首先,会话S1查询tb1表,没有记录返回。
mysql> set session transaction isolation level read uncommitted;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)mysql>
接着会话S2往tb1表写入一条记录,但不提交。
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb1 values(1, 100);Query OK, 1 row affected (0.00 sec)mysql>
接着在会话S1中再次查询tb1表,可以看到,会话S2未提交的脏数据被会话S1查询出来了。
mysql> set session transaction isolation level read uncommitted;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 100 |+----+-------+1 row in set (0.00 sec)mysql>
由此可见,在这个级别下,是会发生脏读的。
Read Committed读已提交
首先,会话S1查询tb1表,没有记录返回。
mysql> set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)mysql>
接着,会话S2往tb1表写入一条记录,但不提交。
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb1 values(1, 100);Query OK, 1 row affected (0.00 sec)mysql>
这时,在会话S1中再次查询tb1表,依然没有记录返回,说明在这个级别下,未提交的数据是不会被查询出来的,能避免脏读。
mysql> set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)mysql>
这时,在会话S2中提交事务。
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb1 values(1, 100);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql>
再次看看会话S1中的情况。
mysql> set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 100 |+----+-------+1 row in set (0.00 sec)mysql>
在会话S2中提交事务后,会话S1中的事务可以看到新的记录了,说明该级别不能防止不可重复读的问题。
Repeatable Read
再来看看Repeatable Read,首先会话S1查询tb1表,返回记录(1, 100)。
mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 100 |+----+-------+1 row in set (0.00 sec)mysql>
接着会话S2更新记录(1, 100)-> (1, 101)并提交事务。
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> update tb1 set value = 101 where id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql>
在会话S1中看看情况。
mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 100 |+----+-------+1 row in set (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 100 |+----+-------+1 row in set (0.00 sec)mysql>
会话S1中查询到的还是原来的结果,如果提交或回滚事务后再次查询,看到的就是被会话S2更新后的数据了。
mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 100 |+----+-------+1 row in set (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 100 |+----+-------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 |+----+-------+1 row in set (0.00 sec)mysql>
所以说这个级别可以防止不可重复读,但是对于幻读呢?我们来看看。
首先,会话S1查询tb1表,返回记录(1, 101)。
mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 |+----+-------+1 row in set (0.00 sec)mysql>
接着会话S2插入一条新的记录(2, 200)并提交事务。
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb1(id, value) values(2, 200);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql>
再看看会话S1中的情况。
mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 |+----+-------+1 row in set (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 |+----+-------+1 row in set (0.00 sec)mysql>
从结果看到,幻读并没有发生,这个本人开始的认识有出入,因为在标准的事务隔离级别定义下,Repeatable Read是不能防止幻读产生的。这里是因为InnoDB使用了2种技术手段(MVCC AND GAP LOCK)实现了防止幻读的发生。
Serializable序列化
既然Repeatable Read已经可以防止幻读的发生了,那Serializable存在的意义何在呢?我们还是来看一个例子吧。
首先,会话S1(在Repeatable Read隔离级别下)查询tb1表。
mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 |+----+-------+1 row in set (0.00 sec)mysql>
接着,会话S2在tb1中插入一条新数据(2, 200)并提交事务。
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb1(id, value) values(2, 200);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql>
回到会话S1中再次查询,从结果从看只有(1, 101)这条数据,但在尝试插入新数据(2, 200)时确提示主键重复错误了。
mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 |+----+-------+1 row in set (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 |+----+-------+1 row in set (0.00 sec)mysql> insert into tb1(id, value) values(2, 200);ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'mysql>
如果隔离级别是Serialiable的话,上面的情况就不会发生了。来看看在Serialiable下的情况:
首先,会话S1查询tb1表。
mysql> set session transaction isolation level serializable;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb1;+----+-------+| id | value |+----+-------+| 1 | 101 || 2 | 200 |+----+-------+2 rows in set (0.00 sec)mysql>
接着会话S2尝试在tb1中插入一条新的记录。
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb1(id, value) values(3, 300);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>
会话S2的插入操作将会被挂起,直到会话S1中的事务结束,所以就不存在Repeatable级别下的问题了,但是Serialiable级别下相关于串行化执行事务了,并行性能太差,一般不会在生产环境使用。
隔离级别与锁的探讨
在我的上一篇文章《事务、事务并发》中已经提到S锁与X锁的概念,但是在测试中发现MySQL与其它数据库存在差异,比如在可重复读这个隔离级别下,查询操作并不会对数据记录加S锁,但更新操作还是会加X锁的。个人猜想,MySQL内部可能为每个数据行都维护了一个版本的概念,通过版本以及X锁来共同实现各种隔离级别的。
以下两种方式,可以显示地指定查询记录时加S锁或X锁。
select * from ... where ... lock in share modeselect * from ... where ... for update
0 0
- MySQL InnoDB隔离级别
- mysql-innodb事务隔离级别
- MySql Innodb 事务隔离级别
- MySQL InnoDB事务的隔离级别
- MySQL InnoDB 四种事务隔离级别
- Mysql InnoDB事务的隔离级别
- MySQL---InnoDB引擎隔离级别详解
- InnoDB 事务隔离级别(Mysql篇)
- MySQL InnoDB中的四种隔离级别
- mysql-Innodb事务隔离级别-repeatable read
- MySQL InnoDB :事务隔离隔离级别以及对应的问题
- Mysql 事务隔离级别、InnoDB实现隔离机制
- Innodb事务隔离级别
- innodb事务隔离级别
- innodb事务隔离级别
- Innodb事务隔离级别
- innodb事务隔离级别
- Innodb事务隔离级别
- How to setup SVN server
- MySQL 账户管理
- 一般纳税人公司与小规模的公司有什么不一样
- 事务、事务并发
- 马上步入2015,写一篇新年计划吧!
- MySQL InnoDB隔离级别
- android坐标
- 我比较擅长网站类的编程,谈下自己的项目
- MySQL InnoDB锁机制(一)
- MySQL InnoDB非阻塞式读的实现原理
- MySQL InnoDB锁机制(二)
- hbase 异常 未解决
- MySQL InnoDB锁机制(三)
- 权限数据库设计