社区 学习 工具库 休闲
搜索
简体中文
首页 > 数据库 > mysql教程 > MySQLInnoDB四个事务级别与脏读、不重复读、幻读_MySQL

MySQLInnoDB四个事务级别与脏读、不重复读、幻读_MySQL

WBOY
发布： 2016-06-01 13:12:46
原创
1003 人浏览过

MySQL InnoDB事务隔离级别脏读、可重复读、幻读

MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。

· 未提交读（READUNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。

· 提交读（READCOMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。

· 可重复读（REPEATABLEREAD）。在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象（稍后解释）。

· 串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥。

四个级别逐渐增强，每个级别解决一个问题。

· 脏读，最容易理解。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。

· 不重复读。解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。

· 幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

CREATETABLE `t` (

`a` int(11) NOT NULL PRIMARY KEY

) ENGINE=InnoDBDEFAULT CHARSET=utf8;

insertINTO t(a)values(1),(2),(3);

上面的文字，读起来并不是那么容易让人理解，以下用几个实验对InnoDB的四个事务隔离级别做详细的解释，希望通过实验来加深大家对InnoDB的事务隔离级别理解。

实验一：解释脏读、可重复读问题

更新事务

事务A READ-UNCOMMITTED

事务B READ-COMMITTED,

事务C-1 REPEATABLE-READ

事务C-2 REPEATABLE-READ

事务D SERIALIZABLE

set autocommit =0;

 

 

 

 

 

start transaction ;

 

 

 

start transaction;

 

insert into t(a)values(4);

 

 

 

 

 

 

 

select * from t;

1,2,3,4(脏读：读取到了未提交的事务中的数据)

 

select * from t;

1,2,3（解决脏读）

select * from t;

1,2,3

 

select * from t;

1,2,3

select * from t;

1,2,3

 

 

 

 

 

 

 

 

 

 

 

 

commit;

 

 

 

 

 

 

select * from t:

1,2,3,4

select * from t:

1,2,3,4

select * from t:

1,2,3,4 (与上面的不在一个事务中，所以读到为事务提交后最新的，所以可读到4)

select * from t:

1,2,3（重复读：由于与上面的在一个事务中，所以只读到事务开始事务的数据，也就是重复读）

select * from t:

1,2,3,4

 

 

 

 

commit（提交事务，下面的就是一个新的事务，所以可以读到事务提交以后的最新数据）

 

 

 

 

 

select * from t:

1,2,3,4

 

READ-UNCOMMITTED 会产生脏读，基本很少适用于实际场景，所以基本不使用。

实验二：测试READ-COMMITTED与REPEATABLE-READ

事务A

事务B READ-COMMITTED

事务C REPEATABLE-READ

set autocommit =0;

 

 

start transaction ;

start transaction;

start transaction;

insert into t(a)values(4);

 

 

 

 

select * from t;

1,2,3

 

select * from t;

1,2,3

 

 

 

 

 

 

commit;

 

 

 

select * from t:

1,2,3,4

select * from t:

1,2,3（重复读：由于与上面的在一个事务中，所以只读到事务开始事务的数据，也就是重复读）

 

 

commit（提交事务，下面的就是一个新的事务，所以可以读到事务提交以后的最新数据）

 

 

select * from t:

1,2,3,4

REPEATABLE-READ可以确保一个事务中读取的数据是可重复的，也就是相同的读取（第一次读取以后，即使其他事务已经提交新的数据，同一个事务中再次select也并不会被读取）。

READ-COMMITTED只是确保读取最新事务已经提交的数据。

当然数据的可见性都是对不同事务来说的，同一个事务，都是可以读到此事务中最新数据的。

starttransaction;

insertinto t(a)values(4);

select *from t;

1,2,3,4;

insertinto t(a)values(5);

select *from t;

1,2,3,4,5;

实验三：测试SERIALIZABLE事务对其他的影响

事务A SERIALIZABLE

事务B READ-UNCOMMITTED

事务C READ-COMMITTED,

事务D REPEATABLE-READ

事务E SERIALIZABLE

set autocommit =0;

 

 

 

 

start transaction ;

 

 

start transaction;

 

select a from t union all select sleep(1000) from dual;

 

 

 

 

 

 

insert into t(a)values(5);

 

