MySQL中RR模式下死锁一例

小咪咪 2023-02-18 13:00 4阅读 0赞

> 作者：高鹏（八怪），《深入理解MySQL主从原理32讲》系列的作者
> 
> 系列链接：https://www.jianshu.com/nb/43148932
> 
>   
> 原文出处：https://www.jianshu.com/p/3e57a428d2a2

一、案例模拟二、死锁分析三、关于锁模式的变化四、关于LOCK\_ORDINARY\[next\_key\_lock\]来历最后

环境：版本5.7.29，RR隔离级别

### 一、案例模拟 ###

CREATE TABLE `t8` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `d_id` varchar(40) NOT NULL DEFAULT '',
      `b_id` varchar(40) NOT NULL DEFAULT '',
      `is_dropped` tinyint(1) NOT NULL DEFAULT '0',
      `u_c` varchar(10) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      UNIQUE KEY `DealerAndBrokerAndDropped` (`d_id`,`b_id`,`is_dropped`)
    ) ENGINE=InnoDB ;
    
    insert into t8 values(1,1,1,0,'a');
    insert into t8 values(2,2,2,0,'a');
    insert into t8 values(3,3,3,0,'a');
    insert into t8 values(4,4,4,0,'a');
    insert into t8 values(5,5,5,0,'a');
    insert into t8 values(6,6,6,0,'a');
    insert into t8 values(7,7,7,0,'a');
    insert into t8 values(8,8,8,0,'a');
    insert into t8 values(9,9,9,0,'a');
    insert into t8 values(10,10,10,0,'a');
    insert into t8 values(11,11,11,0,'a');

执行语句如下：

<table> 
 <thead> 
  <tr> 
   <th>S1</th> 
   <th>S2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>select u_c from t8 where d_id='1' and b_id='1' and is_dropped=0 for update;</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td><br></td> 
   <td>select u_c from t8 where d_id='1' and b_id='1' and is_dropped=0 for update; 处于堵塞状态</td> 
  </tr> 
  <tr> 
   <td>update t8 set u_c='b' where d_id='1' and b_id='1'; —此时触发死锁 S2回滚</td> 
   <td><br></td> 
  </tr> 
 </tbody> 
</table>

发生死锁记录如下： ![format_png][]

### 二、死锁分析 ###

仔细分析我们会发现trx id 5679最后被堵塞需要获取的锁为（lock\_mode X waiting），堵塞发生在索引DealerAndBrokerAndDropped 上，也就是这是一个next key lock 且需要获取的模式为LOCK\_X，处于等待状态。

而我们来看trx id 5679前面获取的锁是什么呢？显然可以看到为（lock\_mode X locks rec but not gap），获取发生在索引DealerAndBrokerAndDropped 上，也就是这是一个key lock且获取模式为LOCK\_X。

但是我们需要知道DealerAndBrokerAndDropped明明是一个唯一索引，获取key lock我们很容易理解，但是为什么也会出现获取next key lock呢？这个问题我们先放一下，先来分析一下整个死锁的产生的过程

S1（select操作）  
通过唯一性索引定位索引数据获取了唯一索引DealerAndBrokerAndDropped 上的  
LOCK\_REC\_NOT\_GAP|LOCK\_X，获取成功记录就是 d\_id='1' b\_id='1' is\_dropped=0这条数据。

S1（select操作）  
回表获取全部数据，这个时候需要主键上的相应的行锁。LOCK\_REC\_NOT\_GAP|LOCK\_X获取成功

S2（select操作）  
通过唯一性索引定位索引数据试图获取了唯一索引DealerAndBrokerAndDropped 上的  
LOCK\_REC\_NOT\_GAP|LOCK\_X，获取失败记录就是 d\_id='1' b\_id='1' is\_dropped=0这条数据，处于等待状态。

S1（update操作）  
通过索引DealerAndBrokerAndDropped 查找数据（注意这里已经不是唯一性定位操作了，下面会做分析），这个时候首先需要通过查询条件获取出需要更新的第一条数据，实际上这个时候也是d\_id='1' b\_id='1' is\_dropped=0这条数据，需要获取的锁为LOCK\_ORDINARY\[next\_key\_lock\]|LOCK\_X，这个时候我发现虽然S1之前获取了这条数据的锁，但是锁模式变化了（一致不会重新获取，下面会分析这种行为），因此这里需要重新获取，但是这显然是不行的，因为S2都还处于等待中，因此这里也发生了等待。

因此通过这个过程就出现死锁，S2等S1 S1等S2。

### 三、关于锁模式的变化 ###

关于这里我们参考函数lock\_rec\_lock\_fast，这里会不进行行锁冲突验证而进行快速加锁，如果锁模式没有变化则也会再这里进行快速加锁（也就是直接跳过），当然如果块中一个row lock 都没有也会在这里进行加锁，这是每个加行锁的操作都必须经历的判断，如果不能快速加锁则进入slow加锁方式，这里看一下下面的这段代码：

if (lock_rec_get_next_on_page(lock)
                 || lock->trx != trx
                 || lock->type_mode != (mode | LOCK_REC)
                 || lock_rec_get_n_bits(lock) <= heap_no) { 
    
                status = LOCK_REC_FAIL;
            }

