數據庫：MySQL

我有一個正在執行的應用程序，其中包含並發事務。我注意到有很多與死鎖有關的錯誤發生。

錯誤說

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction.

調查死鎖

我試圖從我的應用程序中讀取堆棧跟踪並從SHOW ENGINE INNODB STATUS. 我能夠在一個簡單的場景中重現死鎖，但不明白為什麼會發生死鎖。以下是詳細資訊：

DDL

CREATE TABLE `tg_users` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `tg_user_id` bigint(20) unsigned NOT NULL,
 `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `first_name` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `last_name` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `photo` bigint(20) unsigned DEFAULT NULL,
 `group_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0',
 `private_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0',
 `is_bot` enum('0','1') CHARACTER SET utf8 NOT NULL DEFAULT '0',
 `created_at` datetime NOT NULL,
 `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `tg_user_id` (`tg_user_id`),
 KEY `username` (`username`),
 KEY `first_name` (`first_name`),
 KEY `last_name` (`last_name`),
 KEY `group_msg_count` (`group_msg_count`),
 KEY `private_msg_count` (`private_msg_count`),
 KEY `created_at` (`created_at`),
 KEY `updated_at` (`updated_at`),
 KEY `photo` (`photo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

邏輯：

1. 根據 . 從數據庫中選擇使用者資訊tg_user_id。
2. 如果使用者尚未儲存在數據庫中，則將其插入。
3. 如果存在此類數據，則進行比較和更新。

在這種情況下，我專注於插入場景。

只是為了強調檢查可能很重要的事情：

1. tg_user_id是唯一鍵。
2. 連接 1 和連接 2tg_user_id的查詢不同（所以我認為行鎖不相關，因為它們使用不同的行）。

複製步驟：

• （步驟 1）打開 Connection 1 並選擇查詢。（唯一身份：341292662）

---

START TRANSACTION;
-- Query OK, 0 rows affected (0.00 sec)

SELECT 
`id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count` 
FROM `tg_users` WHERE `tg_user_id` = '341292662' FOR UPDATE;
-- Query OK, 0 rows affected (0.00 sec)

保持連接 1 打開，創建連接 2。

• （第 2 步）打開 Connection 2 並選擇查詢。（唯一身份：239302521）

---

START TRANSACTION;
-- Query OK, 0 rows affected (0.00 sec)

SELECT
`id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`
FROM `tg_users` WHERE `tg_user_id` = '239302521' FOR UPDATE;
-- Query OK, 0 rows affected (0.00 sec)

• （第 3 步）返回連接 1 並插入（為什麼會阻塞？）（uniq 身份：341292662）

---

INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('341292662', 'derido', 'Derido', 'Novelium', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`);
-- Now the insert query is blocking

• （第 4 步）返回連接 2 並插入（為什麼會出現死鎖？）（uniq 標識：239302521）

---

INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('239302521', 'tomorimo', 'Tomorimo', 'Avede', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`);
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

問題

1. 為什麼步驟 3 中的查詢會被阻塞？即使步驟 3 中的行不涉及連接 2（正在發出IX鎖）。
2. 為什麼會發生死鎖？
3. 我能做些什麼來防止死鎖？

SHOW ENGINE INNODB STATUS 的死鎖報告

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-08-03 22:35:26 0x9e08eb40
*** (1) TRANSACTION:
TRANSACTION 293921, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1080, 2 row lock(s), undo log entries 1
MySQL thread id 6309, OS thread handle 2406095680, query id 41333 192.168.50.1 ammarfaizi2 update
INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('341292662', 'derido', 'Derido', 'Novelium', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10236 page no 4 n bits 72 index tg_user_id of table `test`.`tg_users` trx id 293921 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 293922, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1080, 2 row lock(s), undo log entries 1
MySQL thread id 6308, OS thread handle 2651384640, query id 41334 192.168.50.1 ammarfaizi2 update
INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('239302521', 'tomorimo', 'Tomorimo', 'Avede', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10236 page no 4 n bits 72 index tg_user_id of table `test`.`tg_users` trx id 293922 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10236 page no 4 n bits 72 index tg_user_id of table `test`.`tg_users` trx id 293922 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

當您執行前兩個選擇時，MySQL 在 tg_user_id 中的最高偽記錄上創建兩個 X 鎖，因為兩個 id（341292662 和 239302521）都超出了現有範圍。它看起來像一個錯誤。當您執行插入時，它們都嘗試在同一條記錄上獲得插入意圖鎖定，但它們被先前的鎖定阻塞。

編輯

確認為錯誤：https ://bugs.mysql.com/bug.php?id=25847

但是，我們有醜陋的變通方法來使這種交易場景有效：https ://stackoverflow.com/questions/17068686/how-do-i-lock-on-an-innodb-row-that-doesnt-exist-yet

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