insert into t(a)values(5);

 

insert into t(a)values(5);

insert into t(a)values(5);

 

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

SERIALIZABLE 串行化执行，导致所有其他事务不得不等待事务A结束才行可以执行，这里特意使用了sleep函数，直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒，而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。

SERIALIZABLE是相当严格的串行化执行模式，不管是读还是写，都会影响其他读取相同的表的事务。是严格的表级读写排他锁。也就失去了innodb引擎的优点。实际应用很少。

实验四：幻读 

一些文章写到InnoDB的可重复读避免了“幻读”（phantom read），这个说法并不准确。

做个试验：(以下所有试验要注意存储引擎和隔离级别)

mysql>show create table t_bitfly/G;
创建表 `t_bitfly` (
`id` bigint(20) NOT NULL 默认 '0',
`value` varchar(32) 默认 NULL ,
主键 (`id`)
) 引擎=InnoDB 默认字符集=gbk

mysql>select @@global.tx_isolation, @@tx_isolation;
------ ----------------- -----------------
| @@global.tx_isolation | @@tx_isolation |
-------------------------- -----------------
|可重复读取|可重复读取 |
-------------------------- -----------------

试验4-1：

tSessionA Session B
|
|开始交易；开始交易；
|
|从 t_bitfly 中选择 *;
|空集
| INSERT INTO t_bitfly VALUES (1, 'a');
|
|
|从 t_bitfly 中选择 *;
|空集
|提交；
|
|从 t_bitfly 中选择 *;
|空集
|
| INSERT INTO t_bitfly VALUES (1, 'a');
|错误 1062 (23000):
| key 1
v 重复输入“1”（妈的，明明刚刚告诉我没有这条记录）

这样就出现了幻读，以为表里没有数据，其实数据已经存在了，傻乎乎的提交后，才发现数据冲突了。

试验4-2：

tSessionA Session B
|
|开始交易；开始交易；
|
|从 t_bitfly 中选择 *;
| ------ -------
| |编号 |值|
| ------ -------
| | 1 |a |
| ------ -------
| INSERT INTO t_bitfly VALUES (2, 'b');
|
|
|从 t_bitfly 中选择 *;
| ------ -------
| |编号 |值|
| ------ -------
| | 1 |a |
| ------ -------
|提交；
|
|从 t_bitfly 中选择 *;
| ------ -------
| |编号 |值|
| ------ -------
| | 1 |a |
| ------ -------
|
|更新 t_bitfly SET value='z';
|匹配的行：2 更改：2 警告：0
| (怎么多一行出来)
|
|从 t_bitfly 中选择 *;
| ------ -------
| |编号 |值|
| ------ -------
| | 1 |z |
| | 2 |z |
| ------ -------
|
v

本事务中第一次读取出一行，完成一次更新后，另一个事务中提交的数据就出现了。也可以看做是一种幻读。

------

那么，InnoDB指出的可以避免幻读是怎么回事呢？

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别下运行，并使用innodb_locks_unsafe_for_binlog系统变量已禁用。在这种情况下，InnoDB 使用下一键锁进行搜索和索引扫描，这可以防止幻像行（请参阅第 13.6.8.5 节，“使用下一键锁定避免幻像问题”）。

准备的理解是，当隔离级别是可重复读，且取消innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描索引的时候使用的next-keylocks可以避免幻读。

关键点存在，是InnoDB默认对一个普通的查询也会加next-key锁，还是说需要应用自己来加锁呢？如果单看这一句，可能会以为InnoDB对普通的查询也加了锁，如果是，那和序列化（SERIALIZABLE）的区别又在

MySQL手册里还有一段：

13.2.8.5.使用 Next-Key Locking 避免幻象问题 (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html)

为了防止幻象，InnoDB 使用了一种称为下一个键锁定结合了索引行锁定和间隙锁定。

您可以使用下一个键锁定在应用程序中实现唯一性检查：如果您在共享模式下读取数据并执行以下操作如果没有看到要插入的行的重复项，那么您可以安全地插入行，并且知道在读取期间在行的后继上设置的下一个键锁会阻止任何人同时为您的行插入重复项。因此，下一个键锁定使您能够“锁定”表中不存在的某些内容。

