說明

線上某業務最近經常會出現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、儘量減少完成事務的時間

 
最終結論

         個（或以上）事務對相同的表進行insert操作，如果insert對應的欄位上有uniq key約束並且第一個事務rollback了，那其中一個將返回死鎖錯誤資訊。

參考連結：

 

http://songuooo.com/2015/1/7/deadlock-detected-on-concurrent-insert