本篇内容介绍了“MySQL死锁分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

RC 隔离级别很少出GAP我已经知道的

继承和分裂会出LOCK_GAP这是代码写死的
purge线程可能触发
页的分裂融合可能触发
内部回滚可能触发

唯一性检查会出LOCK_ORDINARY[next_key_lock]

一、构造死锁

RC RR级别通用

死锁表结构和数据

droptabletestunj1;createtabletestunj1(id1intprimarykey,id2intuniquekey,namevarchar(20));insertintotestunj1values(1,1,'gaopeng'),(10,10,'gaopeng'),(20,20,'gaopeng');mysql>select*fromtestunj1;+-----+------+---------+|id1|id2|name|+-----+------+---------+|1|1|gaopeng||10|10|gaopeng||20|20|gaopeng|+-----+------+---------+3rowsinset(0.01sec)

死锁构造流程

T1T2T3begin;insert into testunj1 values(17,17,'gaopeng'); insert into testunj1 values(15,15,'gaopeng');


begin; insert into testunj1 values(14,15,'gaopeng');堵塞


begin; insert into testunj1 values(16,17,'gaopeng');堵塞rollback;成功死锁

死锁记录

------------------------LATESTDETECTEDDEADLOCK------------------------2017-08-2905:03:470x7f2fdc6f0700***(1)TRANSACTION:TRANSACTION7261233,ACTIVE12secinsertingmysqltablesinuse1,locked1LOCKWAIT4lockstruct(s),heapsize1136,2rowlock(s),undologentries1MySQLthreadid3,OSthreadhandle139843538720512,queryid583localhostrootupdateinsertintotestunj1values(14,15,'gaopeng')***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid797pageno4nbits72indexid2oftable`test`.`testunj1`trxid7261233lock_modeXlocksgapbeforerecinsertintentionwaitingRecordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000014;asc;;1:len4;hex80000014;asc;;***(2)TRANSACTION:TRANSACTION7261234,ACTIVE5secinsertingmysqltablesinuse1,locked14lockstruct(s),heapsize1136,2rowlock(s),undologentries1MySQLthreadid4,OSthreadhandle139843538454272,queryid585localhostrootupdateinsertintotestunj1values(16,17,'gaopeng')***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid797pageno4nbits72indexid2oftable`test`.`testunj1`trxid7261234lockmodeSlocksgapbeforerecRecordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000014;asc;;1:len4;hex80000014;asc;;***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid797pageno4nbits72indexid2oftable`test`.`testunj1`trxid7261234lock_modeXlocksgapbeforerecinsertintentionwaitingRecordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000014;asc;;1:len4;hex80000014;asc;;***WEROLLBACKTRANSACTION(2)二、分析

这个死锁实际上涉及到锁的继承和分裂我们分析如下两个事物堵塞的案例和加锁步骤，主要弄明白gap lock怎么来的。

setglobalinnodb_lock_wait_timeout=200000;setglobalinnodb_show_verbose_locks=1;setglobaltransaction_isolation=1;重新登陆会话建立表和插入数据如下：droptabletestunj1;createtabletestunj1(id1intprimarykey,id2intuniquekey,namevarchar(20));insertintotestunj1values(1,1,'gaopeng'),(10,10,'gaopeng'),(20,20,'gaopeng');如果有debug环境gdb断点：lock_rec_set_nth_bit

步骤如下：

T1T2阶段1
BEGIN;insert into testunj1 values(17,17,'gaopeng');

BEGIN;insert into testunj1 values(16,17,'gaopeng'); 堵塞阶段2
ROLLBACK;

我们只用2个事物来分析流程，实际上流程知道了原因也就知道了。

- 阶段1 T1不提交T2堵塞

前奏
T1的插入不上任何锁，因为插入如果下一条记录没有锁，因此是隐含锁。分析从T2
insert into testunj1 values(16,17,'gaopeng'); 堵塞开始

第一步 T2执行 insert into testunj1 values(16,17,'gaopeng'); 步骤1

T2帮助T1隐士锁转换 上LOCK_X，这里通过函数lock_rec_convert_impl_to_expl 进行转换。

栈帧如下：

