共計 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 行業資訊頻道。
