MySQL中RR模式下死锁一例

作者：高鹏（八怪），《深入理解MySQL主从原理32讲》系列的作者
系列链接：https://www.jianshu.com/nb/43148932
原文出处：https://www.jianshu.com/p/3e57a428d2a2
一、案例模拟二、死锁分析三、关于锁模式的变化四、关于LOCK_ORDINARY[next_key_lock]来历最后
环境：版本5.7.29，RR隔离级别
一、案例模拟CREATETABLE`t8`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`d_id`varchar(40)NOTNULLDEFAULT”,`b_id`varchar(40)NOTNULLDEFAULT”,`is_dropped`tinyint(1)NOTNULLDEFAULT’0′,`u_c`varchar(10)NOTNULLDEFAULT”,PRIMARYKEY(`id`),UNIQUEKEY`DealerAndBrokerAndDropped`(`d_id`,`b_id`,`is_dropped`))ENGINE=InnoDB;insertintot8values(1,1,1,0,’a’);insertintot8values(2,2,2,0,’a’);insertintot8values(3,3,3,0,’a’);insertintot8values(4,4,4,0,’a’);insertintot8values(5,5,5,0,’a’);insertintot8values(6,6,6,0,’a’);insertintot8values(7,7,7,0,’a’);insertintot8values(8,8,8,0,’a’);insertintot8values(9,9,9,0,’a’);insertintot8values(10,10,10,0,’a’);insertintot8values(11,11,11,0,’a’);执行语句如下：
S1S2beginselect u_c from t8 where d_id=’1′ and b_id=’1′ and is_dropped=0 for update;select u_c from t8 where d_id=’1′ and b_id=’1′ and is_dropped=0 for update; 处于堵塞状态update t8 set u_c=’b’ where d_id=’1′ and b_id=’1′; —此时触发死锁 S2回滚发生死锁记录如下：
二、死锁分析仔细分析我们会发现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语句：updatet8setu_c=’b’whered_id=’1’andb_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点源码说明如下：
/*NoteabovethataUNIQUEsecondaryindexcancontainmanyrowswiththesamekeyvalueifoneofthecolumnsistheSQLnull.AclusteredindexunderMySQLcannevercontainnullcolumnsbecausewedemandthatallthecolumnsinprimarykeyarenon-null.*/满足上面4点条件才能确认为唯一查找，本查询由于第3条不满足因此，因此判定失败。不仅如此如果本条数据加锁成功，那么你会看到如下的结果：
—TRANSACTION25830,ACTIVE2sec4lockstruct(s),heapsize1160,3rowlock(s),undologentries1MySQLthreadid5,OSthreadhandle140737101231872,queryid4115localhostrootstartingshowengineinnodbstatusTABLELOCKtable`test`.`t8`trxid25830lockmodeIXRECORDLOCKSspaceid1050pageno4nbits80indexDealerAndBrokerAndDroppedoftable`test`.`t8`trxid25830lock_modeXRecordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits00:len1;hex31;asc1;;1:len1;hex31;asc1;;2:len1;hex80;asc;;3:len8;hex8000000000000001;asc;;RECORDLOCKSspaceid1050pageno3nbits80indexPRIMARYoftable`test`.`t8`trxid25830lock_modeXlocksrecbutnotgapRecordlock,heapno2PHYSICALRECORD:n_fields7;compactformat;infobits00:len8;hex8000000000000001;asc;;1:len6;hex0000000064e6;ascd;;2:len7;hex5f000000430110;asc_C;;3:len1;hex31;asc1;;4:len1;hex31;asc1;;5:len1;hex80;asc;;6:len1;hex62;ascb;;RECORDLOCKSspaceid1050pageno4nbits80indexDealerAndBrokerAndDroppedoftable`test`.`t8`trxid25830lock_modeXlocksgapbeforerecRecordlock,heapno11PHYSICALRECORD:n_fields4;compactformat;infobits00:len2;hex3130;asc10;;1:len2;hex3130;asc10;;2:len1;hex80;asc;;3:len8;hex800000000000000a;asc;;我们发现DealerAndBrokerAndDropped唯一索引的下一条记录也加了gap lock，这完全是RR模式非唯一索引的加锁行为。
最后如果我们将语句
updatet8setu_c=’b’whered_id=’1’andb_id=’1′;修改为
updatet8setu_c=’b’whered_id=’1’andb_id=’1’andis_dropped=0;那么死锁将不会触发了。原因就是第三部分我们说的，这里锁模式完全一致，不会导致加锁操作了。
enjoy MySQL 🙂
全文完。
老叶茶馆推荐搜索
MySQL 8
InnoDB
索引
负载高
写入慢
排查思路
由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
另外，叶老师在腾讯课堂《MySQL性能优化》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：合理利用索引，降低锁影响，提高事务并发度。
下面是自动拼团的二维码直接享受组团价