(gdb)bt#0lock_rec_set_nth_bit(lock=0x3054068,i=5)at/root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#10x0000000001a3f0cfinRecLock::lock_alloc(trx=0x7ffff10c95a0,index=0x7fffa89e3410,mode=1059,rec_id=...,size=9)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484#20x0000000001a3f435inRecLock::create(this=0x7ffff0d59d20,trx=0x7ffff10c95a0,owns_trx_mutex=false,add_to_hash=true,prdt=0x0)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537#30x0000000001a40152inlock_rec_add_to_queue(type_mode=1059,block=0x7fffea699ba0,heap_no=5,index=0x7fffa89e3410,trx=0x7ffff10c95a0,caller_owns_trx_mutex=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1853#40x0000000001a49ceeinlock_rec_convert_impl_to_expl_for_trx(block=0x7fffea699ba0,rec=0x7fffeae680a8"\200",index=0x7fffa89e3410,offsets=0x7fff9c02ef90,trx=0x7ffff10c95a0,heap_no=5)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6180#50x0000000001a4a124inlock_rec_convert_impl_to_expl(block=0x7fffea699ba0,rec=0x7fffeae680a8"\200",index=0x7fffa89e3410,offsets=0x7fff9c02ef90)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6242#60x0000000001a4a9f6inlock_sec_rec_read_check_and_lock(flags=0,block=0x7fffea699ba0,rec=0x7fffeae680a8"\200",index=0x7fffa89e3410,offsets=0x7fff9c02ef90,mode=LOCK_S,gap_mode=0,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6446#70x0000000001aeff23inrow_ins_set_shared_rec_lock(type=0,block=0x7fffea699ba0,rec=0x7fffeae680a8"\200",index=0x7fffa89e3410,offsets=0x7fff9c02ef90,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:1483#80x0000000001af108dinrow_ins_scan_sec_index_for_duplicate(flags=0,index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18,s_latch=false,mtr=0x7ffff0d5aec0,offsets_heap=0x7fff9c02ef08)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:2115#90x0000000001af3440inrow_ins_sec_index_entry_low(flags=0,mode=2,index=0x7fffa89e3410,offsets_heap=0x7fff9c02ef08,heap=0x7fff9c00e918,entry=0x7fff9c01aa70,trx_id=0,thr=0x7fff9c035c18,dup_chk_only=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3034#100x0000000001af451dinrow_ins_sec_index_entry(index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18,dup_chk_only=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421#110x0000000001af46d1inrow_ins_index_entry(index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470#120x0000000001af4bf1inrow_ins_index_entry_step(node=0x7fff9c035978,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618#130x0000000001af4f67inrow_ins(node=0x7fff9c035978,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760#140x0000000001af5564inrow_ins_step(thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945#150x0000000001b14775inrow_insert_for_mysql_using_ins_graph(mysql_rec=0x7fff9c034b10"\374\020",prebuilt=0x7fff9c0353a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283#160x0000000001b14c7dinrow_insert_for_mysql(mysql_rec=0x7fff9c034b10"\374\020",prebuilt=0x7fff9c0353a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406#170x00000000019b87e5inha_innobase::write_row(this=0x7fff9c0345d0,record=0x7fff9c034b10"\374\020")at/root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344#180x0000000000f7d74dinhandler::ha_write_row(this=0x7fff9c0345d0,buf=0x7fff9c034b10"\374\020")at/root/softm/percona-server-5.7.22-22/sql/handler.cc:8466#190x00000000017ed7e9inwrite_record(thd=0x7fff9c000b70,table=0x7fff9c033bd0,info=0x7ffff0d5ca00,update=0x7ffff0d5c980)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881#200x00000000017ea893inSql_cmd_insert::mysql_insert(this=0x7fff9c006e90,thd=0x7fff9c000b70,table_list=0x7fff9c0068f8)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773#210x00000000017f141dinSql_cmd_insert::execute(this=0x7fff9c006e90,thd=0x7fff9c000b70)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121#220x00000000015b9a83inmysql_execute_command(thd=0x7fff9c000b70,first_level=true)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746#230x00000000015c030einmysql_parse(thd=0x7fff9c000b70,parser_state=0x7ffff0d5e600)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#240x00000000015b3ea2indispatch_command(thd=0x7fff9c000b70,com_data=0x7ffff0d5ed70,command=COM_QUERY)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#250x00000000015b2c2findo_command(thd=0x7fff9c000b70)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#260x00000000016fb8a8inhandle_connection(arg=0x38e2880)at/root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#270x00000000019320beinpfs_spawn_thread(arg=0x3c64160)at/root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190---Type<return>tocontinue,orq<return>toquit---#280x00007ffff79c3aa1instart_thread()from/lib64/libpthread.so.0#290x00007ffff6516bcdinclone()from/lib64/libc.so.6

