MySQL InnoDB四个事务级别 与 脏读、不重复读、幻读

最新推荐文章于 2023-05-06 14:15:40 发布
最新推荐文章于 2023-05-06 14:15:40 发布
阅读量403 收藏
点赞数
分类专栏： 数据库 mysql 文章标签： select 事务 mysql
数据库 同时被 2 个专栏收录
122 篇文章 0 订阅
订阅专栏
mysql
117 篇文章 1 订阅
订阅专栏

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;
CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default '0',
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

mysql>select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

试验4-1：

SessionA                                       Session B
|
| START TRANSACTION;                           START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
|                                       INSERT INTO t_bitfly VALUES (1, 'a');
|                             
|
| SELECT * FROM t_bitfly;
| empty set
|                                        COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, 'a');
| ERROR 1062 (23000):
| Duplicate entry '1' for key 1
v (shit,刚刚明明告诉我没有这条记录的)

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

试验4-2：

SessionA               Session B
|
| START TRANSACTION;          START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 |a     |
| +------+-------+
|                        INSERT INTO t_bitfly VALUES (2, 'b');
|                            
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 |a     |
| +------+-------+
|                       COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 |a     |
| +------+-------+
|
| UPDATE t_bitfly SET value='z';
| Rows matched: 2  Changed:2  Warnings: 0
| (怎么多出来一行)
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 |z     |
| |    2 |z     |
| +------+-------+
|
v

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

------

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

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

By default, InnoDB operatesin REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlogsystem variable disabled. In this case, InnoDB uses next-key locks for searchesand index scans, which prevents phantom rows (see Section 13.6.8.5, “Avoidingthe Phantom Problem Using Next-Key Locking”).

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

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

MySQL manual里还有一段：

13.2.8.5. Avoiding the PhantomProblem Using Next-Key Locking (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html)

Toprevent phantoms, InnoDB usesan algorithm called next-key locking that combinesindex-row locking with gap locking.

Youcan use next-key locking to implement a uniqueness check in your application:If you read your data in share mode and do not see a duplicate for a row youare going to insert, then you can safely insert your row and know that thenext-key lock set on the successor of your row during the read prevents anyonemeanwhile inserting a duplicate for your row. Thus, the next-key lockingenables you to “lock” the nonexistence of something in your table.

我的理解是说，InnoDB提供了next-key locks，但需要应用程序自己去加锁。manual里提供一个例子：

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。

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

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

实验4-3：

 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。

