MySql事务隔离机制

àì夳堔傛蜴生んèń 2023-03-14 08:17 36阅读 0赞

## 一.事务(Transaction) ##

> **事务是一个最小的不可再分的工作单元,事务只和DML语句有关,****用来管理insert，update和delete语句****，在 MySql 中只有使用了** **Innodb** **数据库引擎的数据库或表才支持事务。**

> **事务是必须满足4个条件(ACID)： **
> 
> **原子性(Atomicity): 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。**
> 
> **一致性(Consistency):在事务开始之前和事务结束以后，数据库的完整性没有被破坏。应用系统应该从一个正确的状态到另一个正确的状态,这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。**
> 
> **隔离性(Isolation):数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。防止出现：脏读、幻读、不可重复读。**
> 
> **持久性(Durability):事务处理结束后，对数据的修改就是永久的。**

## ** 二.事务隔离级别** ##

> **加锁可以非常完美的保证隔离性，但是会造成数据库性能的大大下降。所以视操作不同,事务管理分为了不同的情况。**
> 
> **a.如果两个事务并发的修改则必须隔离开。**
> 
> **b.如果两个事务并发的查询则完全不用隔离。**
> 
> **c.如果一个事务修改，另一个事务查询，则可能出现****脏读、不可重复读、幻读****的情况，隔离级别主要针对这种情况。**

> **1.脏读：一个事务读取到另一个事务未提交的数据。**
> 
> **2.不可重复读：一个事务读取到另一个事务已经提交的数据。例如事务A读取了一条记录，此时事务B修改了该条数据并提交成功，事务A再次查询该条数据发现与第一次读取的不一样，即为不可重复度（同一个事务内重复读取的数据不一样，则理解成不可重复读）。**
> 
> **3.幻读：一个事务读取到另一个事务已经提交的数据，但与不可重复读不同的是，不可重复读的原因在于update，而幻读源于其他事务 insert 或 delete 了记录导致记录条数不一致。**

> **针对上面的三个问题，数据库提出四大隔离级别：**
> 
> **读未提交(read uncommitted) :** **不作任何隔离，具有脏读、不可重复读、幻读问题**
> 
> **读已提交(read committed) :** **可防止脏读，不能防止不可重复读和幻读问题**
> 
> **可重复读(repeatable read) :** **可以防止脏读、不可重复读，不能防止幻读问题（mysql默认是这个隔离级别）**
> 
> **串行化(serializable) :** **数据库运行在串行化，上述问题都可以防止，只是性能非常低**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70][]

## **三.Mysql各个隔离级别验证** ##

**MySql的默认隔离级别是** **可重复读(repeatable read)****，解决了脏读，不可重复读，但是未解决幻读**

**MySql 8.0以上版本查看事务隔离级别:**

select @@transaction_isolation;

**MySql 8.0以下版本查看事务隔离级别:**

select @@tx_isolation;

**Mysql修改隔离级别:**

**global为修改全局隔离级别，session为修改当前会话的隔离级别**

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

### **下面我们来验证一下：** ###

**创建一个测试表：**

CREATE TABLE `user` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL COMMENT '姓名',
      `age` int(3) DEFAULT NULL COMMENT '年龄',
      `deposit` varchar(255) DEFAULT NULL COMMENT '存款',
      PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**插入两条测试数据：**

INSERT INTO `user` VALUES ('1', '小红', '25', '1000');
    INSERT INTO `user` VALUES ('2', '小兰', '26', '2000');

### **3.1 测试读未提交隔离级别(read uncommitted)** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 1][]

**由图可见，事务A读到了事务B未提交的数据，发生了脏读**

### **3.2 测试读已提交隔离级别(read committed)** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 2][]

**由图可见，读已提交解决了脏读，但是事务A在未提交前读到了事务B提交的数据，发生了不可重复读。**

### **3.3 测试可重复读隔离级别(repeatable read)** ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 3][] ###

**由图可见，可重复读隔离级别解决了脏读和不可重复读**

**我们看下是否解决了幻读：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 4][]

**咦！跟我们设想的不对啊,并没有发生幻读,难道repeatable read隔离级别已经解决了幻读？下面我们看下另外一种测试形式:**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 5][]  
可以看到在事务A更新之后,影响到的是三条数据,并且再次查询也是三条数据,简直是幻觉啊。**

**但为什么是这样呢？**

**要了解这个原因,则还需要知道另一个概念:MVCC**

## **四.MVCC** ##

### 1.MVCC基本概念 ###

> **MVCC全称 Mutli Version Concurreny Control，****多版本并发控制****，是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现****读已提交(read committed)和可重复读(repeatable read)****隔离级别的实现。它通过行的多版本控制方式来读取当前执行时间数据库中的行数据。原理是****将数据保存在某个时间点(版本号)的快照来实现的****，MySQL中MVCC的实现方式是在数据库保存最新版本的数据，但是会在使用undo时动态重构旧版本数据。这样就可以实现不加锁读。**
> 
> **MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此效率高开销低。MVCC实现了非阻塞的读操作，写操作也只锁定必要的行。**

