MySQL事务--万文长字探究隔离性实现原理

r囧r小猫 2024-03-16 11:35 62阅读 0赞

### 1 隔离性简介 ###

事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）四个特性，简称 ACID，缺一不可。这篇文章旨在讲清楚隔离性的产生背景、有何作用及实现原理等。

#### 1.1 产生背景 ####

并发环境下保证共享资源的线程安全性。

数据库表中的一行行数据就是共享资源，链接至数据库服务端的众多客户端就好比是多线程环境。

#### 1.2 作用 ####

保证数据库表数据，在每个事务间是读写安全，写写安全的。

### 2 MySQL事务间的读写冲突 & 解决方案 ###

事务间的读写冲突，分为三种情况：**脏读、不可重复读、幻读**。

#### 2.1 脏读 ####

脏读指当前事务能够读到其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中。读到了不一定最终存在的数据，这就是脏读。

#### 2.2 不可重复读 ####

不可重复读指在同一事务内，不同时刻读到的同一批数据，结果显示可能不一样。原因是这批数据可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。

#### 2.3 幻读 ####

MySQL 官方给出了幻读的定义：**在同一事务中，相同的 SELECT 语句，得到的结果不一致（又造成了不可重复读，破坏了可重复读的隔离级别），并且第二次 select 的 raws 只会比第一次 select 的 raws 要多，这些多出的行，被称为“幻行”（即幻读只会发生在新增数据的场景下）**。

> if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a“phantom”row.

关于幻读，还有一道很经典的面试题：**InnoDB RR 隔离级别下，是否解决了幻读？**

这里先给出结论：快照读场景下，利用MVCC规避了大部分幻读场景，当前读场景下，利用间隙锁解决了幻读。事实上，快照读场景下，MVCC无法规避的幻读场景，在实际应用中也很少出现，因为一般没人那样写SQL，因此可以理解为MVCC + 间隙锁完全解决了幻读问题。

因为幻读的产生及解决方式涉及到 MVCC、快照读、当前读及间隙锁等背景知识，所以这部分的详细内容在下面“再探幻读”一节还会阐述。

--------------------

SQL 标准定义了四种隔离级别用于解决上述这些读写冲突，MySQL 全都支持，下面一一分析。（注：从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，**可重复读**是 MySQL 的默认级别。）

#### 2.4 读未提交 ####

没有解决上述任何一种读写冲突，即 MySQL 面对事务间的读写冲突没有做任何事情，性能最好，也最不实用。

#### 2.5 读已提交 ####

解决了脏读的问题，即当前事务只能读到其他事务已经提交过的数据。

读已提交是大多数流行数据库的默认隔离级别，比如 Oracle，但不是 MySQL 的默认隔离级别。

#### 2.6 可重复读 ####

解决了脏读，不可重复读的问题，即当前事务不会读到其他事务对同一批数据的修改，即使其他事务已提交。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题。

#### 2.7 串行化 ####

解决了脏读，不可重复读，幻读的问题，它将事务的执行变为顺序执行，后一个事务的执行必须等待前一个事务结束，虽然隔离效果最好，但性能最差。

#### 2.8 总结 ####

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>读未提交</td> 
   <td>可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>读已提交</td> 
   <td>不可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>可重复读</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>可能（MVCC无法规避的某些场景）</td> 
  </tr> 
  <tr> 
   <td>串行化</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>不可能</td> 
  </tr> 
 </tbody> 
</table>

### 3 MVCC-MySQL解决读写冲突的实现原理 ###

我们平时在做开发，若在多线程环境下想要保证线程安全，大多数的解决方式都是加锁--同步锁、读写锁等。MySQL 在解决读写冲突时也用到了锁，但又不仅仅只用到了锁，需要分情况讨论。

对于同一条数据库记录来说：

*  读未提交，性能最好，因为它压根儿就不加锁，所以可以理解为事务间毫无隔离性
 *  串行化，性能最差，可以理解为事务运行时使用了排它锁，多个事务间调度必须串行执行

这两种隔离级别在实际中几乎没人使用，读未提交的线程安全性毫无保证，串行化则性能太差。

同时，MySQL 能够支持众多不同业务下的高并发场景需求，因此如果使用锁机制实现事务隔离性，则读写性能可能还是会差强人意。因此，为了提⾼数据库的并发性能，⽤更好的⽅式去处理读-写冲突，做到即使有读写冲突，也不会加锁，MVCC(Multi-Version Concurrency Control) 即多版本并发控制诞生了。

MVCC 除了提高数据库的读写性能外，还解决了脏读、不可重复读、幻读等问题。但是不能解决写写冲突下的更新丢失问题。

MVCC 的实现原理主要依赖记录中的隐式字段，undo 日志，readview 等，接下来会一一介绍。不过在此之前，先要了解一下什么是当前读和快照读：