第二步 insert into testunj1 values(16,17,'gaopeng'); 步骤2

需要做唯一性检查不通过上LOCK_ORDINARY[next_key_lock]等待，唯一检查会涉及到主键和唯一键，如果主键检查通过则会插入数据，然后检查二级唯一索引，如果唯一索引冲突，则主键插入的数据需要回滚。这里是因为每个索引是单独调用row_ins_index_entry_step上层函数进行单独插入的。

栈帧如下：

(gdb)bt#0lock_rec_set_nth_bit(lock=0x30580e8,i=5)at/root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#10x0000000001a3f0cfinRecLock::lock_alloc(trx=0x7ffff10ca5f0,index=0x7fffa89e3410,mode=258,rec_id=...,size=9)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484#20x0000000001a3f435inRecLock::create(this=0x7ffff0d5a1b0,trx=0x7ffff10ca5f0,owns_trx_mutex=true,add_to_hash=true,prdt=0x0)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537#30x0000000001a3fd2binRecLock::add_to_waitq(this=0x7ffff0d5a1b0,wait_for=0x3054068,prdt=0x0)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1731#40x0000000001a4091finlock_rec_lock_slow(impl=0,mode=2,block=0x7fffea699ba0,heap_no=5,index=0x7fffa89e3410,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2004#50x0000000001a40c94inlock_rec_lock(impl=false,mode=2,block=0x7fffea699ba0,heap_no=5,index=0x7fffa89e3410,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2082#60x0000000001a4ab0ainlock_sec_rec_read_check_and_lock(flags=0,block=0x7fffea699ba0,rec=0x7fffeae680a8"\200",index=0x7fffa89e3410,offsets=0x7fff9c02ef90,mode=LOCK_S,gap_mode=0,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6457#70x0000000001aeff23inrow_ins_set_shared_rec_lock(type=0,block=0x7fffea699ba0,rec=0x7fffeae680a8"\200",index=0x7fffa89e3410,offsets=0x7fff9c02ef90,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:1483#80x0000000001af108dinrow_ins_scan_sec_index_for_duplicate(flags=0,index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18,s_latch=false,mtr=0x7ffff0d5aec0,offsets_heap=0x7fff9c02ef08)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:2115#90x0000000001af3440inrow_ins_sec_index_entry_low(flags=0,mode=2,index=0x7fffa89e3410,offsets_heap=0x7fff9c02ef08,heap=0x7fff9c00e918,entry=0x7fff9c01aa70,trx_id=0,thr=0x7fff9c035c18,dup_chk_only=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3034#100x0000000001af451dinrow_ins_sec_index_entry(index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18,dup_chk_only=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421#110x0000000001af46d1inrow_ins_index_entry(index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470#120x0000000001af4bf1inrow_ins_index_entry_step(node=0x7fff9c035978,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618#130x0000000001af4f67inrow_ins(node=0x7fff9c035978,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760#140x0000000001af5564inrow_ins_step(thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945#150x0000000001b14775inrow_insert_for_mysql_using_ins_graph(mysql_rec=0x7fff9c034b10"\374\020",prebuilt=0x7fff9c0353a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283#160x0000000001b14c7dinrow_insert_for_mysql(mysql_rec=0x7fff9c034b10"\374\020",prebuilt=0x7fff9c0353a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406#170x00000000019b87e5inha_innobase::write_row(this=0x7fff9c0345d0,record=0x7fff9c034b10"\374\020")at/root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344#180x0000000000f7d74dinhandler::ha_write_row(this=0x7fff9c0345d0,buf=0x7fff9c034b10"\374\020")at/root/softm/percona-server-5.7.22-22/sql/handler.cc:8466#190x00000000017ed7e9inwrite_record(thd=0x7fff9c000b70,table=0x7fff9c033bd0,info=0x7ffff0d5ca00,update=0x7ffff0d5c980)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881#200x00000000017ea893inSql_cmd_insert::mysql_insert(this=0x7fff9c006e90,thd=0x7fff9c000b70,table_list=0x7fff9c0068f8)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773#210x00000000017f141dinSql_cmd_insert::execute(this=0x7fff9c006e90,thd=0x7fff9c000b70)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121#220x00000000015b9a83inmysql_execute_command(thd=0x7fff9c000b70,first_level=true)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746#230x00000000015c030einmysql_parse(thd=0x7fff9c000b70,parser_state=0x7ffff0d5e600)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#240x00000000015b3ea2indispatch_command(thd=0x7fff9c000b70,com_data=0x7ffff0d5ed70,command=COM_QUERY)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#250x00000000015b2c2findo_command(thd=0x7fff9c000b70)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#260x00000000016fb8a8inhandle_connection(arg=0x38e2880)at/root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#270x00000000019320beinpfs_spawn_thread(arg=0x3c64160)at/root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190---Type<return>tocontinue,orq<return>toquit---#280x00007ffff79c3aa1instart_thread()from/lib64/libpthread.so.0#290x00007ffff6516bcdinclone()from/lib64/libc.so.6