大树叶
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
通过实例分析MySQL中的四种事务隔离级别
01-21
前言 在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离...在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。 串行读(Seria
数据库事务、索引
qq_33789406的博客
07-08 174
数据库事务 四大特性 原子性——undo log 一致性——完整性约束，合法的数据状态 隔离性 持久性——redo log buffer pool 事务并发三大问题 事务的四种隔离级别 读未提交 无需解决，直接输出对应的查询内容 读已提交 隐藏字段 MySQL 的 InnoDB 会维护一系列不暴露给用户的隐藏字段，其中有3个用于实现快照读（非阻塞读） DB_TRX_ID 最新一次修改本行记录的操作ID 同一个事务，同一个操作要加以区分，这里指操作 DB_ROLL_PTR 回滚指针，存储的
Mysql引擎innodb的脏读，不可重复读，幻读问题
felix
10-26 466
不可重复读是指在一个事务内多次读取一个集合数据，这个事务还没有结束时，另外一个事务对这个集合数据做了DML操作，因此第一个事务中的两次读取数据之间，由于第二个事务的操作，造成了两次数据不一样，这种情况称为不可重复读。示例中，会话A开启一个事务读取数据，读到一条数据，此时会话B插入了一条数据，并且提交了，随后会话A又进行了一次读取数据，读到两条，造成会话A两次读取数据记录不一致。不可重复读和脏读的区别是：脏读是读取未提交的数据，不可重复读是读取到已提交的数据，但违反了数据库事务一致性的要求。
什么是脏读，幻读，不可重复读，丢失修改？
热门推荐
来杯Java
06-18 1万+
首先介绍事物的四大特性(ACID)： 原子性：事务中的最小执行单位，要么全部执行，要么全部失败（回滚）； 一致性：执行事务前后，保证数据一致，多个事务对同一个数据读取的结果是相同的； 例如转账案例假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间...
MySQL四种事务隔离级别详解
01-21
本文实验的测试环境：Windows 10+cmd+MySQL5.6.36+InnoDB 一、事务的基本要素（ACID） 　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会...
MySQL经典面试题（含答案）.zip
06-26
什么是脏读、不可重复读、幻读呢？ datetime和timestamp的区别？ varchar和char有什么区别？ count(1)、count(*) 与 count(列名) 的区别？ exist和in的区别？ truncate、delete与drop区别？ union与union all的区别...
MySQL管理之道 性能调优、高可用与监控.part2.rar
09-28
从故障诊断、表设计、sql优化、性能参数调优、mydumper逻辑、xtrabackup热备份与恢复、mysql高可用集群搭建与管理、mysql服务器性能和服务监控等方面多角度深入讲解了如何去管理与维护mysql服务器。 书中内容以实战...
PHP和MySQL WEB开发(第4版)
05-31
13.3.2 通过InnoDB使用事务 13.4 外键 13.5 存储过程 13.5.1 基本示例 13.5.2 局部变量 13.5.3 游标和控制结构 13.6 进一步学习 13.7 下一章 第三篇 电子商务与安全性 第14章 运营一个电子商务网站 14.1 我们要实现...
MySQL-InnoDB的事务隔离级别
weixin_37536020的博客
12-11 989
MySQL-InnoDB的事务隔离级别 及 MVCC实现原理
InnoDb事务隔离级别
weixin_42203131的博客
05-06 755
在标准的隔离级别中，幻读是由更高的隔离级别serializable解决的，但是它也可以通过Mysql提供的Next-key锁解决： repeatable read和read uncommited其实是矛盾的，如果保证了前者就看不到已提交的事务，如果保证了后者，就会导致两次查询的结果不同，mysql为我们提供了一种折中的方式，能够在Repeatable read模式下加锁访问已经提交的数据，其本身并不能解决幻读的问题，而是通过文章前面提到的Next-key锁来解决。此隔离级别，不会使用，忽略。
MySql Innodb事务隔离级别
Fibona的专栏
07-11 1037
Mysql Innodb 的事务隔离级别介绍，及相关示例
MySQL InnoDB存储引擎隔离级别及脏读、不重复读、幻读
u012438810的专栏
08-21 270
前记： ORACLE不支持Read Uncommitted和Repeatable Read事务隔离级别； InnoDB默认是RR，使用Next-Key Lock算法避免幻读，达到Serializable隔离级别； 隔离级别越低，事务请求所越少或保持锁的时间越短； 一、隔离级别查询和修改     #查询全局事务隔离级别     select @@global.tx_isolatio...
一文详解MySQL InnoDB脏读、不可重复读、幻读（通俗易懂）
Java搜索工程技术栈
03-08 485
在MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。 MySQL 是支持多事务并发执行的。否则来一个事务处理一个请求，处理一个人请求的时候，其它事务都等着，那估计都没人敢用MySQL作为数据库,因为用户体验太差，估计都要砸键盘了。 既然事务可以并发操作,这里就有一些问题：一个事务在写数据的时候，另一个事务要读这行数据，该怎么处理？一个事务在写数据，另一个数据也要写这行数据，又该怎么处理这个冲突？ 这就是并发事务所产生的一些问题。具体来.
Innodb中的事务隔离级别和锁的关系
美团技术团队
08-20 1364
前言: 我们都知道事务的几种性质，数据库为了维护这些性质，尤其是一致性和隔离性，一般使用加锁这种方式。同时数据库又是个高并发的应用，同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力。所以对于加锁的处理，可以说就是数据库对于事务处理的精髓所在。这里通过分析MySQL中InnoDB引擎的加锁机制，来抛砖引玉，让读者更好的理解，在事务处理中数据库到底做了什么。 #一...
MySQL~InnoDB引擎解决脏读,不可重复读,幻读,丢失更新的原理(lock事务锁、自增长锁、Record Lock、Gap Lock、Next-Key Lock、死锁)
Shangxingya的博客
12-10 926
文章目录Lock事务锁InnoDB引擎中的行锁事务中的行锁机制俩段锁行锁的三个实现算法事务的四种隔离级别实现原理Read UnCommitted（读未提交）Read Committed（读提交）Repeatable Read（可重复读）丢失更新Serializable (串行化)自增长锁死锁 Lock事务锁 在了解数据库锁之前，首先就要区分开lock和latch。在数据库中，lock和latch虽然都是锁，却有着截然不同的含义。 latch通常被我们称为闩锁(轻量级锁)，因为其要求锁定的时间必须非常
MySQL技术内幕 InnoDB存储引擎：锁问题（脏读、不可重复读）
勿忘初心
10-18 947
1、脏读 在理解脏读（Dirty Read)之前，需要理解脏数据的概念。但是脏数据和之前所介绍的脏页完全是两种不同的概念。脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写人到了重做日志文件中。而所谓脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交（commit)。 对于脏页的读取，是非常正常的...
innodb默认隔离级别可重复度存在幻读吗？如何解决的？
最新发布
07-10
是的，InnoDB默认隔离级别下（REPEATABLE READ）存在幻读的问题。 幻读是指在同一个事务内，多次执行相同的查询，但结果集却不一致的情况。这是由于其他事务在查询期间插入或删除了满足查询条件的行导致的。 为了解决幻读问题，InnoDB引入了多版本并发控制（Multi-Version Concurrency Control，简称MVCC）机制，并提供了两种解决幻读问题的方式： 1. 快照读（Snapshot Read）：在REPEATABLE READ隔离级别下，默认使用快照读。快照读会在事务开始时创建一个一致性视图，并使用该视图来读取数据。其他事务对数据的修改不会影响当前事务的读取操作，从而避免了幻读问题。 2. 当前读（Current Read）：在REPEATABLE READ隔离级别下，可以使用当前读来解决幻读问题。当前读会对查询的数据加锁，确保其他事务不能插入或删除符合查询条件的行。可以使用SELECT ... FOR UPDATE语句或SELECT ... LOCK IN SHARE MODE语句来进行当前读操作。 需要注意的是，REPEATABLE READ隔离级别下，使用快照读可以避免大部分幻读问题，但在某些情况下仍然可能出现幻读。如果需要完全避免幻读，可以将隔离级别提升至SERIALIZABLE，但这可能会影响并发性能。 总结起来，InnoDB通过MVCC机制和快照读、当前读的方式来解决幻读问题。开发者可以根据具体的业务需求和性能要求选择适当的隔离级别和读取方式来处理幻读问题。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值