这里的lock->trx != trx会判断本次加锁事务和上次加锁事务是否是同一个事务，lock->type\_mode != (mode | LOCK\_REC)会判断锁模式是否相同。如果不能满足条件则判定为LOCK\_REC\_FAIL，进入slow加锁方式。

而我们这里S1加锁第一次是LOCK\_REC\_NOT\_GAP|LOCK\_X，而第二次是LOCK\_ORDINARY\[next\_key\_lock\]|LOCK\_X，显然变化了，因此进入slow加锁阶段，进行冲突验证，结果嘛也就冲突了。这是本死锁的一个原因。

### 四、关于LOCK\_ORDINARY\[next\_key\_lock\]来历 ###

这是本死锁的一个最重要原因，知道了这个原因这个案例就理解了。首先我们先看这个update语句：

update t8 set u_c='b' where d_id='1' and b_id='1';

我们发现这个时候唯一索引还少一个条件也就是is\_dropped字段，这个时候本次定位查询不会判定为唯一性查询，而是普通的二级索引定位方式，这个时候RR模式出现LOCK\_ORDINARY\[next\_key\_lock\]就显得很自然了，下面是这个判断过程，代码位于row\_search\_mvcc中。

(match_mode == ROW_SEL_EXACT
            && dict_index_is_unique(index)
            && dtuple_get_n_fields(search_tuple)
            == dict_index_get_n_unique(index)
            && (dict_index_is_clust(index)
            || !dtuple_contains_null(search_tuple)))

稍微解释一下，唯一性查找条件至少包含如下3点：

*  1. 索引具有唯一性
 *  2. 查询的字段数量和索引唯一性字段数量相同
 *  3. 是主键或者查询条件中不包含NULL值

注意第3点源码说明如下：

/* Note above that a UNIQUE secondary index can contain many
            rows with the same key value if one of the columns is the SQL
            null. A clustered index under MySQL can never contain null
            columns because we demand that all the columns in primary key
            are non-null. */

满足上面4点条件才能确认为唯一查找，本查询由于第3条不满足因此，因此判定失败。  
不仅如此如果本条数据加锁成功，那么你会看到如下的结果：

---TRANSACTION 25830, ACTIVE 2 sec
    4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
    MySQL thread id 5, OS thread handle 140737101231872, query id 4115 localhost root starting
    show engine innodb status
    TABLE LOCK table `test`.`t8` trx id 25830 lock mode IX
    RECORD LOCKS space id 1050 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `test`.`t8` trx id 25830 lock_mode X
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 1; hex 31; asc 1;;
     1: len 1; hex 31; asc 1;;
     2: len 1; hex 80; asc  ;;
     3: len 8; hex 8000000000000001; asc         ;;
    
    RECORD LOCKS space id 1050 page no 3 n bits 80 index PRIMARY of table `test`.`t8` trx id 25830 lock_mode X locks rec but not gap
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
     0: len 8; hex 8000000000000001; asc         ;;
     1: len 6; hex 0000000064e6; asc     d ;;
     2: len 7; hex 5f000000430110; asc _   C  ;;
     3: len 1; hex 31; asc 1;;
     4: len 1; hex 31; asc 1;;
     5: len 1; hex 80; asc  ;;
     6: len 1; hex 62; asc b;;
    
    RECORD LOCKS space id 1050 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `test`.`t8` trx id 25830 lock_mode X locks gap before rec
    Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 2; hex 3130; asc 10;;
     1: len 2; hex 3130; asc 10;;
     2: len 1; hex 80; asc  ;;
     3: len 8; hex 800000000000000a; asc         ;;

我们发现DealerAndBrokerAndDropped唯一索引的吓一跳记录也加了gap lock，这完全是RR模式非唯一索引的加锁行为。

### 最后 ###

如果我们将语句

update t8 set u_c='b' where d_id='1' and b_id='1';

修改为

update t8 set u_c='b' where d_id='1' and b_id='1' and is_dropped=0;

那么死锁将不会触发了。原因就是第三部分我们说的，这里锁模式完全一致，不会导致加锁操作了。

enjoy MySQL :)

全文完。

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

*  由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
    
    ![format_png 1][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码直接享受组团价
    
    ![format_png 2][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUelJXMHNRaDZLT1A5S0s2aGJTUUNpYVczUzE0UUJjVlFseVlxSnJOSXIzSW1KZ3ZvU3hQTEhUOXZWY3hlYlNXQW1vdmlhWkg4cUZvSU9BLzY0MA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeEx5ajlRdWJEY0hQeUlwemgwaWNXSVpnTm96aFo3TmxDbW53MkU1ekpqUzZTTjE0TENaR0JvUW04WGliVHkzZDFtaWFqYWlhQVhTcTFTVVEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUek5xdmliY3IwZmJDQkU3MXBoMzF5aWJ5TzNFOVJONnlLd0NYd1R6cVZmc1ZxM2RxUUFpYWRoUDlGSkVXOUluNlk0Mnl4ZTVUUWVuMm9BUS82NDA?x-oss-process=image/format,png