这两步完成后，我们可以到LOCK_S|LOCK_ORDINARY[next_key_lock]的存在

---TRANSACTION19508,ACTIVE143secinsertingmysqltablesinuse1,locked1LOCKWAIT2lockstruct(s),heapsize1160,1rowlock(s),undologentries1MySQLthreadid4,OSthreadhandle140737233942272,queryid684localhostrootupdateinsertintotestunj1values(16,17,'gaopeng')-------TRXHASBEENWAITING0SECFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid91pageno4nbits72indexid2oftable,addris0x3054068`test`.`testunj1`trxid19508lockmodeS(LOCK_S)locksgapandrec(LOCK_ORDINARY[next_key_lock])waiting(LOCK_WAIT)Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000011;asc;;1:len4;hex80000011;asc;;---TRANSACTION19507,ACTIVE148sec2lockstruct(s),heapsize1160,1rowlock(s),undologentries1MySQLthreadid3,OSthreadhandle140737234208512,queryid682localhostrootTABLELOCKtable`test`.`testunj1`trxid19507lockmodeIXRECORDLOCKSspaceid91pageno4nbits72indexid2oftable,addris0x3056b48`test`.`testunj1`trxid19507lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000011;asc;;1:len4;hex80000011;asc;;- 阶段2 做ROLLBACK

第三步 T1帮助T2做锁继承
从事物的指针0x7ffff10ca5f0可以看出是T2，如果有多个事物LOCK_GAP是兼容所以都可以继承完成，LOCK_GAP的存在只是为了LOCK_INTENTION，就是为了防止幻读。
如果做了GDB可以看到这里继承的锁：

(gdb)plock->type_mode$1=546

546 = 512+2+32= LOCK_GAP+LOCK_S+LOCK_REC 这个锁继承给了 heap 4 也就是记录 20(heir_heap_no=4, heap_no=5)

这里将LOCK_S|LOCK_GAP 继承到heap_no 4 上也就是 记录记录20上。

栈帧如下：