*  当前读：`select lock in share mode`(共享锁)、`select for update`、`update`、`insert`、`delete`这些操作都是一种当前读，它会读取记录的最新版本，并对读取的记录进行加锁，保证其他并发事务不能修改当前记录
 *  快照读：`select`操作就是快照读，即不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；快照读可能读到的并不一定是数据的最新版本，而是之前的历史版本

实际上 MVCC 只是一个抽象概念，即：维持一个数据的多个版本，使得读写操作没有冲突，并非实现。快照读相当于是 MySQL 对 MVCC 模型中非阻塞读功能的实现。相对而言，当前读则是悲观锁的具体功能实现。

#### 3.1 隐式字段 ####

数据表中的每条记录，除了我们自定义的字段外，还有数据库隐式定义的 `DB_TRX_ID, DB_ROLL_PTR, DB_RAW_ID` 等3个字段。

*  DB\_TRX\_ID：最近修改（更新、插入）事务ID：记录创建这条记录/最后一次修改该记录的事务ID
 *  DB\_ROLL\_PTR：回滚指针，配合 undo 日志，指向这条记录的上一个版本
 *  DB\_RAW\_ID：隐藏主键（自增id），如果数据表没有主键，则 InnoDB 会自动以 DB\_ROW\_ID 产生一个聚簇索引

例，person 表的某条记录：

![format_png][]

#### 3.2 undo log ####

undo log 实质分两种：

*  insert undo log：事务在 insert 记录时产生的 undo log，只在回滚时需要，并在事务提交后立即丢弃
 *  update undo log：事务在 update 或 delete 记录时产生的 undo log，不仅事务回滚时需要，快照读时也需要，不能随便删除，只有在当前读或事务回滚不涉及该日志时，才会被 purge 线程统一清理

对 MVCC 有实质帮助的是 update undo log。

#### 3.3 undo log的生成流程 ####

举个例子：

1.有个事务向 person 表插入了一条新纪录，隐式主键是1，回滚指针和事务ID假设是 NULL，如下：

![format_png 1][]

2.事务1将该记录的 name 改为 tom：

*  事务1修改该行(记录)数据时，数据库会先对该行加`排他锁`
 *  然后把该行数据拷贝到 `undo log` 中作为旧记录，即在 `undo log` 中有当前行的拷贝副本
 *  拷贝完毕后，修改该行 name 为 Tom，并且修改隐藏字段的事务 ID 为当前事务1的 ID（事务 ID 默认从1开始递增），回滚指针指向拷贝到 `undo log` 的副本记录，即表示该行数据的上一个版本就是它
 *  事务提交后，释放锁

修改成功后，该行数据的组织形式如下：

![format_png 2][]

3.事务2继续修改该行记录，将 age 修改为 30 岁：

*  同样，数据库也先为该行加锁
 *  然后把该行数据拷贝到 `undo log` 中作为旧记录，发现该行记录已经有 `undo log` 了，那么最新的旧数据将作为链表的表头，插在该行记录的 `undo log` 的最前面
 *  修改该行 age 为 30 岁，并且修改隐藏字段的事务 ID 为当前事务2的 ID，也就是 2，回滚指针指向刚刚拷贝到 `undo log` 的副本记录
 *  事务提交，释放锁

最终该行数据的组织形式如下：

![format_png 3][]

如上述过程，不同事务或相同事务对同一记录的修改，会导致该记录的`undo log`成为一条记录版本的链表，`undo log` 的链首就是最新的旧记录，链尾就是最早的旧记录。

#### 3.4 readview读视图 ####

Read View 就是事务进行快照读时生产的读视图(Read View)，即数据库系统当前的一个快照，读视图会记录并维护系统当前活跃事务的 ID（当每个事务开启时，都会被分配一个递增 ID，所以越新的事务，ID 值越大）。

Read View 主要用来做可见性判断，即当某个事务执行快照读的时候，会对该记录创建一个读视图，把它比作条件来判断当前事务能够看到该行记录哪个版本的数据，即可能是最新的数据，也有可能是 undo log 里某个版本的数据。

Read View 有三个全局属性：

1.  trx\_list：一个数值列表，维护生成 Read View 时系统正活跃的事务 ID 列表
2.  low\_limit\_id：trx\_list 列表中最小的事务 ID
3.  up\_limit\_id：ReadView 生成时系统尚未分配的下一个事务 ID，即已出现过事务 ID 的最大值 + 1

Read View 的可见性算法：将当前的事务 ID、产生快照读的记录的最新事务 ID（`DB_TRX_ID`）及 readview 维护的事务 ID 取出来，进行比较，如果`DB_TRX_ID`不符合可见性，则通过`DB_ROLL_PTR`回滚指针取出`Undo Log`中上一版本的`DB_TRX_ID`再比较，即遍历链表的`DB_TRX_ID`，直到找到满足特定条件的`DB_TRX_ID`，那么这条记录就是当前事务所能看见的版本。

可见性算法的规则如下：

