死锁分析和解决方案文档

服务中发生死锁，并且死锁检测时间很长. 31s之后，可以回退死锁检测事务. 在此期间，相同的请求继续出现，导致死锁变得越来越复杂，并且所有服务器端线程池的线程都在等待锁，最终导致服务器端线程池没有空闲线程并拒绝服务.

InnoDB首先记录

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-16 15:38:17 0x7f121830f700
*** (1) TRANSACTION:
TRANSACTION 161027737922, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 88 lock struct(s), heap size 24784, 1412 row lock(s), undo log entries 2
MySQL thread id 42610991, OS thread handle 139701841643264, query id 40446554160 10.7.23.224 promotio_e0a1 updating
/*id:6d7cc9a7*/DELETE FROM `campaignmockqueue` WHERE `campaignid`=52327710 and `addtime` <= 1557992297
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1975 page no 5280 n bits 1272 index idx_campid of table `promotion`.`campaignmockqueue` trx id 161027737922 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 161027729748, ACTIVE 31 sec inserting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 14
MySQL thread id 42610752, OS thread handle 139715692001024, query id 40446554260 10.43.174.209 promotio_e0a1 update
/*id:dfdb66*/INSERT INTO `campaignmockqueue`(
           `campaignid`, `addtime`
        )
        VALUES
          
            (52327709, 1557992297)
         , 
            (52327709, 1559383140)
         , 
            (52327709, 1557992296)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1975 page no 5280 n bits 1272 index idx_campid of table `promotion`.`campaignmockqueue` trx id 161027729748 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1975 page no 5271 n bits 1272 index idx_campid of table `promotion`.`campaignmockqueue` trx id 161027729748 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

如果您不了解日志，可以参考这篇文章

让我们看一下事务1和事务2持有哪些锁以及正在等待什么锁. 由于从事务1的角度打印日志，所以我们只能看到事务2拥有lock_mode X锁定rec而不是间隙锁，等待lock_mode X锁定间隙，然后rec插入意图锁定.

lock_mode X锁定rec，但不间隙，它是只锁定单个记录的写记录锁定.

lock_mode X在rec插入意图是插入意图锁定之前锁定间隙. 目的是锁定相应的间隙（不包括记录本身）.

从事务2的锁信息中，我们可以推断事务1的锁持有信息，因此我们有下图.

事务1当前具有一个由间隙A和记录X组成的密钥锁. 现在等待的是间隙b的锁.

DELETE FROM `campaignmockqueue` WHERE `campaignid`=52327710 and `addtime` <= 1557992297

事务2当前持有记录Y的X锁死锁分析，间隙D的意图锁和间隙C的意图锁. 现在等待的是间隙e的意图锁（也可能是记录Y的记录）锁）.

INSERT INTO `campaignmockqueue`(`campaignid`, `addtime` )VALUES (52327709, 1557992297) , (52327709, 1559383140), (52327709, 1557992296)

由于next-key锁和insert intent锁是互斥的，事务1正在等待事务2释放C，Y，D；事务2正在等待事务1释放A. 这似乎适合InnoDB中的日志.

我认为，因为下一键锁和Gap锁不是一种锁，所以必须存在时差，并且这种时差仅在大量并发的情况下才会突出.

交易1交易2

将“插入”活动模拟队列记录Y

Delete FROM campaignmockqueue已获得A的间隙锁定，但尚未获得下一键锁定

插入广告活动模拟队列（campaignid，addtime）值（52327709，1557992297）死锁分析，（52327709，1559383140），（52327709，1557992296）（阻止）

从campaignmockqueue删除获取下一键锁定（阻止）

以上情况仅是推测，如果我们可以从INSERT语句的角度获取死锁日志. 我搜索了InnoDB日志，并获得了以下日志. 通过交叉分析，我们可以验证我们的猜想.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-16 03:02:36 0x7f11eebd8700
*** (1) TRANSACTION:
TRANSACTION 161022641521, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 42518313, OS thread handle 139715027216128, query id 40270385508 10.7.224.35 promotio_e0a1 update
/*id:dfdb66*/INSERT INTO `campaignmockqueue`(
`campaignid`, `addtime`
)
VALUES
(52247612, 1557946956)
, 
(52247612, 1557936000)
, 
(52247612, 1558022399)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1975 page no 4904 n bits 1264 index addtime of table `promotion`.`campaignmockqueue` trx id 161022641521 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 161022641523, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 42543917, OS thread handle 139714996569856, query id 40270385509 10.7.23.224 promotio_e0a1 updating
/*id:6d7cc9a7*/DELETE FROM `campaignmockqueue` WHERE `campaignid`=52247612 and `addtime` <= 1557946956
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1975 page no 4904 n bits 1264 index addtime of table `promotion`.`campaignmockqueue` trx id 161022641523 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1975 page no 4904 n bits 1264 index addtime of table `promotion`.`campaignmockqueue` trx id 161022641523 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (2)

DELETE FROM campaignmockqueue不使用idx_campid索引进行锁定，而是使用唯一的主键进行操作，而插入操作使用不同的索引来避免此问题.

mysql并发插入死锁问题-差距，插入意图锁冲突-hebaodan的博客-OSCHINA阅读MySQL源代码并查看INSERT锁定过程-aneasystone的博客Innodb死锁日志分段解释-如何读取死锁日志-Kui Yin＆ [MySQL DELETE删除语句锁定分析| 对于DBA

以上是本文的全部内容. 希望对大家的学习有所帮助. 我也希望每个人都能支持代码农场网络.

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-151543-1.html