前几天，网上出现了一个数据库死锁的问题。这个问题已经研究了很长时间。在这个过程中，我对数据库锁机制有了更深的理解。本文总结了这次死锁排查的全过程，分析了死锁产生的原因和解决办法。希望能为大家提供死锁的排查和解决思路。本文涉及MySQL执行引擎、数据库隔离级别、InnoDB锁机制、索引、数据库事务等多个领域的知识，前车之鉴，后路之鉴，希望读者有所收获。现象一天晚上，同事在发帖，突然网上出现大量告警，其中很多是关于数据库死锁的。报警信息如下：\n###SQL:updatefund_transfer_streamsetgmt_modified=now(),state=?wherefund_transfer_order_no=?andseller_id=?andstate='NEW'通过报警，我们基本可以定位到发生死锁的数据库和数据库表，先介绍一下数据库本文案例涉及的相关信息背景我们使用的数据库是MySQL5.7，引擎是InnoDB，事务隔离级别是READ-COMMITED数据库版本查询方法：selectversion()；引擎查询方法：showcreatetablefund_transfer_stream；存储引擎信息会显示在建表语句中，如：ENGINE=InnoDB。事务隔离级别查询方式：select@@tx_isolation；事务隔离级别设置方法（只对当前Session有效）：setsessiontransactionisolationlevelreadcommitted；PS：注意，如果数据库分库，以上SQL语句需要在单个数据库上执行，不要在逻辑库中执行。发生死锁的表结构和索引（隐藏一些不相关的字段和索引）：CREATETABLE`fund_transfer_stream`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'主键',`gmt_create`datetimeNOTNULLCOMMENT'创建时间',`gmt_modified`datetimeNOTNULLCOMMENT'修改时间',`pay_scene_name`varchar(256)NOTNULLCOMMENT'支付场景名称',`pay_scene_version`varchar(256)DEFAULTNULLCOMMENT'支付场景版本',`identifier`varchar(256)NOTNULLCOMMENT'唯一标识',`seller_id`varchar(64)NOTNULLCOMMENT'SellerId',`state`varchar(64)DEFAULTNULLCOMMENT'status',`fund_transfer_order_no`varchar(256)DEFAULTNULLCOMMENT'资金平台返回状态',PRIMARYKEY(`id`),UNIQUEKEY`uk_scene_identifier`(KEY`idx_seller`(`seller_id`),KEY`idx_seller_transNo`(`seller_id`,`fund_transfer_order_no`(20)))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='FundsFlow';这个数据库有3个索引，1个聚簇索引（主键）索引），2个非聚集索引（非p主键索引）。聚集索引：PRIMARYKEY(`id`)非聚集索引：KEY`idx_seller`(`seller_id`),KEY`idx_seller_transNo`(`seller_id`,`fund_transfer_order_no`(20))以上两个索引，其实idx_seller_transNo都有之前讲到idx_seller的时候，由于历史原因，表分为seller_id表，所以先有idx_seller，后面没有idx_seller_trans。死锁日志当数据库发生死锁时，可以使用如下命令获取死锁日志：showengineinnodbstatusdeadlockoccurs，查看***时间的死锁日志，死锁日志内容如下：dumpingdetailedinformation.2019-03-19T21:44:23.516263+08:005877341[Note]InnoDB:***(1)TRANSACTION:TRANSACTION173268495,ACTIVE0secfetchingrowsmysqltablesinuse1,locked1LOCKWAIT304lockstruct(s),heapsize41168,6rowlock(s),undologentries1MySQLthreadid5877358,OSthreadhandle47356539049728,queryid55797018111.183.244.150fin_instant_appupdatingupdate`fund_transfer_stream`set`gmt_modified`=NOW(),`state`='PROCESSING'where((`state`='NEW')AND(`seller_id`='38921111')AND(`fund_transfer_order_no`='99010015000805619031958363857'))2019-03-19T21:44:23.516321+08:005877341[Note]InnoDB:***(1)HOLDSTHELOCK(S):RECORDLOCKSspaceid173pageno13726nbits248indexidx_seller_transNooftable`xxx`.`fund_transfer_stream`trxid173268495lock_modeXlocksrecbutnotgapRecordlock,heapno168PHYSICALRECORD:n_fields3;compactformat;infobits02019-03-19T21:44:23.516565+08:005877341[注意]InnoDB:***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid173pageno12416nbits128indexPRIMARYoftable`xxx`.`fund_transfer_stream`trxid173268495lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno56PHYSICALRECORD:n_fields17;compactformat;infobits02019-03-19T21:44:23.517793+08:005877341[Note]InnoDB:***(2)TRANSACTION:TRANSACTION173268500,ACTIVE0secfetchingrows,threaddeclaredinsideInnoDB81mysqltablesinuse1,locked1302lockstruct(s),heapsize41168,2rowlock(s),undologentries1MySQLthreadid5877341,OSthreadhandle47362313119488,queryid55797018911.131.81.107fin_instant_appupdatingupdate`fund_transfer_stream_0056`set`gmt_modified`=NOW(),`state`='PROCESSING'where((`state`='NEW')AND(`seller_id`='38921111')AND(`fund_transfer_order_no`='99010015000805619031957477256'))2019-03-19T21:44:23.517855+08:005877341[注]InnoDB:***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid173pageno12416nbits128indexPRIMARYoftable`fin_instant_0003`.`fund_transfer_stream_0056`trxid173268500lock_modeXlocksrecbutnotgapRecordlock,heapno56PHYSICALRECORD:n_fields17;compactformat;infobits02019-03-19T21:44:23.519053+08:005877341[Note]InnoDB:***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid173pageno13726nbits248indexidx_seller_transNooftable`fin_instant_0003`.`fund_transfer_stream_0056`trxid173268500lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno168PHYSICALRECORD:n_fields3;compactformat;infobits02019-03-19T21:44:23.519297+08:005877341[注意]InnoDB:***WEROLLBACKTRANSACTION(2)简单阅读死锁日志后，可以得到如下信息：①导致死锁的两条SQL语句是:更新`fund_transfer_stream_0056`set`gmt_modified`=NOW(),`state`='PROCESSING'where((`state`='NEW')AND(`seller_id`='38921111')AND(`fund_transfer_order_no`='99010015000805619031957477256'))更新`fund_transfer_stream_0056`set`gmt_modified`=NOW(),`state`='PROCESSING'where((`state`='NEW')AND(`seller_id`='38921111')AND(`fund_transfer_order_no`='9901001500080561903155783638'))②事务1，持有索引idx_seller_transNo的锁，等待获取PRIMARY的锁③事务2持有PRIMARY的锁，等待获取idx_seller_transNo的锁。④事务1和事务2循环等待导致死锁。⑤事务1和事务2当前持有的锁都是：lock_modeXlocksrecbutnotgap。两个事务对记录加X锁和NoGap锁，即对当前行的记录加锁（RecordLock），不加间隙锁。X锁：独占锁，又称写锁。如果事务T给数据对象A加了X锁，事务T就可以读取A或者修改A，其他事务不能再给A加任何锁，直到T释放对A的锁。这样就保证了其他事务不能再读取和修改AuntilT释放了A上的锁。与之对应的是S锁：共享锁，又称读锁，如果事务T给数据对象A加了S锁，事务T可以读取A但不能修改A，其他事务可以只给A加S锁，而不是加X锁，直到T释放A上的S锁。这保证其他事务可以读取A，但不能对A做任何修改，直到T释放A上的S锁。间隙锁：间隙锁，锁定一个范围，但不包括记录本身。Gap锁的目的是防止同一个事务的两次当前读产生幻读。Next-KeyLock：1+2，锁定一个范围，锁定记录本身。对于行查询，采用这种方式，主要目的是解决幻读问题。排查问题根据我们目前已知的数据库相关信息和死锁日志，基本上可以做出一些简单的判断。首先，这个死锁肯定与Gaplock和Next-KeyLock无关。因为我们的数据库隔离级别是RC（READ-COMMITED），这个隔离级别是不会加Gap锁的。之前的死锁日志也提到了这一点。那么，是时候翻代码了，看看我们代码中的事务是如何完成的。核心代码和SQL如下：@Transactional(rollbackFor=Exception.class)publicintdoProcessing(StringsellerId,Longid,StringfundTransferOrderNo){fundTreansferStreamDAO.updateFundStreamId(sellerId,id,fundTransferOrderNo);returnfundTreansferStreamDAO.updateStatus(sellerCEORING,Nofund)"PROSS"的代码的目的是修改同一记录的两个不同字段，updateFundStreamIdSQL:updatefund_transfer_streamsetgmt_modified=now(),fund_transfer_order_no=#{fundTransferOrderNo}whereid=#{id}andseller_id=#{sellerId}updateStatusSQL:updatefund_transfer_streamsetgmt_modified=now(,state=#{state}wherefund_transfer_order_no=#{fundTransferOrderNo}andseller_id=#{sellerId}andstate='NEW'可以看出我们在同一个事务中执行了两条Update语句，这里是接下来两条SQL的执行计划：执行updateFundStreamId时使用PRIMARY索引，执行updateStatus时使用idx_seller_transNo索引，通过执行计划，我们发现发现updateStatus实际上有两个索引可用，执行时实际用到的是idx_seller_transNo索引。这是因为MySQL查询优化器是一种基于成本的查询方法。因此，在查询过程中，最重要的部分是根据查询SQL语句和各种索引计算查询所需的成本，从而选择最佳的索引方式生成查询计划。我们的查询执行计划是在死锁发生之后制定的，之后查询的执行计划不一定和死锁时刻的索引使用情况一样。但是我们也可以结合死锁日志定位到上面两条SQL语句执行时使用的索引。即执行updateFundStreamId时使用PRIMARY索引，执行updateStatus时使用idx_seller_transNo索引。有了以上已知信息，我们就可以开始排查死锁的原因和背后的原理了。通过分析死锁日志，结合我们的代码和建表语句，发现主要问题出在我们的idx_seller_transNo索引：KEY`idx_seller_transNo`(`seller_id`,`fund_transfer_order_no`(20))索引创建语句，我们使用前缀索引。为了节省索引空间，提高索引效率，我们只选取fund_transfer_order_no字段的前20位作为索引值。因为fund_transfer_order_no只是一个普通索引，并不是唯一索引。又因为在特殊情况下，同一用户的两个fund_transfer_order_no的前20位是相同的。这会导致两个具有相同索引值的不同记录（因为seller_id和fund_transfer_order_no(20)相同）。就像本文的例子，两条死锁记录的fund_transfer_order_no字段的前20位相同：9901001500080561903195836385799010015000805619031957477256那么为什么fund_transfer_order_no的前20位相同会导致我们死锁呢？以这个案例来看看MySQL数据库的加锁原理是怎样的，以及本文死锁的背后究竟发生了什么。我们在数据库上模拟一个死锁场景，执行顺序如下：我们知道在MySQL中，行级锁不是直接锁定记录，而是锁定索引。索引分为主键索引和非主键索引：如果SQL语句操作了主键索引，MySQL会锁定主键索引。如果一条语句操作了非主键索引，MySQL会先锁定非主键索引，然后再锁定相关的主键索引。主键索引的叶子节点存储整行数据。在InnoDB中，主键索引也被称为聚集索引（ClusteredIndex）。非主键索引的叶子节点的内容就是主键的值。在InnoDB中，非主键索引也称为非聚集索引（SecondaryIndex）。因此，本文示例涉及的索引结构（索引为B+树，简化为表）如图所示：是否发生死锁与事务中SQL语句的条数无关。死锁的关键在于：两个（或多个）Session加锁的顺序不一致。那么我们来看一下上面例子中两个事务的加锁顺序：下图是分解图，每条SQL执行时的加锁情况：结合上面两张图，我们找到了造成死锁的原因：事务1执行update1，占用PRIMARY=1锁；事务2执行update1，占用PRIMARY=2锁；。事务1执行update2占用idx_seller_transNo=(3111095611,99010015000805619031)的锁，试图占用PRIMARY=2的锁失败（阻塞）。事务2执行update2，试图占用idx_seller_transNo=(3111095611,99010015000805619031)的锁，失败（死锁）。当事务以非主键索引为Where条件执行Update时，会先锁定非主键索引，然后查询非主键索引对应的主键索引，然后锁定这些主键索引.)解决方案至此，我们已经分析了导致死锁的根本原理和背后的原理。那么解决这个问题就不难了。可以从两方面着手，即：修改索引修改代码（包括SQL语句）修改索引：只要我们修改前缀索引idx_seller_transNo中fund_transfer_order_no的前缀长度即可。比如改成50可以避免死锁。但是改变idx_seller_transNo的前缀长度后，解决死锁的前提是在真正执行Update语句时会使用fund_transfer_order_no索引。如果MySQL查询优化器经过成本分析后决定使用索引KEYidx_seller(seller_id)，仍然会出现死锁问题。原理与本文类似。所以根本的解决办法就是改代码：所有的Update都是通过主键ID来进行的。在同一个事务中，避免多个Update语句修改同一条记录。总结与思考在死锁发生后的一周里，我几乎每天都抽空研究一下。问题定位早，也有修改方案，但一直没有弄清楚原理。我来来回回做了很多种推论和假设，一次又一次被推翻。最后还是要靠实践来验证自己的想法。于是自己在本地安装了数据库，做了一些实际测试，实时查看了数据库的锁情况。显示引擎innodb状态；可以查看锁定情况。终于知道原因了。几点感想：遇到问题千万不要乱猜！！！自己复现问题，再分析。不要忽视上下文！！！一开始只关注死锁日志，一直忽略代码中的事务实际上执行了另外一条SQL语句（updateFundStreamId）。理论知识再丰富，关键时刻也未必想得通！！！坑都是自己给自己埋的！！！
