数据库死锁是最难调试跟踪的。场景如下：同一张表，先在一个事务中插入一条记录，然后更新这条记录，在并发事务中会造成死锁。并且可以重现。有什么工具可以模拟并发交易、查看信息、解决问题？这就是我今天要分享的内容。1.前期准备setsessiontransactionisolationlevelrepeatableread;setsessionautocommit=0;createtablet(idint(20)primarykeyAUTO_INCREMENT,cellvarchar(20)unique)engine=innodb;starttransaction;insertintot(cell)values(11111111111);insertintot(cell)values(222222;insertotot22)(cell)values(33333333333);commit;解释：事件发生时，事务隔离级别为RR；对于多端实验，需要关闭自动提交事务；创建表，设置PK和unique，初始化数据；2、并发事务模拟SessionA:starttransaction;insertintot(cell)values(44444444444);[1]SessionB:starttransaction;insertintot(cell)values(5555555555);[2]updatesetcell=123wherecell=44444444444;[3]updatesetcell=456wherecell=55555545]555;[终端模拟并发事务：红色SQL为事务A；黑色SQL为事务B；[1][2][3][4]为执行顺序；3、实验现象insertintot(cell)values(44444444444);[1]事务A插入数据，***执行结果：插入成功insertintot(cell)values(55555555555);[2]事务B插入数据，第二次执行结果：插入结果updatesetcell=123wherecell=44444444444;[3]事务A修改[1]，第三次执行结果：阻塞，等待执行结果画外音：修改一段自己插入的数据，你还在等什么？updatesetcell=456wherecell=55555555555；[4]事务B修改[2]中插入的数据，***执行结果：事务B死锁，事务B回滚；事务A中，[3]语句被阻塞，执行成功；画外音：说明事务A中被阻塞的语句确实在等待事务B中的某一个锁4.结果分析两个事务修改了自己插入的数据，但是却出现了死锁，真是诡异。上述实验现象的两个核心问题是：语句[3]被阻塞了，它在等待什么锁？语句[4]陷入僵局。这时事务A和事务B必须互相占有一把锁，互相请求对方的锁。什么是锁？工具一：showengineinnodbstatus；执行后显示内容如下（放大仔细看）：信息量很大，不用着急，宿主会告诉你的。在***部分，关键词是：交易1，交易3998；执行updatesetcell=123wherecell=44444444444；等待这个锁被授予，记录锁，主键索引（indexprimary），互斥锁（lock_modeX），物理记录（physicalrecord），asc55555555555；画外音：英文不好没关系，抓住关键词。画外音，InnoDB存储引擎，聚簇索引和非聚簇索引的实现，确定会在聚簇索引上加锁，详见文章：《1分钟了解MyISAM与InnoDB的索引差异》。第二部分，关键字为：交易2，交易3999；执行updatesetcell=456wherecell=55555555555；持有锁（holdsthelock），记录锁（recordlocks），主键索引（indexprimary），互斥锁（lock_modeX），物理记录，asc55555555555；等待这个锁被授予，记录锁，主键索引（indexprimary），互斥量（lock_modeX），物理记录（physicalrecord），asc11111111111；事务2回滚（我们回滚事务2）；通过showengineinnodbstatus；可以看到事务和锁之间有很多信息，对分析问题很有帮助，可以说明一些问题，但是还有两个疑惑：（1）事务1为什么要拿55555555555的锁？画外音：这正是交易1被阻止的原因。(2)为什么事务2要拿11111111111的锁呢？死锁的出现说明此时事务1真正占用了11111111111的锁。一个事务占555，抢111，循环嵌套会造成死锁。工具二：explain为了进一步查找原因，可以使用explain查看导致死锁的语句的执行计划。explainupdatesetcell=456wherecell=55555555555；(1)select_type:SIMPLE这是一个简单类型的SQL语句，没有子查询和UNION。(2)type：索引访问类型，即用于查找所需数据的遍历方式，可能的方式有：ALL(FullTableScan)：全表扫描；index：按索引全表扫描；range:***whereClause范围索引扫描；ref/eq_ref：非唯一索引/唯一索引单值扫描；const/system：常量扫描；NULL：不需要访问表；以上几种扫描方式，ALL最慢，逐渐变快，NULL最快。疑点1：明明cell字段有uniq索引，为什么还要用PK索引进行全表扫描？(3)possible_keys：在哪个索引中可以NULL查找记录。(4)key：PRIMARY实际使用的是索引。画外音：PK全表扫描。(5)ref：哪些列为NULL，或者常量，用于在索引上查找值。疑点2：where条件中的查询条件55555555555本应该作为索引上检索到的值？(6)rows:5找到需要的记录，估计需要读取的行数。疑惑3：明明修改的是5，为什么第一个事务插入的初始化的1、2、3、4，第二个事务插入的5都读出来了？应该不是全表扫描啊。通过explain，基本可以判断：updatesetcell=456wherecell=55555555555；它并没有像我们预期的那样使用单元格索引进行查询，而是使用PK索引进行了全表扫描。仔细看看：createtablet(idint(20)primarykeyAUTO_INCREMENT,cellvarchar(20)unique)engine=innodb;创建表格时，单元格定义为字符串类型。更新时，updatesetcell=456wherecell=55555555555；使用整数类型。类型转换会引起全表扫描，发生锁升级，所有记录都会被锁住。添加引号，通过explain再次验证：explainupdatesetcell='456'wherecell='55555555555';果然猜想得到证实：type:range，变成了索引的字符串比较，范围扫描；possible_keys：单元格，通过单元格索引找到记录；key：cell，使用实际的cell索引；ref:const，常量'555'用于比较；rows:1，预估读取行数为1；一切都可以在这里解释。总结就本例而言：需要注意字符串和整数之间的强制类型转换。有时少一个引号会将行锁升级为表锁。死锁是MySQL中一个非常难调试的问题。常见的思路和方法是：通过多个终端模拟并发事务，重现死锁；通过showengineinnodbstatus；您可以查看交易和锁定信息；通过explain可以查看执行计划；想法比结论更重要，希望大家有所收获。【本文为专栏作者《58神剑》原创稿件，转载请联系原作者】点此阅读更多该作者好文
