前几天，网上出现了一个数据库死锁问题。这个问题查了很久。锁机制有了更深的理解。本文总结了这次死锁排查的全过程，分析了死锁产生的原因和解决办法。希望能为大家提供死锁的排查和解决思路。本文涉及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；事务隔离级别设置方法（只对当前会话生效）：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='fundflow';数据库有3个索引，1个聚簇索引（主键索引）,2非聚集索引（非主键中指数）指数。聚集索引：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_transNo3，死锁日志当数据库发生死锁时，可以通过获取死锁日志以下命令：showengineinnodbstatusdeadlockoccurred，查看***时间的死锁日志，死锁日志内容如下：Transactionsdeadlockdetected,dumpingdetailedinformation.2019-03-19T21:44:23.516263+08:005877341[Note]InnoDB：***（1）交易：交易173268495，ACTIVE0secfetchingrowsmysqltables，locked1LOCKWAIT304lockstruct（s），heapsize41168，6rowlock（s），undologentries1MySQLthreadid5877358，OSthreadhandle47356539049728，queryid55797018111.1811.183.24fintating_uptant_uptantdins.24ate`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[注]InnoDB:***(1)HOLDSTHELOCK):RECORDLOCKSspaceid173pageno13726nbits248indexidx_seller_transNooftable`xxx`.`fund_transfer_stream`trxid173268495lock_modeXlocksrecbutnotgapRecordlock,heapno168PHYSICALRECORD:n_fields3;compactformat;infobits02019-03-19T21:44:23.516565+08:005877341[Note]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_id')AND=1'1318')AND2)AND(`fund_transfer_order_no`='99010015000805619031957477256'))2019-03-19T21:44:23.517855+08:005877341[Note]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)简单解读死锁日志，可以得到如下信息：1.导致死锁的两条SQL语句为：update`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`='99010015000805619031958363857'))2、事务1持有索引idx_seller_transNo的锁，等待获取PRIMARY的锁3、事务2持有PRIMARY的锁，等待获取idx_seller_transNo的锁。4、事务1和事务2之间循环等待导致死锁。5、事务1和事务2目前持有的锁是：lock_modeXlocksrecbutnotgap。两个事务都给记录加X锁，NoGaplocks，即锁定当前记录（RecordLock），不加Gap锁。X锁：独占锁，又称写锁。如果事务T给数据对象A加了一个X锁，那么事务T就可以读取A或者修改A，其他事务不能给A加任何锁，直到T释放对A的锁。这保证了其他事务不能再读取和修改A直到T释放对A的锁。与之对应的是S锁：共享锁，也称为读锁，如果事务T给数据对象A加了S锁，事务T可以读取A但不能修改A，其他事务只能给A加S锁，而不是加X锁，直到T释放A上的S锁。这样可以保证其他事务可以读取A，但不能对A做任何修改，直到T释放A上的S锁。间隙锁：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的作用是防止同一个事务的两次当前读产生幻读。Next-KeyLock：1+2，锁定一个范围，锁定记录本身。对于行查询，采用这种方式，主要目的是解决幻读问题。4、故障排除根据我们目前已知的数据库相关信息和死锁日志，基本上可以做出一些简单的判断。首先，这个死锁肯定与Gaplock和Next-KeyLock无关。因为我们的数据库隔离级别是RC（READ-COMMITED），这个隔离级别是不会加Gap锁的。之前的死锁日志也提到了这一点。那么，是时候翻代码了，看看我们代码中的事务是如何完成的。核心代码和SQL如下：@Transactional(rollbackFor=Exception.class)publicintdoProcessing(StringsellerId,Longid,StringfundTransferOrderNo){fundTreansferStreamDAO.updateFundStreamId(sellerId,id,fundTransferOrderNo);returnfundTreansferStreamDAO.updateStatus(sellerId,fundCEOrderING)"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索引，通过执行计划，我们发现nd那个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字段的值：99010015000805619031958363857和99010015000805619031957477256前20位相同。那么为什么相同的fund_transfer_order_no前20位会导致死锁呢？5.加锁原理下面我们就以这个案例来看看MySql数据库的加锁原理是怎样的，以及本文死锁的背后究竟发生了什么。我们在数据库上模拟一个死锁场景，执行顺序如下：我们知道在MySQL中，行级锁不是直接锁定记录，而是锁定索引。索引分为主键索引和非主键索引。如果一条SQL语句操作了主键索引，MySQL会锁住主键索引；如果一条语句操作了非主键索引，MySQL会先锁定非主键索引，然后再锁定关联的主键索引。主键索引的叶子节点存储整行数据。在InnoDB中，主键索引也被称为聚集索引（clusteredindex）。非主键索引的叶子节点的内容就是主键的值。在InnoDB中，非主键索引也称为非聚集索引（二级索引）。所以，本文例子中涉及的索引结构（索引是B+树，简化成表）如图：交易。死锁的关键在于：两个（或多个）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)的锁，失败（死锁）；当事务使用非主键索引作为update的where条件时，会先加锁非主键索引，然后查询非主键索引对应的主键索引有哪些，然后加锁这些主键索引。)6.解决方案至此，我们已经分析了导致死锁的根本原理和背后的原理。那么解决这个问题就不难了。可以从修改索引和修改代码（包括SQL语句）两方面着手。修改索引：只要我们修改前缀索引idx_seller_transNo中fund_transfer_order_no的前缀长度即可。比如改成50，可以避免死锁。但是改变idx_seller_transNo的前缀长度后，解决死锁的前提是在真正执行update语句的时候会使用fund_transfer_order_no这个索引。如果MySQL查询优化器经过成本分析后决定使用索引KEYidx_seller(seller_id)，仍然会出现死锁问题。原理与本文类似。所以，根本的解决办法就是改代码：所有的更新都是通过主键ID来进行的。在同一个事务中，避免多个更新语句修改同一条记录。七、总结与思考死锁发生后的一周内，我几乎每天都抽空研究一下。问题定位早，也有修改方案，但一直没有弄清楚原理。我来来回回做了很多推论和假设，一次又一次被推翻。最后还是要靠实践来验证自己的想法。于是自己在本地安装了数据库，做了一些实际测试，实时查看了数据库的锁情况。显示引擎innodb状态；可以查看锁定情况。终于知道原因了。几点感想：遇到问题千万不要乱猜！！！自己复现问题，再分析。不要忽视上下文！！！一开始只关注死锁日志，一直忽略代码中的事务实际上执行了另外一条SQL语句（updateFundStreamId）。理论知识再多，关键时刻也未必想得通！！！坑都是自己给自己埋的！！！参考资料：http://hedengcheng.com/?p=771https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html《MySql实战45讲》https://www.hollishuang.com/archives/914