2018-10-11T08:15:44.292686Z4[Note]InnoDB:Trx(19999)isblocked!!!!![SwitchingtoThread0x7ffff0da0700(LWP9278)]Breakpoint2,lock_rec_set_nth_bit(lock=0x3058230,i=4)at/root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:9191ut_ad(lock);(gdb)bt#0lock_rec_set_nth_bit(lock=0x3058230,i=4)at/root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#10x0000000001a3f0cfinRecLock::lock_alloc(trx=0x7ffff10ca5f0,index=0x7fffa89e3410,mode=546,rec_id=...,size=9)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484#20x0000000001a3f435inRecLock::create(this=0x7ffff0d9ada0,trx=0x7ffff10ca5f0,owns_trx_mutex=false,add_to_hash=true,prdt=0x0)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537#30x0000000001a40152inlock_rec_add_to_queue(type_mode=546,block=0x7fffea699ba0,heap_no=4,index=0x7fffa89e3410,trx=0x7ffff10ca5f0,caller_owns_trx_mutex=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1853#40x0000000001a4299binlock_rec_inherit_to_gap(heir_block=0x7fffea699ba0,block=0x7fffea699ba0,heir_heap_no=4,heap_no=5)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2787#50x0000000001a4475einlock_update_delete(block=0x7fffea699ba0,rec=0x7fffeae680a8"\200")at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:3692#60x0000000001c26418inbtr_cur_optimistic_delete_func(cursor=0x7ffff0d9b7c0,flags=0,mtr=0x7ffff0d9b2b0)at/root/softm/percona-server-5.7.22-22/storage/innobase/btr/btr0cur.cc:5200#70x0000000001d54fe7inrow_undo_ins_remove_sec_low(mode=16386,index=0x7fffa89e3410,entry=0x7fffa89cde10,thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:260#80x0000000001d55101inrow_undo_ins_remove_sec(index=0x7fffa89e3410,entry=0x7fffa89cde10,thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:295#90x0000000001d555a8inrow_undo_ins_remove_sec_rec(node=0x7fffa89a25c0,thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:429#100x0000000001d55810inrow_undo_ins(node=0x7fffa89a25c0,thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:483#110x0000000001b69c80inrow_undo(node=0x7fffa89a25c0,thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0undo.cc:324#120x0000000001b69dcdinrow_undo_step(thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0undo.cc:370#130x0000000001abfea4inque_thr_step(thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1061#140x0000000001ac00aeinque_run_threads_low(thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1125#150x0000000001ac0254inque_run_threads(thr=0x7fffa89a23e8)at/root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1165#160x0000000001bcf4dcintrx_rollback_to_savepoint_low(trx=0x7ffff10c95a0,savept=0x0)at/root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:118#170x0000000001bcf714intrx_rollback_for_mysql_low(trx=0x7ffff10c95a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:180#180x0000000001bcf9b2intrx_rollback_low(trx=0x7ffff10c95a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:212#190x0000000001bcfcebintrx_rollback_for_mysql(trx=0x7ffff10c95a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:289#200x00000000019b1c6cininnobase_rollback(hton=0x2edf1f0,thd=0x7fffa8012940,rollback_trx=true)at/root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:5126#210x0000000000f6db24inha_rollback_low(thd=0x7fffa8012940,all=true)at/root/softm/percona-server-5.7.22-22/sql/handler.cc:2007#220x00000000018671d9inMYSQL_BIN_LOG::rollback(this=0x2e39a40,thd=0x7fffa8012940,all=true)at/root/softm/percona-server-5.7.22-22/sql/binlog.cc:2447#230x0000000000f6ddbainha_rollback_trans(thd=0x7fffa8012940,all=true)at/root/softm/percona-server-5.7.22-22/sql/handler.cc:2094#240x00000000016ca4d5intrans_rollback(thd=0x7fffa8012940)at/root/softm/percona-server-5.7.22-22/sql/transaction.cc:356#250x00000000015bc90ainmysql_execute_command(thd=0x7fffa8012940,first_level=true)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:4556#260x00000000015c030einmysql_parse(thd=0x7fffa8012940,parser_state=0x7ffff0d9f600)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#270x00000000015b3ea2indispatch_command(thd=0x7fffa8012940,com_data=0x7ffff0d9fd70,command=COM_QUERY)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#280x00000000015b2c2findo_command(thd=0x7fffa8012940)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#290x00000000016fb8a8inhandle_connection(arg=0x3c52dd0)at/root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#300x00000000019320beinpfs_spawn_thread(arg=0x3c64160)at/root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190#310x00007ffff79c3aa1instart_thread()from/lib64/libpthread.so.0#320x00007ffff6516bcdinclone()from/lib64/libc.so.6

T2自己做分裂了
分裂(heir_heap_no=5, heap_no=4) 可以看到这里将 记录20的type_mode=546分裂给记录17 也就是512+2+32=LOCK_GAP+LOCK_S+LOCK_REC。

栈帧如下：

