MySQL-20：幻读如何解决？

浅浅的花香味﹌ 2022-12-27 07:50 158阅读 0赞

先建个表，看下面sql：

CREATE TABLE `r` (
      `id` int(11) NOT NULL,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `c` (`c`)
    ) ENGINE=InnoDB;
    
    insert into r values(0,0,0),(5,5,5),
    (10,10,10),(15,15,15),(20,20,20),(25,25,25);

问题：在InnoDB引擎默认的隔离级别(可重复读下)，下面事务的锁什么时候加，什么时候释放，加多少行的锁？

begin;
    select * from t where d=5 for update;
    commit;

#### 20.1 幻读是什么？ ####

**幻读：** 事务A在不同的时间对同一数据集查询的结果不一致。

**说明：** ①在可重复读隔离级别下，普通的查询是快照读，幻读只出现在当前读下。②幻读偏向于新插入的数据。

**当前读：** 能读到所有已经提交的记录的最新值。

看下面示意图，我们能明显看到Session A出现幻读。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

#### 20.2 幻读的问题 ####

**下面我们假设仅在d=5行加锁：**

看下面示意图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

**问题一：** `select * from t where d = 5 for update`的语义为锁住所有d=5的行，不允许所有事务进行读写操作。T2，T4时刻就破坏了该语义。T1在一开始只在id=5这一行加锁，并没有在id=0,1行加锁。

**问题二：** 数据一致性问题，数据库和binlog恢复出来的数据不一致。

# 上面示意图写入binlog的内容
    
    update t set d=5 where id=0; /*(0,0,5)*/
    update t set c=5 where id=0; /*(0,5,5)*/
    
    insert into t values(1,1,5); /*(1,1,5)*/
    update t set c=5 where id=1; /*(1,5,5)*/
    
    update t set d=100 where d=5;/*所有d=5的行，d改成100*/

数据库： (0,5,5)，(1,5,5)，(5,5,100)；binlog恢复：(0,5,100)，(1,5,5)，(5,5,100)

**现在我们假设把所有行都加上锁：**

**binlog内容如下：**

insert into t values(1,1,5); /*(1,1,5)*/
    update t set c=5 where id=1; /*(1,5,5)*/
    
    update t set d=100 where d=5;/*所有d=5的行，d改成100*/
    
    update t set d=5 where id=0; /*(0,0,5)*/
    update t set c=5 where id=0; /*(0,5,5)*/

我们得到数据库：(0,5,5)，(1,5,5)，(5,5,100)；binlog恢复：(0,5,5)，(1,5,100)，(5,5,100)

在所有行上加写锁无法控制到新数据 insert ，因为它本来就不存在，下面看InnoDB如何解决它。

#### 20.3 如何解决幻读 ####

**产生幻读：** 无法控制新数据的插入，也就是记录之间的“间隙”。所以InnoDB引入了新的锁→间隙锁(Gap Lock)。

间隙锁，锁的就是两条数据之间的间隙，例如开头的表格，插入6个数据，产生7个间隙。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]

也就是说当你去 执行`select * from t where d=5 for update`的时候，不仅会给所有行加写锁，还同时加上7个间隙锁。间隙锁之间不存在冲突关系，它们的共同目的都是保护这个间隙，不插入新值。

间隙锁和行锁合称**next-key lock**，每个next-key lock是前开后闭区间。

但间隙锁和next-key lock的引入帮我们解决幻读问题，也带来了其他问题。

这里说明个情况：`select * from t where d=9 for update`，但没有数据的时候，就只会加间隙锁，不会加行锁，因为没有数据行。

模拟下图情景会导致死锁： ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]

间隙锁是在可重复读隔离级别下才会生效的 。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/6791191b8791467ebd3390b5544c2892.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/5244b19fa3de4fb3ac725de362c70db0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/2020120823522462.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201208235238605.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center