1.  比较产生快照读的记录的最新事务 ID（DB\_TRX\_ID）与 low\_limit\_id, 如果 DB\_TRX\_ID < low\_limit\_id，说明此版本的记录对应的事务已提交，则当前事务能看到`DB_TRX_ID`对应的版本记录，如果大于等于则进入下一个判断
2.  如果 DB\_TRX\_ID >= up\_limit\_id，则代表`DB_TRX_ID`所在的记录是在 Read View 生成后才出现的，因此对当前事务不可见，如果小于则进入下一个判断
3.  如果`DB_TRX_ID >= low_limit_id && DB_TRX_ID < up_limit_id`，则判断`trx_list.contains (DB_TRX_ID)`(即 DB\_TRX\_ID 是否在活跃事务之中)。如果在，则代表 Read View 生成时，该记录版本对应的事务还在活跃，没有 Commit，因此此记录版本当前事务不可见；如果不在，则说明，此版本记录对应的事务在 Read View 生成之前就已经 Commit 了，因此当前事务可见
4.  如果最新记录版本不满足事务可见性，则遍历 undo 日志链表，取更早的记录版本，重复上述判断规则，直到找到可见的版本记录为止

**从可见性算法的实现规则来看，MVCC 解决了脏读的问题，即实现了读已提交。**

#### 3.5 整体流程 ####

在了解了 MVCC 的基本实现原理后，我们举个例子，再来模拟一下 MVCC 的整体流程。

1.事务 2 对某行数据执行了快照读，数据库为该行数据生成一个 Read View 读视图，假设当前事务 ID 为 2，同时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交了更新。Read View 记录了系统当前活跃的事务ID——1，3，维护在 trx\_list 上。

<table> 
 <thead> 
  <tr> 
   <th>事务1</th> 
   <th>事务2</th> 
   <th>事务3</th> 
   <th>事务4</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>事务开始</td> 
   <td>事务开始</td> 
   <td>事务开始</td> 
   <td>事务开始</td> 
  </tr> 
  <tr> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>修改且已提交</td> 
  </tr> 
  <tr> 
   <td>进行中</td> 
   <td>快照读</td> 
   <td>进行中</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

2.Read View 除了维护 trx\_list 列表，还有两个属性：low\_limit\_id、up\_limit\_id。在这个例子中，low\_limit\_id 就是 1，up\_limit\_id 就是 4 + 1 = 5，trx\_list 集合的值是 1、3。Read View 如下图：

![format_png 4][]

3.在此例子中，只有事务4修改过该行记录，并在事务2执行快照读前就提交了事务，所以当前该行数据的`undo log`如下图所示；事务2在快照读的时候，会拿该行记录的 DB\_TRX\_ID 去跟 up\_limit\_id，low\_limit\_id 和 trx\_list 比较，以此判断事务2能看到该记录的版本是哪个

![format_png 5][]

4.判断过程如下：

*  先拿该记录最新的 DB\_TRX\_ID（即4）跟 Read View 的 low\_limit\_id 比较，是否满足`DB_TRX_ID(4) < low_limit_id(1)`，不符合条件，继续下一步判断
 *  判断`DB_TRX_ID(4) >= low_limit_id(5)`，也不符合条件，继续下一步判断
 *  最后判断`trx_list.contains(4)`，发现事务4并不在当前活跃事务列表中，即 Read View 生成时事务4已提交，符合可见性条件，因此事务2能读到事务4所提交的记录版本

MVCC 可见性算法的整体流程如下：

![format_png 6][]

#### 3.6 RR隔离级别的实现 ####

<table> 
 <thead> 
  <tr> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>开启事务</td> 
   <td>开启事务</td> 
  </tr> 
  <tr> 
   <td>快照读查询金额为500</td> 
   <td>快照读查询金额为500</td> 
  </tr> 
  <tr> 
   <td>更新金额为400</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>提交事务</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>快照读</code>金额为？</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>select lock in share mode<code>当前读</code>金额为400</td> 
  </tr> 
 </tbody> 
</table>

如上图，在 RC 隔离级别下，事务 B 进行快照读的结果为 400，在 RR 隔离级别下，结果为 500。

Read View 的使用方式，造成 RC、RR 级别下快照读的结果不同。

*  RC 级别：每次快照读都会新生成一个 Read View，因此可能导致同一事务中前后读取同一条数据的结果并不相同
 *  RR 级别：在整个事务中，只会记录产生的第一个 Read View，此后对数据进行快照读的时候，使用的是同一个 Read View，因此对 Read View 生成后的修改不可见

#### 3.7 再探幻读 ####

3.7.1 幻读的再解释

为了加深大家对 2.3 小结中幻读定义的印象，我举下面几个例子：

**例子一**

![format_png 7][]

如上图，步骤 6 读到了另一个事务新增的数据，这个不是幻读，幻读是违背了可重复读的定义（同一个 select 语句，当前读的 select 与快照读的 select 还是不一样的），即如果步骤 5 所读到的数据与步骤 2 不一致，才叫幻读。

**例子二**

![format_png 8][]