[SwitchingtoThread0x7ffff0d5f700(LWP9548)]Breakpoint2,lock_rec_set_nth_bit(lock=0x3058230,i=5)at/root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:9191ut_ad(lock);(gdb)bt#0lock_rec_set_nth_bit(lock=0x3058230,i=5)at/root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#10x0000000001a400fainlock_rec_add_to_queue(type_mode=546,block=0x7fffea699ba0,heap_no=5,index=0x7fffa89e3410,trx=0x7ffff10ca5f0,caller_owns_trx_mutex=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1845#20x0000000001a42acfinlock_rec_inherit_to_gap_if_gap_lock(block=0x7fffea699ba0,heir_heap_no=5,heap_no=4)at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2829#30x0000000001a44643inlock_update_insert(block=0x7fffea699ba0,rec=0x7fffeae680a8"\200")at/root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:3659#40x0000000001c219b4inbtr_cur_optimistic_insert(flags=0,cursor=0x7ffff0d5b6f0,offsets=0x7ffff0d5b7c8,heap=0x7ffff0d5a6e0,entry=0x7fff9c01aa70,rec=0x7ffff0d5b7c0,big_rec=0x7ffff0d5b7b8,n_ext=0,thr=0x7fff9c035c18,mtr=0x7ffff0d5aec0)at/root/softm/percona-server-5.7.22-22/storage/innobase/btr/btr0cur.cc:3346#50x0000000001af3a0dinrow_ins_sec_index_entry_low(flags=0,mode=2,index=0x7fffa89e3410,offsets_heap=0x7fff9c00e918,heap=0x7fff9c02ef08,entry=0x7fff9c01aa70,trx_id=0,thr=0x7fff9c035c18,dup_chk_only=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3166#60x0000000001af451dinrow_ins_sec_index_entry(index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18,dup_chk_only=false)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421#70x0000000001af46d1inrow_ins_index_entry(index=0x7fffa89e3410,entry=0x7fff9c01aa70,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470#80x0000000001af4bf1inrow_ins_index_entry_step(node=0x7fff9c035978,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618#90x0000000001af4f67inrow_ins(node=0x7fff9c035978,thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760#100x0000000001af5564inrow_ins_step(thr=0x7fff9c035c18)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945#110x0000000001b14775inrow_insert_for_mysql_using_ins_graph(mysql_rec=0x7fff9c034b10"\374\020",prebuilt=0x7fff9c0353a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283#120x0000000001b14c7dinrow_insert_for_mysql(mysql_rec=0x7fff9c034b10"\374\020",prebuilt=0x7fff9c0353a0)at/root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406#130x00000000019b87e5inha_innobase::write_row(this=0x7fff9c0345d0,record=0x7fff9c034b10"\374\020")at/root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344#140x0000000000f7d74dinhandler::ha_write_row(this=0x7fff9c0345d0,buf=0x7fff9c034b10"\374\020")at/root/softm/percona-server-5.7.22-22/sql/handler.cc:8466#150x00000000017ed7e9inwrite_record(thd=0x7fff9c000b70,table=0x7fff9c033bd0,info=0x7ffff0d5ca00,update=0x7ffff0d5c980)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881#160x00000000017ea893inSql_cmd_insert::mysql_insert(this=0x7fff9c006e90,thd=0x7fff9c000b70,table_list=0x7fff9c0068f8)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773#170x00000000017f141dinSql_cmd_insert::execute(this=0x7fff9c006e90,thd=0x7fff9c000b70)at/root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121#180x00000000015b9a83inmysql_execute_command(thd=0x7fff9c000b70,first_level=true)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746#190x00000000015c030einmysql_parse(thd=0x7fff9c000b70,parser_state=0x7ffff0d5e600)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#200x00000000015b3ea2indispatch_command(thd=0x7fff9c000b70,com_data=0x7ffff0d5ed70,command=COM_QUERY)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#210x00000000015b2c2findo_command(thd=0x7fff9c000b70)at/root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#220x00000000016fb8a8inhandle_connection(arg=0x38e2880)at/root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#230x00000000019320beinpfs_spawn_thread(arg=0x3c64160)at/root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190#240x00007ffff79c3aa1instart_thread()from/lib64/libpthread.so.0#250x00007ffff6516bcdinclone()from/lib64/libc.so.6(gdb)

最终形成如下的锁模式，因为记录 11,11已经不存在了因此

addris0x30580e8`test`.`testunj1`trxid19999lockmodeS(LOCK_S)locksgapandrec(LOCK_ORDINARY[next_key_lock])

下不会有任何记录

---TRANSACTION19999,ACTIVE972sec3lockstruct(s),heapsize1160,2rowlock(s),undologentries1MySQLthreadid4,OSthreadhandle140737233942272,queryid687localhostrootstartingshowengineinnodbstatusTABLELOCKtable`test`.`testunj1`trxid19999lockmodeIXRECORDLOCKSspaceid93pageno4nbits72indexid2oftable,addris0x30580e8`test`.`testunj1`trxid19999lockmodeS(LOCK_S)locksgapandrec(LOCK_ORDINARY[next_key_lock])RECORDLOCKSspaceid93pageno4nbits72indexid2oftable,addris0x3058230`test`.`testunj1`trxid19999lockmodeS(LOCK_S)locksgapbeforerec(LOCK_GAP)Recordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000014;asc;;1:len4;hex80000014;asc;;Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex80000011;asc;;1:len4;hex80000010;asc;;

可以看到 lock mode S(LOCK_S) locks gap before rec(LOCK_GAP) 已经出来了。

三、断点及一些补遗

lock_rec_has_to_wait 检测是否需要等待

RecLock::add_to_waitq 将LOCK_T结构加入到rec hash中(等待)

RecLock::lock_add 将LOCK_T结构加入到rec hash中

lock_rec_set_nth_bit 设置LOCK_T位图

row_ins_scan_sec_index_for_duplicate 二级唯一索引唯一性检查加锁函数

