并发insert操作导致的dead lock该怎么办

这期内容当中小编将会给大家带来有关并发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了，那其中一个将返回死锁错误信息。

上述就是小编为大家分享的并发insert操作导致的dead lock该怎么办了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注编程网数据库频道。