聊聊MySQL中的死锁

欢迎关注微信公众号：互联网全栈架构

死锁是指两个或者多个事务互相持有对方所需的资源，从而导致它们都无法继续执行的情况。下图是一个死锁的示例，事务1锁住了id=1的数据（比如更新id=1的数据记录），同时请求锁住id=2的数据，但事务2持有id=2的锁，同时又请求id=1的锁，这样就造成了相互等待对方释放锁的情况，从而产生了死锁：

上图是死锁产生的示例说明，我们用实际的SQL来演示死锁的产生，首先创建一个测试表，它只有两个字段，id和数量，id为自增类型，然后向表中插入两条数据：

------------------------LATESTDETECTEDDEADLOCK------------------------2023-11-0815:57:230x4df8***(1)TRANSACTION:TRANSACTION350231,ACTIVE12secstartingindexreadmysqltablesinuse1,locked1LOCKWAIT3lockstruct(s),heapsize1136,2rowlock(s),undologentries1MySQLthreadid3,OSthreadhandle19044,queryid339localhost::1rootupdatingUPDATEt_testSETquantity=102WHEREid=2***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid743pageno3nbits72indexPRIMARYoftable`test`.`t_test`trxid350231lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno3PHYSICALRECORD:n_fields4;compactformat;infobits00:len4;hex80000002;asc;;1:len6;hex000000055818;ascX;;2:len7;hex2f000001401cb2;asc/@;;3:len4;hex800000c9;asc;;***(2)TRANSACTION:TRANSACTION350232,ACTIVE10secstartingindexread,threaddeclaredinsideInnoDB5000mysqltablesinuse1,locked13lockstruct(s),heapsize1136,2rowlock(s),undologentries1MySQLthreadid5,OSthreadhandle19960,queryid340localhost127.0.0.1rootupdatingUPDATEt_testSETquantity=202WHEREid=1***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid743pageno3nbits72indexPRIMARYoftable`test`.`t_test`trxid350232lock_modeXlocksrecbutnotgapRecordlock,heapno3PHYSICALRECORD:n_fields4;compactformat;infobits00:len4;hex80000002;asc;;1:len6;hex000000055818;ascX;;2:len7;hex2f000001401cb2;asc/@;;3:len4;hex800000c9;asc;;***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid743pageno3nbits72indexPRIMARYoftable`test`.`t_test`trxid350232lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno2PHYSICALRECORD:n_fields4;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex000000055817;ascX;;2:len7;hex2e0000018d1edf;asc.;;3:len4;hex80000065;asce;;***WEROLLBACKTRANSACTION(2)