lock_rec_other_has_conflicting 判断冲突->lock_rec_has_to_wait 检测是否需要等待

lock_rec_inherit_to_gap LOCK继承函数

lock_rec_inherit_to_gap_if_gap_lock LOCK分裂函数

lock_rec_convert_impl_to_expl 隐含锁转换函数，比较复杂

1、即便是同一个block，不同事物(即便是同一个事物的不同锁模式)也需要新建一个LOCK_T结构，来表示一个锁，其以space id/page no为基础。其内存结构BITMAP会以位图的形式每一位代表一行数据是否上锁(0 or 1)2、innodb锁类型只有LOCK_REC和LOCK_TABLE两种及行锁和表锁，但是可以有多种模式组合。3、rec hash 通过space id 和 page no 构造 那么同一块的 都放到一个链表中，同时这个链表上的可能还有冲突而来的，所以每次获取的时候必然查看page no和space id 参考lock_rec_add_to_queue -> lock_rec_get_first_on_page函数。4、每次增加一个lock_t 结构都会加入到rec hash中,这也是所谓的等待队列，加入队列就是指加入rec hash中关于本space id和page no的队列，当然最后还需要 bitmap的确认才能在page中找到这个锁的位置。5、不同的事物对于同一行数据的上锁通常不共享一个lock_t，他们共同连接到rec hash的链表下面6、判断某行是否上锁 需要不断循环整个链表使用heap no定位到bitmap 来进行判断。参考lock_rec_other_has_conflicting ->lock_rec_get_first。7、对于某些标记为del flag还没有purge的记录，在某些情况下会加锁，但是会跳过判断，参考row_ins_scan_sec_index_for_duplicate ->row_ins_dupl_error_with_rec 函数。

row_ins_scan_sec_index_for_duplicate 片段：

