MySQL的RR模式下死锁
本篇内容主要讲解“MySQL的RR模式下死锁”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL的RR模式下死锁”吧!
一、问题提出如下构造方式，问为什么RC模式下不会死锁，RR模式下死锁。
droptablett;CREATETABLE`tt`(`id`int(11)NOTNULL,`c1`int(11)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`c1`(`c1`))ENGINE=InnoDBDEFAULTCHARSET=utf8;insertintottvalues(1,1);session 1 session 2 begin;
select * from tt where c1=1 for update;
update tt set id=2 where c1=1;
begin;select * from tt where c1=1 for update;堵塞select * from tt where c1=1 for update;
死锁回滚二、分析方式首先分析session 1 第一句：
select * from tt where c1=1 for update;
执行后的加锁行为
RR
---TRANSACTION231106,ACTIVE9sec3lockstruct(s),heapsize1160,2rowlock(s)MySQLthreadid11,OSthreadhandle140737153623808,queryid303localhostrootTABLELOCKtable`test`.`tt`trxid231106lockmodeIXRECORDLOCKSspaceid127pageno4nbits72indexc1oftable`test`.`tt`trxid231106lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000001;asc;;1:len4;hex80000001;asc;;RECORDLOCKSspaceid127pageno3nbits72indexPRIMARYoftable`test`.`tt`trxid231106lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex0000000386c0;asc;;2:len7;hexaa0000003f0110;asc?;;3:len4;hex80000001;asc;;
RC
---TRANSACTION231105,ACTIVE7sec3lockstruct(s),heapsize1160,2rowlock(s)MySQLthreadid11,OSthreadhandle140737153623808,queryid295localhostrootTABLELOCKtable`test`.`tt`trxid231105lockmodeIXRECORDLOCKSspaceid127pageno4nbits72indexc1oftable`test`.`tt`trxid231105lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000001;asc;;1:len4;hex80000001;asc;;RECORDLOCKSspaceid127pageno3nbits72indexPRIMARYoftable`test`.`tt`trxid231105lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex0000000386c0;asc;;2:len7;hexaa0000003f0110;asc?;;3:len4;hex80000001;asc;;
我们可以看到因为 c1是主键因此加锁方式不管怎么样都是LOCK_X|LOCK_REC_NOT_GAP，主键上也是同样的。就是锁住了二级唯一索引和主键的相关记录。
然后分析session 1 第二句：update tt set id=2 where c1=1;
执行后的加锁行为
这一句比较重要，在二级唯一索引c1上会做一个删除和插入操作，也就是会将原来的1,1记录标记为del flag，同时插入2,1这条记录，这会引起一个锁的继承操作(lock_rec_inherit_to_gap_if_gap_lock调用会出现GAP LOCK)，但是之前还会涉及到唯一性检查因此还涉及到LOCK_S锁和next key lock的存在(对于二级索引做唯一性检查始终是next key lock)。这里的del flag也是形成死锁的重要原因。(对于二级索引的update操作总是先删除然后插入记录，主键则会进行判断是否可以容下新的记录)
RR
---TRANSACTION231106,ACTIVE1626sec5lockstruct(s),heapsize1160,5rowlock(s),undologentries2MySQLthreadid11,OSthreadhandle140737153623808,queryid305localhostrootTABLELOCKtable`test`.`tt`trxid231106lockmodeIXRECORDLOCKSspaceid127pageno4nbits72indexc1oftable`test`.`tt`trxid231106lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;RECORDLOCKSspaceid127pageno3nbits72indexPRIMARYoftable`test`.`tt`trxid231106lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits320:len4;hex80000001;asc;;1:len6;hex0000000386c2;asc;;2:len7;hex2c000000410dca;asc,A;;3:len4;hex80000001;asc;;RECORDLOCKSspaceid127pageno4nbits72indexc1oftable`test`.`tt`trxid231106lockmodeS(LOCK_S)locksgapandrec(LOCK_ORDINARY[next_key_lock])Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits00:len8;hex73757072656d756d;ascsupremum;;Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;RECORDLOCKSspaceid127pageno4nbits72indexc1oftable`test`.`tt`trxid231106lockmodeS(LOCK_S)locksgapbeforerec(LOCK_GAP)Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000001;asc;;1:len4;hex80000002;asc;;
RC
5lockstruct(s),heapsize1160,5rowlock(s),undologentries2MySQLthreadid11,OSthreadhandle140737153623808,queryid316localhostrootTABLELOCKtable`test`.`tt`trxid231123lockmodeIXRECORDLOCKSspaceid128pageno4nbits72indexc1oftable`test`.`tt`trxid231123lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;RECORDLOCKSspaceid128pageno3nbits72indexPRIMARYoftable`test`.`tt`trxid231123lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits320:len4;hex80000001;asc;;1:len6;hex0000000386d3;asc;;2:len7;hex370000003206e2;asc72;;3:len4;hex80000001;asc;;RECORDLOCKSspaceid128pageno4nbits72indexc1oftable`test`.`tt`trxid231123lockmodeS(LOCK_S)locksgapandrec(LOCK_ORDINARY[next_key_lock])Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits00:len8;hex73757072656d756d;ascsupremum;;Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;RECORDLOCKSspaceid128pageno4nbits72indexc1oftable`test`.`tt`trxid231123lockmodeS(LOCK_S)locksgapbeforerec(LOCK_GAP)Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000001;asc;;1:len4;hex80000002;asc;;
到这里RR和RC加锁并没有什么不同，因为都是唯一值，同时锁继承也都是二级索引上的都是LOCK_S|LOCK_ORDINARY[next_key_lock]，但是下面就会出现不同了。
然后分析session 2的第一句：select * from tt where c1=1 for update;
实际上这个时候存在2条c1=1的记录只有1,1标记为删除了，1,2没有提交，都是需要访问的。
然后堵塞行为为：
RR
LOCKWAIT2lockstruct(s),heapsize1160,1rowlock(s)MySQLthreadid10,OSthreadhandle140737153824512,queryid350localhostrootstatisticsselect*fromttwherec1=1forupdate-------TRXHASBEENWAITING11SECFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid129pageno4nbits72indexc1oftable`test`.`tt`trxid231146lock_modeX(LOCK_X)locksgapandrec(LOCK_ORDINARY[next_key_lock])waiting(LOCK_WAIT)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;
RC
LOCKWAIT2lockstruct(s),heapsize1160,1rowlock(s)MySQLthreadid10,OSthreadhandle140737153824512,queryid325localhostrootstatisticsselect*fromttwherec1=1forupdate-------TRXHASBEENWAITING9SECFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid128pageno4nbits72indexc1oftable`test`.`tt`trxid231128lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)waiting(LOCK_WAIT)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;
我们这里可以看到对于RR模式下唯一键c1的1,1已经删除了。我做了debug发现这里会在函数中row_search_mvcc加锁前做判断如下：
if(!set_also_gap_locks||srv_locks_unsafe_for_binlog||trx->isolation_level<=TRX_ISO_READ_COMMITTED||(unique_search&&!rec_get_deleted_flag(rec,comp))||dict_index_is_spatial(index)){gotono_gap_lock;}else{lock_type=LOCK_ORDINARY;}
我们抛开其他来分析这两句
trx->isolation_level <= TRX_ISO_READ_COMMITTED
如果是RC模式则直接上LOCK_REC_NOT_GAP及只锁住记录本身
(unique_search && !rec_get_deleted_flag(rec, comp))
如果不是RC，如果是二级唯一索引并且没有被标记为del flag则标记为LOCK_REC_NOT_GAP。但是如果标记为del flag则标记为LOCK_ORDINARY就是next key lock。
select * from tt where c1=1 for update;
如上这个语句会访问1,1标记为删除了，1,2没有提交 的两个记录。这个时候就出现了不同。
RC
只需要唯一索引 1,1上 LOCK_REC_NOT_GAP|LOCK_X 及记录索引，这个锁在本事物的第一句语句上已经获得了，因此直接通过了，不需要做检测。
RR
需要在唯一索引 1,1上 LOCK_ORDINARY|LOCK_X 也就是就是next key lock。这个锁在本事物中并没有获取过，因此需要重新的检测所的兼容性，最终加入了等待列表。
我们来看一下函数lock_rec_lock_slow，我做debug的时候明显看到了不同的逻辑：
if(lock_rec_has_expl(mode,block,heap_no,trx)){/*Thetrxalreadyhasastrongenoughlockonrec:do1,1keylockRRNEXKEYLOCKstronagernothing*/lock_rec_print(mode,block,heap_no,index,thr_get_trx(thr));err=DB_SUCCESS;}else{constlock_t*wait_for=lock_rec_other_has_conflicting(mode,block,heap_no,trx,index);if(wait_for!=NULL){/*Ifanothertransactionhasanon-gapconflictingrequestinthequeue,asthistransactiondoesnothavealockstrongenoughalreadygrantedontherecord,wemayhavetowait.*/RecLockrec_lock(thr,index,block,heap_no,mode);err=rec_lock.add_to_waitq(wait_for);}三、总结
最终RR下形成了环路
session1 首先获得唯一索引上的 1,1记录的 LOCK_REC_NOT_GAP|LOCK_X
然后session 1做update 获得唯一索引上的 1,1记录的 LOCK_ORDINARY(next key lock)|LOCK_S
然后session 2需要获取唯一索引上的 1,1记录的 LOCK_ORDINARY(next key lock)|LOCK_X 发生等待
然后session 1需要获取唯一索引上的 1,1记录的 LOCK_ORDINARY(next key lock)|LOCK_X 加入等待队列进行等待
死锁发生因此发生，而RC模式下最后两部需要获取的都是 LOCK_REC_NOT_GAP|LOCK_X，虽然session 2处于等待但是session因为已经获得相同级别的锁不会在进行检测加锁等待，而直接通过了。
下面是死锁的记录：
***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid117pageno4nbits72indexc1oftable`t1`.`tt`trxid230530lock_modeX(LOCK_X)locksgapandrec(LOCK_ORDINARY[next_key_lock])waiting(LOCK_WAIT)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;***(2)TRANSACTION:TRANSACTION230525,ACTIVE68secstartingindexreadmysqltablesinuse1,locked16lockstruct(s),heapsize1160,6rowlock(s),undologentries2MySQLthreadid6,OSthreadhandle140737153423104,queryid156localhostrootstatisticsselect*fromttwherec1=1forupdate***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid117pageno4nbits72indexc1oftable`t1`.`tt`trxid230525lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid117pageno4nbits72indexc1oftable`t1`.`tt`trxid230525lock_modeX(LOCK_X)locksgapandrec(LOCK_ORDINARY[next_key_lock])waiting(LOCK_WAIT)Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits320:len4;hex80000001;asc;;1:len4;hex80000001;asc;;***WEROLLBACKTRANSACTION(1)四、栈帧参考
最后留下几个栈帧以备分析使用
锁继承
#0lock_rec_set_nth_bit(lock=0x30b1230,i=3)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/lock0priv.ic:91#10x0000000001a5d44ainRecLock::lock_alloc(trx=0x7fffd7803b10,index=0x7ffe7459ff80,mode=546,rec_id=...,size=9)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:1484#20x0000000001a5d826inRecLock::create(this=0x7fffec0c0eb0,trx=0x7fffd7803b10,owns_trx_mutex=false,add_to_hash=true,prdt=0x0)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:1537#30x0000000001a5e60cinlock_rec_add_to_queue(type_mode=546,block=0x7fff9adb8150,heap_no=3,index=0x7ffe7459ff80,trx=0x7fffd7803b10,caller_owns_trx_mutex=false)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:1853#40x0000000001a611ecinlock_rec_inherit_to_gap_if_gap_lock(block=0x7fff9adb8150,heir_heap_no=3,heap_no=1)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:2829#50x0000000001a62ea3inlock_update_insert(block=0x7fff9adb8150,rec=0x7fff9b9c408c"\200")at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:3659#60x0000000001c53c25inbtr_cur_optimistic_insert(flags=0,cursor=0x7fffec0c23f0,offsets=0x7fffec0c24c8,heap=0x7fffec0c13e0,entry=0x7ffe7403bb70,rec=0x7fffec0c24c0,big_rec=0x7fffec0c24b8,n_ext=0,thr=0x7ffe7403ba00,mtr=0x7fffec0c1bc0)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:3346#70x0000000001b195feinrow_ins_sec_index_entry_low(flags=0,mode=2,index=0x7ffe7459ff80,offsets_heap=0x7ffe7403bf98,heap=0x7ffe7403c448,entry=0x7ffe7403bb70,trx_id=0,thr=0x7ffe7403ba00,dup_chk_only=false)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0ins.cc:3166#80x0000000001b1a15einrow_ins_sec_index_entry(index=0x7ffe7459ff80,entry=0x7ffe7403bb70,thr=0x7ffe7403ba00,dup_chk_only=false)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0ins.cc:3421#90x0000000001b9e053inrow_upd_sec_index_entry(node=0x7ffe7403b6f8,thr=0x7ffe7403ba00)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0upd.cc:2337#100x0000000001b9e1c3inrow_upd_sec_step(node=0x7ffe7403b6f8,thr=0x7ffe7403ba00)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0upd.cc:2364
RR加锁del flag记录
#0lock_rec_set_nth_bit(lock=0x30b28b8,i=2)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/lock0priv.ic:91#10x0000000001a5d44ainRecLock::lock_alloc(trx=0x7fffd7804080,index=0x7ffe74064ea0,mode=259,rec_id=...,size=9)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:1484#20x0000000001a5d826inRecLock::create(this=0x7fffec0c1e00,trx=0x7fffd7804080,owns_trx_mutex=true,add_to_hash=true,prdt=0x0)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:1537#30x0000000001a5e1c4inRecLock::add_to_waitq(this=0x7fffec0c1e00,wait_for=0x30b0e58,prdt=0x0)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:1731#40x0000000001a5ee37inlock_rec_lock_slow(impl=0,mode=3,block=0x7fff4d027b20,heap_no=2,index=0x7ffe74064ea0,thr=0x7ffe7459ff60)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:2004#50x0000000001a5f1ceinlock_rec_lock(impl=false,mode=3,block=0x7fff4d027b20,heap_no=2,index=0x7ffe74064ea0,thr=0x7ffe7459ff60)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:2082#60x0000000001a69a02inlock_sec_rec_read_check_and_lock(flags=0,block=0x7fff4d027b20,rec=0x7fff4da8c07e"\200",index=0x7ffe74064ea0,offsets=0x7fffec0c2690,mode=LOCK_X,gap_mode=0,thr=0x7ffe7459ff60)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:6457#70x0000000001b717f7insel_set_rec_lock(pcur=0x7ffe7459f6d0,rec=0x7fff4da8c07e"\200",index=0x7ffe74064ea0,offsets=0x7fffec0c2690,mode=3,type=0,thr=0x7ffe7459ff60,mtr=0x7fffec0c2180)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:1270#80x0000000001b7ab6ainrow_search_mvcc(buf=0x7ffe7405b9c0"\375\002",mode=PAGE_CUR_GE,prebuilt=0x7ffe7459f4b0,match_mode=1,direction=0)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:5591
RC加锁del flag记录
#0lock_rec_lock_slow(impl=0,mode=1027,block=0x7fff3310cdf0,heap_no=2,index=0x7ffe74076d90,thr=0x7ffe7459fc20)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:1962#10x0000000001a5f1ceinlock_rec_lock(impl=false,mode=1027,block=0x7fff3310cdf0,heap_no=2,index=0x7ffe74076d90,thr=0x7ffe7459fc20)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:2082#20x0000000001a69a02inlock_sec_rec_read_check_and_lock(flags=0,block=0x7fff3310cdf0,rec=0x7fff33bdc07e"\200",index=0x7ffe74076d90,offsets=0x7fffec0c2690,mode=LOCK_X,gap_mode=1024,thr=0x7ffe7459fc20)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:6457#30x0000000001b717f7insel_set_rec_lock(pcur=0x7ffe7459f6d0,rec=0x7fff33bdc07e"\200",index=0x7ffe74076d90,offsets=0x7fffec0c2690,mode=3,type=1024,thr=0x7ffe7459fc20,mtr=0x7fffec0c2180)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:1270#40x0000000001b7ab6ainrow_search_mvcc(buf=0x7ffe7403ae80"\375\002",mode=PAGE_CUR_GE,prebuilt=0x7ffe7459f4b0,match_mode=1,direction=0)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:5591#50x00000000019d5493inha_innobase::index_read(this=0x7ffe7403cda0,buf=0x7ffe7403ae80"\375\002",key_ptr=0x7ffe74095600"",key_len=5,find_flag=HA_READ_KEY_EXACT)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536#60x0000000000f934aainhandler::index_read_map(this=0x7ffe7403cda0,buf=0x7ffe7403ae80"\375\002",key=0x7ffe74095600"",keypart_map=1,find_flag=HA_READ_KEY_EXACT)at/root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.h:2942
到此，相信大家对“MySQL的RR模式下死锁”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