### **2.InnoDB的MVCC实现机制** ###

> **InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是****系统版本号****。每开始一个新的事务，****系统版本号都会自动递增****。****事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较****。下面看一下在可重复读(repeatable read)隔离级别下，MVCC具体是如何操作的：**

**MySQL中，会在表中每一条数据后面添加两个隐藏字段：**

> **创建版本号：****创建一行数据时，将当前系统版本号作为创建版本号赋值**  
> **删除版本号：****删除一行数据时，将当前系统版本号作为删除版本号赋值**

**在RR隔离级别下，MVCC的操作如下：**

> **select读取数据的规则必须同时满足以下两个条件：  
>    a. 创建版本号 <= 当前事务版本号:InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务本身插入或者修改过的。（这也是我们为什么在可重复读隔离级别下,读操作不会查到其他事务新增的数据,但是进行修改操作后却读出了三条数据的原因）  
>    b. 删除版本号 为空 或 > 当前事务版本号:这可以确保事务读取到的行，在事务开始之前未被删除。**
> 
> **insert操作:  
> InnoDB为新插入的每一行保存当前系统版本号作为行的创建版本号。**
> 
> **delete操作:  
> InnoDB为删除的每一行保存当前系统版本号作为行的删除版本号。**
> 
> **update操作:  
> 插入一条新记录，保存当前系统版本号为行创建版本号，同时保存当前系统版本号到原来的行删除版本号，实际上这里的更新是通过delete和insert实现的。**

**但是这里有个问题我们看下：**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 6][]**

**比如依次开启事务A、B、C，我们在事务A中插入一条数据并提交，在事务C中可以查看到，这不就是另类的幻读？然后再在事务B中插入一条数据并提交，但是在事务C中却查找不到了，这个并不符合select时的规则，因为事务C的事务版本号明显大于或等于事务B中插入数据的行版本号，但仍然搜索不到，这是为什么呢？**

**要分析这个问题，我们还要了解两个概念：快照读和当前读**

### **3.快照读和当前读** ###

> **MVCC机制虽然让数据可重复读，但有时我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读(snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。**

**快照读：**

> **当执行select操作时innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了也不会影响当前select的数据，这就实现了可重复读了。如上所示，开启了事务A、B、C，这时候事务A插入了一条数据然后提交,然后事务C这时候执行select，那么返回的数据中就会有事务A添加的那条数据。但是之后无论再有其他事务插入并提交数据都没有关系，因为快照已经生成了，事务C后面的select都是根据快照来的。**

**当前读：**

> **对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。假设要更新一条记录，但是在另一个事务中已经删除掉这条数据并且提交了，如果更新就会产生冲突，所以在update的时候需要知道最新的数据。**

**select的当前读需要手动的加锁：**

select * from user lock in share mode;

select * from user for update;

**综上所述，在快照读读情况下，MySQL通过MVCC可以避免幻读。但是在当前读情况下幻读依然存在，所以说MVCC对于幻读的解决是不彻底的**

### 3.那么如何彻底解决幻读 ###

**法一：使用串行化读的隔离级别，但该方法效率太低。**

**法二：我们先大概看看mysql InnoDB引擎下的行锁:**

> **Record lock：****单个行记录上的锁**  
> **Gap lock：****间隙锁，锁定一个范围，不包括记录本身**  
> **Next-key lock：****Record + Gap 锁定一个范围，包含记录本身**

> **在纯粹的读操作时,Innodb用 MVCC 解决幻读问题,新的 insert 和 update 不会阻塞。  
> 在涉及到写操作时,Innodb用 MVCC 和 Next-key lock 解决幻读问题,新的 insert 和 update 会阻塞。**

**当我们为 select 后加上 for update 或者 lock in share mode,则查找到的行会被加上 Next-key lock**

**看下测试:**

**a.select 后加 for update**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 7][]**

**b.select 后加 lock in share mode**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 8][]**

### **如图所示,我们解决了幻读,然而,在真正的项目中,幻读不能被接受的场景貌似也不多见。。。。** ###

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70]: /images/20230312/dd296b20768d41d8b705c8f1719d9222.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 1]: /images/20230312/9e5493a1ae114ccbb388e6430a5d658c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 2]: /images/20230312/67f9c016c1b54658a6fd82659e118857.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 3]: /images/20230312/0c6175d7b6144319861d0b9db333e81a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 4]: /images/20230312/da3c20742c7240218e5e96630ed4820c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 5]: /images/20230312/d046a29b51d842f6ad4a5d1aaf166263.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 6]: /images/20230312/5f44cad9a6df4419990245bf6ea79e8d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 7]: /images/20230312/2dc8283c59ad426e981d6ebd6c126b88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h5eTEwMjg_size_16_color_FFFFFF_t_70 8]: /images/20230312/5db369cb758040379b04bb4980ad7951.png