如上图，上图描述有误，RR 隔离级别下，根据 MVCC 读视图的生成规则，由于事务 A 中的第二个 Select 语句使用的 Read View 与第一个 Select 语句是一样的，因此事务 B 插入的数据不符合可见性原则，因此也不会发生幻读。

**例子三**

![format_png 9][]

如上图，id 列是主键，由于事务 B 已经插入了`id=30`的数据，因此即使事务 A 两次快照读都没有读到`id=30`的数据，步骤 6 还是会插入失败。这个也不是幻读，为了满足 RR 隔离级别，这个现象是正常的，符合 SQL 标准。

3.7.2 MVCC无法规避的幻读场景

3.7.1小节中同时说明了那些本应该出现，但 MVCC 已经规避了的幻读场景，下面看两个 MVCC 无法规避的幻读场景。

![format_png 10][]

可以看到，有两个事务 session1 与 session2。session1 在 session2 插入数据前后的两次快照读，读到的结果都为空（即使 session2 提交事务的时机放在 session1 第二次快照读之前，结果也依然为空），符合 RR 隔离级别。但是在 session1 进行了`update t1 set a = 3`之后，再次快照读，发现多出了一条数据（破坏了 RR）。这条数据，就是幻行（session 1 本身没有新增数据，因此 RR 隔离级别下，session1 中相同的快照读语句，前后读到的结果本应该是一致的）。

相同的例子，还有如下：

![format_png 11][]

以上述这个例子说明这部分MVCC无法规避的幻读场景产生的原因：

1.  事务 1 先 select，事务 2 insert 并 commit
2.  事务 1 再次 select，根据 RR 隔离级别的原理，查询的结果与第一次 select 一样
3.  接着事务 1 不加条件地 update，这个 update 会作用在所有行上，包括事务 2 新加的（新加的行的事务 id 被改变为 1，符合可见性原则）
4.  事务 1 再次 select 就会出现事务 2 中的新行，出现幻读

快照读场景下，幻读发生的本质是由于当前事务更新了新插入数据的事务id，导致新插入数据的可见性发生变化，破坏了RR隔离级别。

不过也正如上述场景所述，在实际应用中，我们很少这样去写 SQL，更别说像例子二一样去写全表更新这样的 SQL，因此可以简单的认为，MVCC 已经解决了快照读场景下的幻读问题。

3.7.3 间隙锁-当前读下幻读的规避方案

由于当前读会给记录加锁，阻止了其他事务在当前读期间，并发修改记录的情况发生，因此当前读天然的不会出现脏读及不可重复读的问题，但是幻读仍然会发生。

例子如下：

sql

复制代码

``CREATE TABLE `author` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; INSERT into author VALUES (1,'g1',20),(5,'g5',20),(15,'g15',30),(20,'g20',30);``

<table> 
 <thead> 
  <tr> 
   <th>时间</th> 
   <th>session1</th> 
   <th>session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T1</td> 
   <td>select * from author where age = 20 for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td></td> 
   <td>INSERT into author VALUES (25,'g25',20);</td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td>select * from author where age = 20&nbsp;for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td>commit;</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

session1在 T1 时刻读取到的结果：`(1,'g1',20), (5,'g5',20)`，若没有间隙锁，session1在 T3 时刻会出现幻读，即读取到的结果：`(1,'g1',20), (5,'g5',20), (25,'g25',20)`，若引入了间隙锁，则 session2 在 T2 时刻的插入语句会被阻塞，直到 session1 commit 为止，这样就防止了幻读的发生。

那么间隙锁的实现原理是什么呢？看下面这个例子。

sql

复制代码

