MySQL 5.6.26 高並發死鎖

表結構：

CREATE TABLE `extend_0` ( 
`id` bigint(20) NOT NULL, 
`code` varchar(30) NOT NULL, 
`data_key` varchar(50) NOT NULL, 
`data_value` varchar(200) NOT NULL, 
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`yn` tinyint(3) NOT NULL DEFAULT '1', 
PRIMARY KEY (`id`,`create_time`), 
UNIQUE KEY `idx_unq_code_data_key` (`code`,`data_key`,`create_time`) USING BTREE, 
KEY `idx_code` (`code`) USING BTREE 
) ENGINE=InnoDB;

當兩個事務同時刪除和插入相同的數據時，有時會發生死鎖。tx_isolation 是 RR。

例如：

DELETE FROM extend_0 WHERE code = '00226915986' AND yn = 1; 

INSERT INTO extend_0 (id, code, data_key, data_value, create_time) VALUES (1133296779049299970, '00226915986', 'consignWare', 'food', '2019-05-28 16:59:42.418'), (1133296779049299971, '00226915986', 'productType', '0001,0006', '2019-05-28 16:59:42.418'); 

死鎖日誌：

------------------------------------------------------------------------------ 
2019-05-30 14:48:07 0x7fbb7872c700 
*** (1) TRANSACTION: 
TRANSACTION 125554670, ACTIVE 0 sec starting index read 
mysql tables in use 1, locked 1 
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) 
MySQL thread id 5148727, OS thread handle 140443189679872, query id 6111057236 192.168.162.16 waybill updating 
DELETE FROM extend_0 WHERE code = '00226915986' AND yn = 1 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_code_data_key of table `waybill_0`.`extend_0` trx id 125554670 lock_mode X waiting 
Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 
0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;; 
1: len 11; hex 636f6e7369676e57617265; asc consignWare;; 
2: len 4; hex 5cecf87e; asc \ ~;; 
3: len 8; hex 911d8ce2e1ddf000; asc ;; 

*** (2) TRANSACTION: 
TRANSACTION 125554668, ACTIVE 0 sec inserting 
mysql tables in use 1, locked 1 
5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3 
MySQL thread id 5148728, OS thread handle 140443156399872, query id 6111057237 192.168.162.16 waybill update 
INSERT INTO extend_0 (id, code, data_key, data_value, create_time) VALUES (i-1, '00226915986', 'consignWare', 'food', '2019-05-28 16:59:42.418'), (i, '00226915986', 'productType', '0001,0006', '2019-05-28 16:59:42.418') 
*** (2) HOLDS THE LOCK(S): 
RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_code_data_key of table `waybill_0`.`extend_0` trx id 125554668 lock_mode X 
Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 
0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;; 
1: len 11; hex 636f6e7369676e57617265; asc consignWare;; 
2: len 4; hex 5cecf87e; asc \ ~;; 
3: len 8; hex 911d8ce2e1ddf000; asc ;; 

Record lock, heap no 287 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 
0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;; 
1: len 11; hex 70726f6475637454797065; asc productType;; 
2: len 4; hex 5cecf87e; asc \ ~;; 
3: len 8; hex 911d8ce2e1ddf020; asc ;; 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_waybill_code_data_key of table `waybill_0`.`extend_0` trx id 125554668 lock_mode X locks gap before rec insert intention waiting 
Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 
0: len 15; hex 4a4456433030323236393135393836; asc JDVC00226915986;; 
1: len 11; hex 636f6e7369676e57617265; asc consignWare;; 
2: len 4; hex 5cecf87e; asc \ ~;; 
3: len 8; hex 911d8ce2e1ddf000; asc ;; 

*** WE ROLL BACK TRANSACTION (1) 
------------------------------------------------------------------------------ 

transaction2 已經有 lock_mode X。為什麼會發生“lock_mode X locks gap before rec insert intent waiting”？

但是當我們一個一個地執行事務時，不會發生死鎖。例如：

step1: tx1 begin; delete.....;
step2: tx2 begin; delete.....;
step3: tx1 insert....;
step4: tx2 insert....;
step5: tx1 commit;

只有在高並發時才會發生死鎖；死鎖看起來是由 tx1 的刪除和 tx2 的插入並發引起的。

您正在刪除與插入完全相同的記錄 (by code)。這就是伺服器死鎖的原因，因為現在 mysql 有辦法知道要採取什麼行動。您需要根據業務案例使用適當的操作在應用程序中處理此錯誤。

沒有什麼魔法可以避免這種情況。指數code已經上線

這個問題看起來與我的變體非常相似。據我了解，刪除語句中的全掃描問題。您沒有嘗試對錶進行全掃描的索引和刪除語句，全掃描對錶中的所有行應用鎖，即使是對插入中未送出的行也是如此。

MySQL/innodb 試圖從並行事務中鎖定未送出的行，結果死鎖

嘗試在程式碼和 yn 文件上創建索引。索引可以減少死鎖事件。確保刪除使用索引而不是全掃描。

引用自：https://dba.stackexchange.com/questions/239516