我的理解是说，InnoDB 提供了下一个键锁，但需要应用程序自己去加锁。手册里提供一个例子：

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假设子表里有一行id为102），以及100-102，102的间隙都加上锁。

可以使用showinnodb status来查看是否给表加上了锁。

再看一个实验，要注意，表t_bitfly里的id主键字段。

实验4-3：

t SessionA Session B
|
| START TRANSACTION; START TRANSACTION;
|
| SELECT * FROM t_bitfly
| WHERE id<=1
| FOR UPDATE;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 |a |
| +------+-------+
| INSERT INTO t_bitfly VALUES (2, 'b');
| Query OK, 1 row affected
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 |a |
| +------+-------+
| INSERT INTO t_bitfly VALUES (0, '0');
| (waiting for lock ...
| then timeout)
| ERROR 1205 (HY000):
| Lock wait timeout exceeded;
| try restarting transaction
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 |a |
| +------+-------+
| COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 |a |
| +------+-------+
v

可以看到，用id<=1加的锁，只锁住了id<=1的范围，可以成功添加id为2的记录，添加id为0的记录时就会等待锁的释放。

MySQL manual里对可重复读里的锁的详细解释：

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

Forlocking reads (SELECT with FORUPDATE or LOCK IN SHARE MODE),UPDATE, and DELETE statements, lockingdepends on whether the statement uses a unique index with a unique searchcondition, or a range-type search condition. For a unique index with a uniquesearch condition, InnoDB locksonly the index record found, not the gap before it. For other searchconditions, InnoDB locksthe index range scanned, using gap locks or next-key (gap plus index-record)locks to block insertions by other sessions into the gaps covered by the range.

------

一致性读和提交读，先看实验，

实验4-4：

tSessionA Session B
|
| STARTTRANSACTION; START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 |a |
| +----+-------+
| INSERT INTO t_bitfly VALUES (2, 'b');
|
| COMMIT;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 |a |
| +----+-------+
|
| SELECT * FROM t_bitfly LOCK IN SHARE MODE;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 |a |
| | 2 |b |
| +----+-------+
|
| SELECT * FROM t_bitfly FOR UPDATE;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 |a |
| | 2 |b |
| +----+-------+
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 |a |
| +----+-------+
v

如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。

本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据。

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

Ifyou want to see the “freshest” state of the database, you should use either theREAD COMMITTED isolation level or a locking read:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

------

结论：MySQLInnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-keylocks。

相关标签：
隔离
来源：php.cn
上一篇：RDS中的MYSQL备份恢复_MySQL 下一篇：MySQL数据库管理101_MySQL
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
在网络应用程序中显示版本信息的最佳实践是什么？ 我正在开发一个网络应用程序。在网络应用程序中显示版本信息的最佳实践是什么？我正在使用语义版本控制，并且我已经有了semver，例如1.2.0但我很好奇在哪里显示它的最佳方式以及如何...
来自于 2024-04-06 19:13:16
0
2
476
如何在Reactjs中选择多个复选框 我有一个选项列表，我想在其中选择多个复选框，但无法做到这一点，这段代码有什么问题？沙箱网址
来自于 2024-04-06 18:55:21
0
1
396
实体仓库扩展了JpaRepository<Entity, Id>和JpaSpecificationExecutor<Entity>，EntitySpec扩展了Specification<Entity> 这是我的端点http://localhost:8080/country/all回复所有国家都可以当我想过滤它们时返回未找到国家为空并返回异常thrownewRecordNotFou...
来自于 2024-04-06 17:11:03
0
1
421
MERN stack搜索框和复选框的正则表达式过滤器 我正在尝试通过边做边学来了解MERN堆栈如何协同工作，并且我正在遵循bezcoder的这些教程：Node.js/Express/MongoDb（Github整个代码）和Reactj...
来自于 2024-04-06 14:53:12
0
1
425
将在MYSQL数据库中将ASP Classic代码存储为字符串的可能性 是否可以将asp经典代码存储到MySQL数据库中并让它在拉取时执行代码？例如将变量名存储到字符串中并让它在显示之前执行ASP？我怀疑这是不可能的，因为MySQL请求在页面加载后运行...
来自于 2024-04-06 13:38:43
0
1
358
相关专题
更多>
热门推荐
热门教程
更多>
相关教程
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板