灵魂发问：MySQL是如何解决幻读的？

川长思鸟来 2023-10-01 23:07 23阅读 0赞

### 概念 ###

MySQL InnoDB支持三种行锁定方式：

*  **行锁（Record Lock）**：锁直接加在索引记录上面。
 *  **间隙锁（Gap Lock）**：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
 *  **Next-Key Lock**：行锁与间隙锁组合起来用就叫做`Next-Key Lock`。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

select * from  user where user_id > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的`user_id`值为101的记录加锁，也会对`user_id`大于101（这些记录并不存在）的“间隙”加锁。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了`user_id`大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要

#### 快照读和当前读 ####

快照读历史数据－mvcc

innodb的默认事务隔离级别是rr（可重复读）。它的实现技术是mvcc（MVCC只在读提交可重复读两种隔离级别下工作）。基于版本的控制协议。该技术不仅可以保证innodb的可重复读，而且可以防止幻读。但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。

当前读最新数据－next-key lock

如何做到保证数据是一致的（也就是一个事务，其内部读取对应某一个数据的时候，数据都是一样的），同时读取的数据是最新的数据。innodb提供了`next－key lock`，也就是结合gap锁与行锁，达到最终目的。

实现：

**1.快照读(snapshot read)**

简单的select操作(不包括 `select ... lock in share mode, select ... for update`)

2.当前读(current read)

select ... lock in share mode、select ... for update
    
    insert、update、delete

> 在RR级别下，快照读是通过MVCC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。

测试

建表innodb\_lock：

DROP TABLE IF EXISTS `innodb_lock`;
    CREATE TABLE `innodb_lock` (
      `a` int(10) NOT NULL,
      `b` varchar(255) NOT NULL DEFAULT '',
      KEY `index_a` (`a`),
      KEY `index_b` (`b`)
    ) ENGINE=InnoDB;

插入数据，注意这里边没有a为2的数据：

INSERT INTO `innodb_lock` VALUES ('1', 'b2');
    INSERT INTO `innodb_lock` VALUES ('3', '3');
    INSERT INTO `innodb_lock` VALUES ('4', '4000');
    INSERT INTO `innodb_lock` VALUES ('5', '5000');
    INSERT INTO `innodb_lock` VALUES ('6', '6000');
    INSERT INTO `innodb_lock` VALUES ('7', '7000');
    INSERT INTO `innodb_lock` VALUES ('8', '8000');
    INSERT INTO `innodb_lock` VALUES ('9', '9000');

（1）开启两个客户端，修改事务隔离级别为可重复读

![b8416fb8e48e3006252dfaaf4f94af37.png][]

（2）开启事务，在左侧客户端批量修改a为1~6范围内的数据。在右侧客户端插入a为2的数据。右侧操作被阻塞。说明有间隙锁。

![0857ee57b240ebcdad35b103db931ec6.png][]

（3）重复（2），事务隔离级别依然是`repeatable read`，只不过变成在右侧客户端插入a为10的数据，成功。

![d283ee7d0867cbb11f4dd161ca89738f.png][]

（4）事务隔离级别设置为`read committed`，重复步骤（2），发现右侧客户端的操作成功，说明该隔离级别无间隙锁。

![3e3c5cc5671d2d8011736712e5d03a91.png][]

（5）还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！左侧客户端给不存在的记录加锁，右侧客户端的增加操作阻塞。

![de75b01c78ad8d0ae1732ef748fbb1c5.png][]

但是，如果a是唯一索引，不会升级全表锁。

先添加唯一索引：

![52210d3664d3047fd15d26fac8f38514.png][]

（6）重复步骤（5），发现右侧客户端不会被阻塞，数据插入成功

![15119f712477f338c3e5201179dc6cd8.png][]

### 小结 ###

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用`Next-Key Lock`的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。

要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数`innodb_locks_unsafe_for_binlog`。

### 补充 ###

`MySQL InnoDB`的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是`next-key locks`。

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

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

![db8edf32ea01458291356cb17cb7ba2c.jpeg][]

[b8416fb8e48e3006252dfaaf4f94af37.png]: https://img-blog.csdnimg.cn/img_convert/b8416fb8e48e3006252dfaaf4f94af37.png
[0857ee57b240ebcdad35b103db931ec6.png]: https://img-blog.csdnimg.cn/img_convert/0857ee57b240ebcdad35b103db931ec6.png
[d283ee7d0867cbb11f4dd161ca89738f.png]: https://img-blog.csdnimg.cn/img_convert/d283ee7d0867cbb11f4dd161ca89738f.png
[3e3c5cc5671d2d8011736712e5d03a91.png]: https://img-blog.csdnimg.cn/img_convert/3e3c5cc5671d2d8011736712e5d03a91.png
[de75b01c78ad8d0ae1732ef748fbb1c5.png]: https://img-blog.csdnimg.cn/img_convert/de75b01c78ad8d0ae1732ef748fbb1c5.png
[52210d3664d3047fd15d26fac8f38514.png]: https://img-blog.csdnimg.cn/img_convert/52210d3664d3047fd15d26fac8f38514.png
[15119f712477f338c3e5201179dc6cd8.png]: https://img-blog.csdnimg.cn/img_convert/15119f712477f338c3e5201179dc6cd8.png
[db8edf32ea01458291356cb17cb7ba2c.jpeg]: https://img-blog.csdnimg.cn/db8edf32ea01458291356cb17cb7ba2c.jpeg