``-- 建表 CREATE TABLE `userinfo` ( `id` int(11) NOT NULL COMMENT '主键', `name` varchar(255) DEFAULT NULL COMMENT '姓名', `age` int(11) DEFAULT NULL COMMENT '年龄，普通索引列', `phone` varchar(255) DEFAULT NULL COMMENT '手机，唯一索引列', `remark` varchar(255) DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`), UNIQUE KEY `idx_userinfo_phone` (`phone`) USING BTREE COMMENT '手机号码，唯一索引', KEY `idx_user_info_age` (`age`) USING BTREE COMMENT '年龄，普通索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 初始化数据 INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (0, 'mayun', 20, '0000', '马云'); INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (5, 'liuqiangdong', 23, '5555', '刘强东'); INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (10, 'mahuateng', 18, '1010', '马化腾'); INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (15, 'liyanhong', 27, '1515', '李彦宏'); INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (20, 'wangxing', 23, '2020', '王兴'); INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (25, 'zhangyiming', 38, '2525', '张一鸣');``

**首先明确一点：行锁及间隙锁不是加在记录上的，而是加在索引上的！**

如上表，userinfo中目前有 3 种索引，分别是主键索引、唯一索引、普通索引。唯一索引这里用处不大，可以先忽略。

间隙锁，顾名思义，锁的是索引之间的间隙，因此对于此表数据，间隙锁的作用范围如下（以主键为例，另外两个索引同理）：

![format_png 12][]

具体间隙锁是如何规避幻读的，例子如下：

sql

复制代码

`select * from userinfo where age = 23 for update;`

如上述 SQL，由于普通索引并不唯一，因此可能有并发事务会新增一条 age 为 23 的数据，这时如果没有间隙锁，就会发生幻读。那么在此例中，为了防止出现幻读，只要给`(20,23), (23, 27)`这两个区间，即(20, 27)加上间隙锁即可。

![format_png 13][]

对上图再做一个详细的解释（上图涉及到 B+Tree 的结构，不清楚的可以先了解一下 B+Tree）：

1.  此时的间隙锁为(20, 27)，不包含左右两边的边界值 20 和 27
2.  在 age=23 的普通索引上有两把行锁，同时在 id=5 和 id=20 的主键索引上也有两把行锁
3.  只要当 age 的取值范围为 (20, 27) 时，任何并发事务都不能插入成功，因为间隙锁阻止了数据的插入，当然也阻止了修改与删除
4.  只要当 age 的值不属于 (20, 27)，且不等于 20 也不等于 27 时，任何并发事务都能插入成功，前提是待插入的数据行中的 id 值符合主键唯一性和 phone 值符合唯一索引唯一性
5.  当 age=20 的时候，只有对应的主键值小于 0 才能插入。举例说明：当待插入的数据为`age=20, id=1`时，还需要为 age 这个非唯一索引下面存上主键索引的值：1，此时的 1 比表中已经存在的主键值 0 大，所以它会放在 0 的后面，此时的`age=20, id=1`依然会占用被锁住的索引间隙，所以此时不能插入成功。能插入成功的数据为`age=20, id=-1`或`age=20, id=-2`这样的组合
6.  当 age=27 的时候，同理，只有对应的主键值大于 15 才能插入。因为主键值如果小于 15，如上图，若插入`age=27, id=14`，数据会被放在`age=27, id=15`的前面，这时依然会占用被锁住的索引间隙，所以不能插入成功
7.  上面红色箭头的区域都是有间隙锁的区域，在此区域内新增数据不能插入成功

由于间隙锁锁的是索引之间的位置，因此会发生上述第 5、6 点的情况，但个人觉得其实并没有必要， MySQL 应该是可以准确计算出间隙锁作用的区间是开区间还是闭区间。在本例中，当 age 等于 20 或 27 时，即使 id 取任意值插入，也不会出现幻读，但 MySQL 并没有这样实现。这个东西也不必深究，只需明白间隙锁的原理即可。如下是对 5、6 点情况的验证：

![format_png 14][]

### 4 MySQL事务间的写写冲突 & 解决方案 ###

#### 4.1 何为写写冲突 ####

不过多赘述，学习过并发编程的应该都明白如果没有互斥条件，并发写可能出现的问题就是数据丢失（数据覆盖）等，并发事务同理。

这里主要关注 MySQL 对并发事务之间的写冲突如何处理，首先来了解一下 MySQL 中的各种锁。

#### 4.2 共享锁 ####

可以被不同事务共享的锁就是共享锁（Shared Locks），简称 S 锁、读锁。共享锁与排它锁互斥，共享锁之间不互斥。

如下 SQL 会加共享锁：

sql

复制代码

`SELECT ... LOCK IN SHARE MODE;`

#### 4.3 排它锁 ####

排它锁，也称独占锁（Exclusive Locks），简称 X 锁、写锁。排它锁之间互斥，排它锁与共享锁互斥，因此一个时刻，一行数据，只能被一个事务加 X 锁。

如下 SQL 会加排它锁：

sql

复制代码

`SELECT ... FOR UPDATE; INSERT ... ; UPDATE ... ; DELETE ... ;`

#### 4.4 表锁 ####

MySQL 中粒度最大的一种锁，对整张表加锁，资源开销比行锁少，不会出现死锁，但发生锁冲突的概率很大。被大部分 MySQL 引擎支持，如 MyISAM 和 InnoDB。MyISAM 只支持表锁，InnoDB 默认是行锁，只有全表扫描的时候才会升级为表锁。因此我们主要围绕 MyISAM 来讲述一下表锁。

MySQL的表级锁有两种模式：

*  表级共享锁：不会阻塞其它线程对同一表的读请求，但会阻塞对同一表的写请求。只有当表级共享锁释放后，才会执行其它线程的写操作
 *  表级排他锁：会阻塞其它线程对同一表的读和写操作，只有当表级排他锁释放后，才会执行其它线程的读写操作。MyISAM 下写锁默认比读锁的优先级要高，即使读请求先到达锁请求队列，也会放在写请求的后面执行

因此 MyISAM 不适合做写多读少场景下的存储引擎。想要了解更多表锁在 MyISAM 中的技术细节，可以阅读此文章：[MySQL中的锁（表锁、行锁）][MySQL]。

#### 4.5 页锁 ####

MySQL 中数据在内存及磁盘上存储的基本单位并不是记录（行），而是页，可以简单理解为多个行会组成一个页，页锁即是给页上加的锁。

BDB 存储引擎中实现了页锁，而 InnoDB 以及 MyISAM 存储引擎都没有页锁，因此知道有这个概念就行，有兴趣的同学可以自行了解。

#### 4.6 行锁 ####

行锁大家都比较熟悉，这里不再赘述。

#### 4.7 意向锁 ####

意向锁是一种表锁，也分为意向共享锁（IS）和意向排它锁（IX）。由于 InnoDB 既支持表锁又支持行锁，因此意向锁的作用是用来辅助表锁和行锁的冲突判断。在取得行级共享锁和行级排它锁之前，会先取得对应的意向共享锁和意向排它锁（此步骤数据库系统自动完成，不需要人工干预）。

若没有意向锁，则判断表锁和行锁冲突的时候，需要对索引进行遍历，判断表上是否有行锁。有了意向锁，只要判断表是否有意向锁，就知道表上是否有行锁。表锁与意向锁的兼容性如下：

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>X</th> 
   <th>IX</th> 
   <th>S</th> 
   <th>IS</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>X</td> 
   <td>Conflict</td> 
   <td>Conflict</td> 
   <td>Conflict</td> 
   <td>Conflict</td> 
  </tr> 
  <tr> 
   <td>IX</td> 
   <td>Conflict</td> 
   <td>Compatible</td> 
   <td>Conflict</td> 
   <td>Compatible</td> 
  </tr> 
  <tr> 
   <td>S</td> 
   <td>Conflict</td> 
   <td>Conflict</td> 
   <td>Compatible</td> 
   <td>Compatible</td> 
  </tr> 
  <tr> 
   <td>IS</td> 
   <td>Conflict</td> 
   <td>Compatible</td> 
   <td>Compatible</td> 
   <td>Compatible</td> 
  </tr> 
 </tbody> 
</table>

**注：从意向锁的作用及加锁时机可知，意向锁不与行锁冲突，因此表格中的 S、X 代表的是表级共享锁与表级排它锁。**

#### 4.8 悲观锁 & 乐观锁 ####

悲观锁 & 乐观锁都是抽象概念，并不是真实存在的锁。大部分情况下，悲观锁及乐观锁的使用需要我们自己编写业务逻辑进行实现。

悲观锁 & 乐观锁产生的背景：由于在并发场景下，共享资源也不可能时时都处于竞争状态，基于此种条件，提出了悲观锁及乐观锁。

基于产生背景，悲观锁顾名思义，即并发场景下对共享资源的操作比较悲观，认为共享资源可能时刻处于竞争冲突中，因此操作数据的时候，直接加排他锁，保证本次操作的原子性、互斥性、可见性。像 Java 中 synchronized 的使用就是悲观锁的实现场景。

而乐观锁则相反，即并发场景下对共享资源的操作比较乐观，默认每次对共享资源的操作并没有引起竞争冲突，即每次操作天然线程安全，不用加锁，只有发现确实出现了竞争冲突，才会对共享资源真正的上锁。比较有代表性的实现方案就是 CAS 与版本号方案。

乐观锁的使用场景这里不再赘述，有兴趣的可以阅读此文章-[【BAT面试题系列】面试官：你了解乐观锁和悲观锁吗？][BAT]，写的比较好。

这里重点提一下面试官喜欢问的两个问题（上文已有详细的描述，这里总结一下）：

**1.CAS的ABA问题本质是什么？怎么解决？**

当业务场景需要关注共享资源被操作的中间过程，却没有进行记录，就容易引发 ABA 问题。解决方案就是引入版本号或时间戳，即记录共享资源被修改的次数或其他信息。

详情可阅读：[真实业务场景展现CAS原理的ABA问题及解决方案][CAS_ABA]。

**2.乐观锁有加锁吗？**

乐观锁本身不加锁，只是在更新时判断一下数据是否被其他线程更新了，AtomicInteger 便是一个例子。有时乐观锁可能会与加锁操作合作，例如 `update test set id = 1 where version = 1` 这条 SQL，使用版本号机制实现乐观锁，但 update 时 MySQL 默认还会加排它锁，但这只是乐观锁与加锁操作合作的例子，不能改变 “乐观锁本身不加锁” 这一事实。

#### 4.9 Next-key锁 ####

Next-key 锁也不是什么新鲜玩意，事实上，前面所讲的行锁与间隙锁的组合就并称为 Next-key 锁，之所以有这么个叫法，是因为 **MySQL 中加锁的基本粒度是 Next-key 锁**，即默认行锁 + 间隙锁的组合，不过不同情况下，Next-key 锁可能会退化成单独的行锁或间隙锁。

以 3.7.3 节的例子再为例，Next-key 锁的锁范围如下：

![format_png 15][]

**注：Next-key 锁是左开右闭的区间。**

#### 4.10 加解锁时机 ####

**MySQL中使用两阶段锁协议进行加解锁**，详情链接：[详解MySQL两阶段加锁协议][MySQL 1]。

这里做个总结：

1.  在事务中，在对记录进行更新或者(select for update、lock in share model)时，就会对记录加锁，但只有提交(commit)或者回滚(rollback)时才会解锁
2.  依据 2PL 的性能优化：SQL 编写时，越热点的记录离事务的终点越近，这样可以显著提高吞吐量

#### 4.11 加锁情况详细分析 ####

上面基本已经介绍完了 MySQL 中常见的所有锁，接下来，是本篇节最重要的部分，各种加锁情况分析（来将所学的知识融会贯通吧）~

不同的 MySQL 版本，相同的 SQL 加锁情况可能不尽相同，下面的结论只是根据前述所讲所得出的理论，并不代表实际加锁情况，况且不同的 MySQL 版本中 Next-key 的加锁范围还有一定的 bug，这里不关注这些旁枝末节。

如果想要查看 MySQL 实际的加锁情况，可使用 `select * from performance_schema.data_locks` 命令进行查看。此命令执行后，有几个重要关键字简单解析一下：

<table> 
 <thead> 
  <tr> 
   <th>LOCK_TYPE</th> 
   <th>LOCK_MODE</th> 
   <th>INDEX_NAME</th> 
   <th>LOCK_DATA</th> 
   <th>锁范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TABLE</td> 
   <td>IX</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>表级锁，意向排他锁</td> 
  </tr> 
  <tr> 
   <td>RECORD</td> 
   <td>X,REC_NOT_GAP</td> 
   <td>PRIMARY</td> 
   <td>15</td> 
   <td>主键值为15的数据加了行锁</td> 
  </tr> 
  <tr> 
   <td>RECORD</td> 
   <td>X,GAP</td> 
   <td>uniq_a</td> 
   <td>115, 15</td> 
   <td>唯一索引列，值为115之前的间隙，不包括115加了间隙锁</td> 
  </tr> 
  <tr> 
   <td>RECORD</td> 
   <td>X</td> 
   <td>uniq_a</td> 
   <td>115, 15</td> 
   <td>唯一索引列，值为115之前的间隙加了 Next-key 锁，前开后闭区间，包括115</td> 
  </tr> 
 </tbody> 
</table>

**0.环境准备**

MySQL 不同的版本加锁策略可能不同，下面以 `MySQL 8.0.25` 版本为例，进行多角度验证 next-key lock 的加锁范围。

MySQL 版本：8.0.25、隔离级别：可重复读（RR）、存储引擎：InnoDB。

sql

复制代码

``CREATE TABLE `t` ( `id` int NOT NULL COMMENT '主键', `a` int DEFAULT NULL COMMENT '唯一索引', `c` int DEFAULT NULL COMMENT '普通索引', `d` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_a` (`a`), KEY `idx_c` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 初始化数据 INSERT INTO `t`(`id`, `a`, `c`, `d`) VALUES (0, 10, 20, 30); INSERT INTO `t`(`id`, `a`, `c`, `d`) VALUES (5, 15, 25, 35); INSERT INTO `t`(`id`, `a`, `c`, `d`) VALUES (10, 110, 210, 310); INSERT INTO `t`(`id`, `a`, `c`, `d`) VALUES (15, 115, 215, 315); INSERT INTO `t`(`id`, `a`, `c`, `d`) VALUES (20, 120, 220, 320);``

**1.主键索引等值查询-值存在**

sql

复制代码

`mysql> begin; select * from t where id = 10 for update;`

直接说结论，根据前文所述，首先会给表加上意向排它锁，虽然加锁的基本单位是 Next-key，但这里主键存在，只要锁住 id=10 的主键索引就行，因此 Next-key 会退化为行锁。

**2.主键索引等值查询-值不存在**

sql

复制代码

`mysql> select * from t where id = 11 for update;`

主键索引结构如下：

![format_png 16][]

根据前文所述，首先会加表级意向排它锁，然后为防止幻读，Next-key会退化为间隙锁，间隙锁范围是(10, 15)。

**3.主键索引范围查询**

sql

复制代码

`mysql> begin; select * from t where id > 10 and id <= 15 for update;`

根据索引结构以及前文所述，首先会给表加意向排它锁，然后会给(10, 15\]区间加 Next-key 锁，不会退化。

**4.唯一索引等值查询-值存在**

sql

复制代码

`begin; select * from t where a = 110 for update;`

唯一索引等值查询也不需要间隙锁，原因与主键索引等值查询一样。不同的是索引上的加锁方式，由于涉及到回表，因此 X 锁不仅会加到唯一索引上，同时也会加到主键索引上。

这里牵扯到两个问题：

1.  X锁只给唯一索引上加行不行
    
     *  不行，如果此时有一个并发SQL：`delete from t where id = 10;`，而`select for update`语句没有将主键索引上的记录加锁，那么并发的 delete 就会感知不到前面 select 语句的存在，违背了同一记录上排他锁之间互斥的原则
2.  X锁只给主键索引上加行不行
    
     *  可以，由于唯一索引与主键索引是一一映射的关系，并且写操作最终会回归到主键索引上，因此是可以只给主键索引加锁的，但 MySQL 并没有这样做，我想原因大概是为了提高性能，毕竟唯一索引上只要加了锁，就有可能避免一次无效的回表操作

综上，不管是`select for share`语句还是不涉及回表的`select id from t where a = 110 for update;`语句，最终都会给主键索引加上相应的锁。

**5.唯一索引等值查询-值不存在**

sql

复制代码

`begin; select * from t where a = 111 for update;`

![format_png 17][]

首先会给表加意向排他锁，然后会给唯一索引的 (110,115) 加上间隙锁（Next-key锁退化），由于已经解决了幻读问题，因此主键索引上不会加锁。

**6.唯一索引范围查询**

sql

复制代码

`mysql> begin; select * from t where a >= 110 and a < 115 for update;`

首先会给表加意向排他锁，然后 Next-key 锁会分别退化为唯一索引 110 的行锁，(110, 115)的间隙锁，由于 110 有对应的主键，因此也需要对相应的主键索引加上行锁。

**6.普通索引等值查询-值存在**

sql

复制代码

`begin; select * from t where c = 210 for update;`

首先加意向排它锁，由于是普通索引，值可能重复，因此 Next-key 锁不会退化，同时给 210 对应的主键索引也加上行锁。

**7.普通索引等值查询-值不存在**

sql

复制代码

`begin; select * from t where c = 211 for update;`

首先加意向排它锁，然后给 215 的前置区间加 Next-key 锁，由于 (210, 215) 的锁区间已经能够防止幻读产生，因此 Next-key 锁退化成间隙锁。

**8.普通索引范围查询**

sql

复制代码

`mysql> begin; select * from t where c >= 210 and a < 215 for update;`

首先加意向排它锁，由于是普通索引，索引值可能重复，因此第一个 Next-key 锁并不会退化，锁区间为(25,210\]，并给 210 对应的主键索引加上行锁，第二个 Next-key 锁会退化为间隙锁，锁范围为(210, 215)。

**9.无索引查询**

当锁定读查询语句`select for share`，`select for update`没有使用索引列作为查询条件，或者查询语句没有走索引查询，将导致全表扫描，同时锁全表（全表的每一个索引都会加 Next-key 锁，相当于把整个表锁住了，并不是加了表锁），这时如果其他事务对该表进行写操作，都会被阻塞。

根据两阶段锁协议，以及 MySQL 加锁的基本单位是 Next-key 锁，最终全表的加锁情况将如下所示：

![format_png 18][]

因此，在线上执行 update、delete、select for update 等具有加锁性质的语句时，一定要检查语句是否走了索引，如果发生了全表扫描，那将会引发很严重的问题！

[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/2aae61c484d94950af16dd7042e92002.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/7dda1850984b4495936903f4944c7e13.png
[format_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/8c3dc4e926ab4603b0c87847acb0b39b.png
[format_png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/0956a03cd1e34968b6b46405977eaf87.png
[format_png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e0d4476196a14c2f8e521f62878ca2b6.png
[format_png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c5cd051aef93435db86a947b9efe9108.png
[format_png 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/7fed924ad2864a40a3b310a1f59f4d3e.png
[format_png 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e669c19072f54385a1e5748e3481e6b8.png
[format_png 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e1399e7f15104bc987e19bc38d13ec59.png
[format_png 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/afe2b63bd57f4111a8ebb27b74e596b9.png
[format_png 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/2471d0ccea1b49c3a9e2f33ef69f8615.png
[format_png 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/0b63a4a27bf94202b4c67aad9a5e6dca.png
[format_png 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/70233c25a8d04075b8a925a3c95307fd.png
[format_png 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/7790448bdece4ea29e2aa567d8ae986c.png
[format_png 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/5e5aa15f7ba742c593d8a86dd6ecb066.png
[MySQL]: https://link.juejin.cn?target=https%3A%2F%2Fwww.cnblogs.com%2Fchenqionghe%2Fp%2F4845693.html
[BAT]: https://link.juejin.cn?target=https%3A%2F%2Fwww.cnblogs.com%2Fkismetv%2Fp%2F10787228.html
[CAS_ABA]: https://link.juejin.cn?target=https%3A%2F%2Fblog.csdn.net%2Fcauchy6317%2Farticle%2Fdetails%2F120475452
[format_png 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/531ab6b3ace74e1985f12ce6ee28429c.png
[MySQL 1]: https://link.juejin.cn?target=https%3A%2F%2Fsegmentfault.com%2Fa%2F1190000038163191
[format_png 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/bbce1cdd6e66496abdba5e6d9df02290.png
[format_png 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/489321b89d744ae3a9acbfe75f48b84c.png
[format_png 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/250a8d0639ed4d4b9b505ac4c49f875b.png