if(cmp==0&&!index->allow_duplicates){//记录相等并且是唯一索引，还需要判断唯一的字段是否能够对上，同时要确认不是delflag的记录if(row_ins_dupl_error_with_rec(rec,entry,index,offsets)){//这里会跳过delflag的记录不标记为重复，IF逻辑不会停止，会继续到一行err=DB_DUPLICATE_KEY;thr_get_trx(thr)->error_info=index;......gotoend_scan;}}else{ut_a(cmp<0||index->allow_duplicates);gotoend_scan;}

row_ins_dupl_error_with_rec 片段：

if(matched_fields<n_unique){//需要相同的字段否则判断为FLASEreturn(FALSE);}/*InauniquesecondaryindexweallowequalkeyvaluesiftheycontainSQLNULLs*/if(!dict_index_is_clust(index)&&!index->nulls_equal){for(i=0;i<n_unique;i++){if(dfield_is_null(dtuple_get_nth_field(entry,i))){return(FALSE);}}}return(!rec_get_deleted_flag(rec,rec_offs_comp(offsets)));//如果相同但是是delflag的记录则同样放回FALSE四、未分析出来的死锁

本系统没有ROLLBACK，但是 INSERT ON DUPLICATE 语句，对于 INSERT ON DUPLICATE/REPLACE语句都是做唯一性检查的时候普通语句进行报错，但是这两个语句会返回错误给上层，接着做相应的处理

INSERT ON DUPLICATE 是调用的UPDATE接口

REPLACE是调用的DELETE 然后 INSERT的接口

因此两类语句出现堵插入印象的地方就有两处要么是初次INSERT的时候，要么是唯一键冲突造成的二次更改上。而对于 INSERT ON DUPLICATE/REPLACE做唯一性检测的时候上的LOCK_X并非LOCK_S如下：

if(flags&BTR_NO_LOCKING_FLAG){/*Setnolockswhenapplyingloginonlinetablerebuild.*/}elseif(allow_duplicates){/*IftheSQL-querywillupdateorreplaceduplicatekeywewilltakeX-lockforduplicates(REPLACE,LOADDATAFILEREPLACE,INSERTONDUPLICATEKEYUPDATE).*/err=row_ins_set_exclusive_rec_lock(lock_type,block,rec,index,offsets,thr);//此处需要对这条数据加NTEXKEYLOCKLOCK_ORDINARYlock_rec_convert_impl_to_expl可能转换}else{err=row_ins_set_shared_rec_lock(lock_type,block,rec,index,offsets,thr);//此处需要对这条数据加NTEXKEYLOCKLOCK_ORDINARYlock_rec_convert_impl_to_expl可能转换隐含锁}

因此 INSERT ON DUPLICATE/REPLACE两个语句需要加锁的地方比较多并且流程比较复杂，分析比较麻烦。

死锁如下：

5.7.22

RC隔离级别

行数据2000W左右

INSERT ON DUPLICATE语句

LOCK_GAP存在

间隔一键时间触发本死锁

无应用发起ROLLBACK

唯一二级索引不太合理键值教长

如果哪位大哥分析出来请赐教

CREATETABLE`testgp`(`id`BIGINTUNSIGNEDAUTO_INCREMENTNOTNULLCOMMENT'id',`kdt_id`BIGINTUNSIGNEDNOTNULLDEFAULT'0',`conversation_id`VARCHAR(100)NOTNULL,`fans_uid`VARCHAR(50)NOTNULLCOMMENT'fansUId',`last_msg`BIGINTNOTNULL,`buyer_type`VARCHAR(32)NOTNULL,`last_receptionist_id`BIGINTUNSIGNEDNOTNULLDEFAULT'0',`created_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,`updated_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(`id`),UNIQUEKEY`uniq_key`(`kdt_id`,`conversation_id`),KEY`idx_kdt_lastmsg`(`kdt_id`,`last_msg`))ENGINE=InnoDBCHARSET=utf8mb4;

死锁记录

------------------------LATESTDETECTEDDEADLOCK------------------------2018-10-1212:26:220x7fd70f7ff700***(1)TRANSACTION:TRANSACTION33473425185,ACTIVE0secinsertingmysqltablesinuse1,locked1LOCKWAIT3lockstruct(s),heapsize1136,2rowlock(s),undologentries1MySQLthreadid1465312,OSthreadhandle140558885697280,queryid162808390710.255.201.50courierupdateINSERTINTOtestgp(`kdt_id`,`conversation_id`,`fans_uid`,`last_msg`,`buyer_type`,`last_receptionist_id`)VALUES(41372282,'41372282#mmp_6562662125#customerservice','mmp_6562662125',1539318382643,'mmp',0)ONDUPLICATEKEYUPDATElast_msg=1539318382643,buyer_type='mmp',last_receptionist_id=0,updated_at=CURRENT_TIMESTAMP()***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid1234pageno468974nbits320indexuniq_keyoftable`courier`.`testgp`trxid33473425185lock_modeXlocksgapbeforerecinsertintentionwaitingRecordlock,heapno211PHYSICALRECORD:n_fields3;compactformat;infobits00:len8;hex0000000002774a7a;ascwJz;;1:len30;hex3431333732323832237765636861745f3635343836323238323423637573;asc41372282#wechat_6548622824#cus;(total42bytes);2:len8;hex000000000836ffd3;asc6;;***(2)TRANSACTION:TRANSACTION33473425184,ACTIVE0secinserting,threaddeclaredinsideInnoDB1mysqltablesinuse1,locked14lockstruct(s),heapsize1136,3rowlock(s),undologentries1MySQLthreadid1460265,OSthreadhandle140561654740736,queryid162808390510.255.201.50courierupdateINSERTINTOtestgp(`kdt_id`,`conversation_id`,`fans_uid`,`last_msg`,`buyer_type`,`last_receptionist_id`)VALUES(41372282,'41372282#mmp_6563932378#customerservice','mmp_6563932378',1539318382633,'mmp',0)ONDUPLICATEKEYUPDATElast_msg=1539318382633,buyer_type='mmp',last_receptionist_id=0,updated_at=CURRENT_TIMESTAMP()***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid1234pageno468974nbits320indexuniq_keyoftable`courier`.`testgp`trxid33473425184lock_modeXlocksgapbeforerecRecordlock,heapno211PHYSICALRECORD:n_fields3;compactformat;infobits00:len8;hex0000000002774a7a;ascwJz;;1:len30;hex3431333732323832237765636861745f3635343836323238323423637573;asc41372282#wechat_6548622824#cus;(total42bytes);2:len8;hex000000000836ffd3;asc6;;***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid1234pageno468974nbits320indexuniq_keyoftable`courier`.`testgp`trxid33473425184lock_modeXlocksgapbeforerecinsertintentionwaitingRecordlock,heapno211PHYSICALRECORD:n_fields3;compactformat;infobits00:len8;hex0000000002774a7a;ascwJz;;1:len30;hex3431333732323832237765636861745f3635343836323238323423637573;asc41372282#wechat_6548622824#cus;(total42bytes);2:len8;hex000000000836ffd3;asc6;;***WEROLLBACKTRANSACTION(1)

“MySQL死锁分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！