共计 7674 个字符，预计需要花费 20 分钟才能阅读完成。
这期内容当中丸趣 TV 小编将会给大家带来有关并发 insert 操作导致的 dead lock 该怎么办，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
说明
线上某业务最近经常会出现 dead lock，相关信息如下：
2016-06-15 20:28:25 7f72c0043700InnoDB: transactions deadlock detected, dumping detailed information.
2016-06-15 20:28:25 7f72c0043700
*** (1) TRANSACTION:
TRANSACTION 151506716, ACTIVE 30 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 1467337, OS thread handle 0x7f72a84d6700, query id 308125831 IP 地址 1 fold-sys update
insert into t (a,b,c, addtime)
values
(63, 27451092,120609109,now())
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 46 page no 693076 n bits 664 index `unq_fk_key` of table `dbname`.`t` trx id 151506716 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 151506715, ACTIVE 30 sec inserting, thread declared inside InnoDB 1
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 1477334, OS thread handle 0x7f72c0043700, query id 308125813 IP 地址 2 fold-sys update
insert into t (a,b,c, addtime)
values
(63, 27451092,120609109,now())
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 46 page no 693076 n bits 664 index `unq_fk_folder_fk_video_seq` of table `folder`.`t_mapping_folder_video` trx id 151506715 lock mode S locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 46 page no 693076 n bits 664 index`unq_fk_key` of table `dbname`.`t` trx id 151506715 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
初步分析
1、122 和 120 在同一时刻发起了相同的 insert 操作 数据一模一样 而 a,b,c 刚好是 uniq key
2、咱们是 RC 级别 出现了 GAP lock 这个有点疑问？查阅了下文档
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable theinnodb_locks_unsafe_for_binlog system variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
设置 innodb_locks_unsafe_for_binlog 或者 RC 级别来关闭 gap
后面部分 可以理解为 RC 级别下的 外键和重复检查的时候也会产生 GAP 呢
重现此 deadlock
5.5.19-55-log Percona Server (GPL), Release rel24.0, Revision 204
tx_isolation=READ-COMMITTED
innodb_locks_unsafe_for_binlog=OFF
创建实验表
CREATE TABLE `deadlock` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`a` smallint(5) unsigned NOT NULL DEFAULT 0 ,
`b` int(11) NOT NULL DEFAULT 0 ,
`c` int(11) NOT NULL DEFAULT 0 ,
`d` datetime NOT NULL DEFAULT 0000-00-00 00:00:00 ,
PRIMARY KEY (`id`),
UNIQUE KEY `unq_b_c_a` (`b`,`c`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
事务 T1
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql insert into deadlock(a,b,c) values(1,2,3);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
事务和锁
# 此时表 deadlock 上被加了一把意向排它锁（IX）
—TRANSACTION 4F23D, ACTIVE 20 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root
TABLE LOCK table `yujx`.`deadlock` trx id 4F236 lock mode IX
事务 T2
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql insert into deadlock(a,b,c) select 1,2,3;
# 此处会处于等待
事务和锁
—TRANSACTION 4F23E, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x408d8940, query id 243 localhost root update
insert into deadlock(a,b,c) values(1,2,3)
#事务 T2 对表 deadlock 加了一把意向排它锁（IX），而对 unq_b_c_a 唯一约束检查时需要获取对应的共享锁，但是对应记录被 T1 加了 X 锁，此处等待获取 S 锁（# 注意，insert 进行的是当前读，所以读会被 X 锁阻塞。如果是快照读的话，不需要等待 X 锁）
——- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting
——————
TABLE LOCK table `yujx`.`deadlock` trx id 4F237 lock mode IX
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting
—TRANSACTION 4F23D, ACTIVE 37 sec
# 事务 T1 对表 deadlock 加了一把意向排它锁（IX）和记录锁（X）
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root
TABLE LOCK table `yujx`.`deadlock` trx id 4F236 lock mode IX
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F236 lock_mode X locks rec but not gap
—————————-
事务 T3
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql insert into deadlock(a,b,c) values(1,2,3);
# 此处会处于等待
事务和锁
—TRANSACTION 4F23F, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 0x41976940, query id 245 localhost root update
insert into deadlock(a,b,c) values(1,2,3)
——- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
# 同样，事务 T3 与上面的事务 T2 的事务和锁等待一样，事务 T1 造成了 T2 和 T3 的等待
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F238 lock mode S waiting
——————
TABLE LOCK table `yujx`.`deadlock` trx id 4F238 lock mode IX
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F238 lock mode S waiting
—TRANSACTION 4F23E, ACTIVE 31 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x408d8940, query id 243 localhost root update
insert into deadlock(a,b,c) values(1,2,3)
——- TRX HAS BEEN WAITING 31 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting
——————
TABLE LOCK table `yujx`.`deadlock` trx id 4F237 lock mode IX
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F237 lock mode S waiting
—TRANSACTION 4F23D, ACTIVE 65 sec
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 0x41441940, query id 237 localhost root
TABLE LOCK table `yujx`.`deadlock` trx id 4F236 lock mode IX
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F236 lock_mode X locks rec but not gap
事务 T1 进行 rollback
# 事务 T1 进行 rollback;
mysql rollback;
Query OK, 0 rows affected (0.00 sec)
# 事务 T2 的 insert 成功
mysql insert into deadlock(a,b,c) values(1,2,3);
Query OK, 1 row affected (10.30 sec)
# 事务 T3 返回 deadlock 错误
mysql insert into deadlock(a,b,c) values(1,2,3);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
DEADLOCK 信息
————————
LATEST DETECTED DEADLOCK
————————
160620 11:38:14
*** (1) TRANSACTION:
TRANSACTION 4F23E, ACTIVE 48 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x408d8940, query id 297 localhost root update
insert into deadlock(a,b,c) values(1,2,3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F23E lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 4F23F, ACTIVE 30 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 0x41976940, query id 300 localhost root update
insert into deadlock(a,b,c) values(1,2,3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F23F lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 101724 n bits 72 index `unq_b_c_a` of table `yujx`.`deadlock` trx id 4F23F lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
如上，只能看到事务 T2 和事务 T3 最终导致了 deadlock；T2 等待获取 unq_b_c_a 唯一 key 对应的记录锁（X lock）,T3 在 `unq_b_c_a` 对应的记录上持有 S 锁，并且 T3 也在等待获取对应的 X 锁。最终 T3 被 ROLL BACK 了，并且发回了 DEAD LOCK 的提示信息
综上
1、SHOW ENGINE INNODB STATUS\G 看到的 DEADLOCK 相关信息，只会返回最后的 2 个事务的信息，而其实有可能有更多的事务才最终导致的死锁
2、当有 3 个（或以上）事务对相同的表进行 insert 操作，如果 insert 对应的字段上有 uniq key 约束并且第一个事务 rollback 了，那其中一个将返回死锁错误信息。
3、死锁的原因
l T1 获得 X 锁并 insert 成功
l T2 试图 insert, 检查重复键需要获得 S 锁, 但试图获得 S 锁失败, 加入等待队列, 等待 T1
l T3 试图 insert, 检查重复键需要获得 S 锁, 但试图获得 S 锁失败, 加入等待队列, 等待 T1
l T1 rollback, T1 释放锁, 此后 T2, T3 获得 S 锁成功, 检查 duplicate-key, 之后 INSERT 试图获得 X 锁, 但 T2, T3 都已经获得 S 锁, 导致 T2, T3 死锁
4、避免此 DEADLOCK；我们都知道死锁的问题通常都是业务处理的逻辑造成的，既然是 uniq key，同时多台不同服务器上的相同程序对其 insert 一模一样的 value，这本身逻辑就不太完美。故解决此问题：
a、保证业务程序别再同一时间点并发的插入相同的值到相同的 uniq key 的表中
b、上述实验可知，是由于第一个事务 rollback 了才产生的 deadlock，查明 rollback 的原因
c、尽量减少完成事务的时间
最终结论
当有 3 个（或以上）事务对相同的表进行 insert 操作，如果 insert 对应的字段上有 uniq key 约束并且第一个事务 rollback 了，那其中一个将返回死锁错误信息。
上述就是丸趣 TV 小编为大家分享的并发 insert 操作导致的 dead lock 该怎么办了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。
