记一次MySQL Insert导致的死锁

最新推荐文章于 2024-01-28 23:53:56 发布

weixin_34186931

最新推荐文章于 2024-01-28 23:53:56 发布

阅读量174

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3023401/blog/1592273

版权

2019独角兽企业重金招聘Python工程师标准>>>

前言：因为觉得这个案例很有意思，因为决定今天分享一下。昨晚在我收拾东西准备回家的时候，突然，收到线上业务报警，有大量的DeadLock产生，然后就开始打开服务器。。。（所有信息已经经过脱敏处理）

问题现象：

排查过程：

查看mysql 服务器当时的状态，是发现有很多的死锁信息的，如下所示：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-12-18 22:05:17 7f3550209700
*** (1) TRANSACTION:
TRANSACTION 27707529, ACTIVE 75 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 372227, OS thread handle 0x7f3520da8700, query id 48360426 xx update
INSERT INTO xxxx values(xxxxx)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 631 page no 59 n bits 440 index xxxx of table xxxx trx id 27707529 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 219 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 2c0d3e08bbab9816; asc , >     ;;
 1: len 8; hex 000000000000091b; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 27705830, ACTIVE 77 sec inserting
mysql tables in use 1, locked 1
53 lock struct(s), heap size 13864, 2 row lock(s), undo log entries 1
MySQL thread id 371954, OS thread handle 0x7f3550209700, query id 48352484 xxxx xxxx update
INSERT INTO xxxxx
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 631 page no 59 n bits 440 index xxxx of table xxxx trx id 27705830 lock mode S locks gap before rec
Record lock, heap no 219 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 2c0d3e08bbab9816; asc , >     ;;
 1: len 8; hex 000000000000091b; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 631 page no 59 n bits 440 index xxxx of table xxxx trx id 27705830 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 219 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 2c0d3e08bbab9816; asc , >     ;;
 1: len 8; hex 000000000000091b; asc         ;;

*** WE ROLL BACK TRANSACTION (1)

纳尼？insert产生的死锁？再仔细一看，insert居然产生了gap lock？T2怎么会在等待自己的锁？一条insert怎么会有两行被锁住？

先分析一下上面的这个监控信息吧，第一个事物等待的是一个带有gap的意向排他锁，第二个事物持有一个带有gap的共享锁，在等待一个带有gap的意向排他锁。好像根据上面的内容不能判断具体的产生这个问题的原因，然后我就着手开始分析mysql的现场监控日志。

------------
TRANSACTIONS
------------
---TRANSACTION 27711843, ACTIVE 190 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 781310, OS thread handle 0x7f2f3b2ca700, query id 48377233 xxxx xxxx update
INSERT INTO xxxx values xxxxxx
---TRANSACTION 27711842, ACTIVE 195 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s)
MySQL thread id 775682, OS thread handle 0x7f35206f5700, query id 48377231 xxxx xxxx update
INSERT INTO xxxx values xxxxx
---TRANSACTION 27711841, ACTIVE 195 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 775681, OS thread handle 0x7f3520a98700, query id 48377229 xxxx xxxx update
INSERT INTO xxxxx values xxxxx
---TRANSACTION 27711840, ACTIVE 200 sec rollback
ROLLING BACK 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 770360, OS thread handle 0x7f3521b0e700, query id 48377227 xxxx xxxx
Trx read view will not see trx with id >= 27711841, sees < 27705729
---TRANSACTION 27711839, ACTIVE 200 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 770258, OS thread handle 0x7f3522caa700, query id 48377224 xxxx  xxxx update
INSERT INTO xxxx values xxxxx

这里面可以很明显的看到几个信息：

1、有事物执行失败回滚了；

2、有大量不同的事物，在操作同一条记录信息；

3、有很多大事物。

我的猜测：

是有大量的不同的事物去同时操作同一行记录，导致的死锁

验证猜测：

同时执行上面的三个事物，由于其他原因第一个事物实行失败回滚，在mysql里面，rollback的时候，其实是执行了一个delete的操作，T2继承了T1的锁，因此锁队列应该是这样的：T1回滚->T2 S锁->T3 S锁->T2意向插入锁->T3意向插入锁。也就是T2在等待T3的S锁，T3又在等待T2的意向排查锁，T3的意向排他锁，又在等待自己的S锁，T3太烧脑，所以报Deadlock，回滚T3。

看下我们的监控输出

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-12-19 15:09:22 7f61206a0700
*** (1) TRANSACTION:
TRANSACTION 13155, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 0x7f61206d1700, query id 170 127.0.0.1 root update
insert into t1(id_card) values(7)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13 page no 4 n bits 80 index `uniq_card` of table `tom`.`t1` trx id 13155 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 13154, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 0x7f61206a0700, query id 169 127.0.0.1 root update
insert into t1(id_card) values(7)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 13 page no 4 n bits 80 index `uniq_card` of table `tom`.`t1` trx id 13154 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 4; hex 80000003; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13 page no 4 n bits 80 index `uniq_card` of table `tom`.`t1` trx id 13154 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 4; hex 80000003; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

OK，至此问题找到，我可以退下了。官网介绍就不贴了，可以点击下面的链接去看

官方网站相关介绍：https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

具体锁分析参考：http://mysqllover.com/?p=431