我正在尝试调试一个让我困惑了一段时间的死锁场景。我想知道是否有人可以阐明这是如何发生的。
这是死锁输出：
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-06-28 14:04:18 0x7fe9a811d700 *** (1) TRANSACTION: TRANSACTION 3141, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 MySQL thread id 47, OS thread handle 140641311893248, query id 3281 172.17.0.1 mmuser update INSERT INTO PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE DeleteAt = ?, TeamId = ?, DisplayName = ?, Name = ?, Header = ?, Purpose = ? *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 76 page no 4 n bits 104 index Name of table `mydb`.`PublicChannels` trx id 3141 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 38 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 30; hex 6e656365737369746174696275732d697073612d6c6f6164746573746368; asc necessitatibus-ipsa-loadtestch; (total 38 bytes); 1: len 26; hex 776e6b6969666d6d7574723337796f6437336d7031686b616377; asc wnkiifmmutr37yod73mp1hkacw;; 2: len 26; hex 3875316d626365397a62677366677831627263656578356f6f6f; asc 8u1mbce9zbgsfgx1brceex5ooo;; *** (2) TRANSACTION: TRANSACTION 3140, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 50, OS thread handle 140641523848960, query id 3226 172.17.0.1 mmuser update INSERT INTO PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE DeleteAt = ?, TeamId = ?, DisplayName = ?, Name = ?, Header = ?, Purpose = ? *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 76 page no 4 n bits 104 index Name of table `mydb`.`PublicChannels` trx id 3140 lock_mode X locks gap before rec Record lock, heap no 38 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 30; hex 6e656365737369746174696275732d697073612d6c6f6164746573746368; asc necessitatibus-ipsa-loadtestch; (total 38 bytes); 1: len 26; hex 776e6b6969666d6d7574723337796f6437336d7031686b616377; asc wnkiifmmutr37yod73mp1hkacw;; 2: len 26; hex 3875316d626365397a62677366677831627263656578356f6f6f; asc 8u1mbce9zbgsfgx1brceex5ooo;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 76 page no 4 n bits 104 index Name of table `mydb`.`PublicChannels` trx id 3140 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 38 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 30; hex 6e656365737369746174696275732d697073612d6c6f6164746573746368; asc necessitatibus-ipsa-loadtestch; (total 38 bytes); 1: len 26; hex 776e6b6969666d6d7574723337796f6437336d7031686b616377; asc wnkiifmmutr37yod73mp1hkacw;; 2: len 26; hex 3875316d626365397a62677366677831627263656578356f6f6f; asc 8u1mbce9zbgsfgx1brceex5ooo;; *** WE ROLL BACK TRANSACTION (1)
这是表架构和索引：
mysql> describe PublicChannels; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | Id | varchar(26) | NO | PRI | NULL | | | DeleteAt | bigint(20) | YES | MUL | NULL | | | TeamId | varchar(26) | YES | MUL | NULL | | | DisplayName | varchar(64) | YES | | NULL | | | Name | varchar(64) | YES | MUL | NULL | | | Header | text | YES | | NULL | | | Purpose | varchar(250) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> show INDEX FROM PublicChannels; +----------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | PublicChannels | 0 | PRIMARY | 1 | Id | A | 1 | NULL | NULL | | BTREE | | | | PublicChannels | 0 | Name | 1 | Name | A | 1 | NULL | NULL | YES | BTREE | | | | PublicChannels | 0 | Name | 2 | TeamId | A | 1 | NULL | NULL | YES | BTREE | | | | PublicChannels | 1 | idx_publicchannels_team_id | 1 | TeamId | A | 1 | NULL | NULL | YES | BTREE | | | | PublicChannels | 1 | idx_publicchannels_name | 1 | Name | A | 1 | NULL | NULL | YES | BTREE | | | | PublicChannels | 1 | idx_publicchannels_delete_at | 1 | DeleteAt | A | 1 | NULL | NULL | YES | BTREE | | | | PublicChannels | 1 | idx_publicchannels_search_txt | 1 | Name | NULL | 1 | NULL | NULL | YES | FULLTEXT | | | | PublicChannels | 1 | idx_publicchannels_search_txt | 2 | DisplayName | NULL | 1 | NULL | NULL | YES | FULLTEXT | | | | PublicChannels | 1 | idx_publicchannels_search_txt | 3 | Purpose | NULL | 1 | NULL | NULL | YES | FULLTEXT | | | +----------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 9 rows in set (0.00 sec) mysql> show CREATE TABLE PublicChannels; | PublicChannels | CREATE TABLE `PublicChannels` ( `Id` varchar(26) NOT NULL, `DeleteAt` bigint(20) DEFAULT NULL, `TeamId` varchar(26) DEFAULT NULL, `DisplayName` varchar(64) DEFAULT NULL, `Name` varchar(64) DEFAULT NULL, `Header` text, `Purpose` varchar(250) DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Name` (`Name`,`TeamId`), KEY `idx_publicchannels_team_id` (`TeamId`), KEY `idx_publicchannels_name` (`Name`), KEY `idx_publicchannels_delete_at` (`DeleteAt`), FULLTEXT KEY `idx_publicchannels_search_txt` (`Name`,`DisplayName`,`Purpose`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | 1 row in set (0.00 sec)
此事务是 2 表修改查询的一部分，在该查询被调用之前插入另一个表。此表与上表没有关系。
话虽如此，让我们深入研究这个僵局：
Name
TeamId
但是即使他们这样做了，为什么 tx 2 已经拥有相同的锁，它又会尝试再次获取相同的锁呢？
这是我认为事件发生的顺序
最后一步让我感到困惑。为什么 UPSERT 语句会获得 2 个间隙锁？根据我对https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html的阅读：
INSERT … ON DUPLICATE KEY UPDATE 与简单的 INSERT 不同之处在于，当发生重复键错误时，将在要更新的行上放置排他锁而不是共享锁。对重复的主键值采用排他索引记录锁。对重复的唯一键值采用独占的下一个键锁。
好的，所以有一个 X 锁和一个间隙锁，这很好。但这不是死锁输出似乎所说的。
MySQL 版本是 5.7.28。
从查看代码来看，我似乎不可能尝试从 2 个不同的事务中插入相同的数据。
一个常见的错误是
BEGIN; SELECT ... WHERE ...; -- fetch the row to see if it there IF not there THEN INSERT ...; -- since it was not there ENDIF COMMIT;
解决方法是更改选择：
SELECT ... WHERE ... FOR UPDATE;
这会在 处获取一个锁SELECT，在那里它可以停止而不是死锁。（也就是说，innodb_lock_wait_time开始。几乎总是导致两个查询静默进行——第一个是成功的；第二个说“哎呀，那行存在”，并按照你的逻辑为这种情况做。
SELECT
innodb_lock_wait_time
要考虑的另一件事：拥有两个UNIQUE键（一个是PRIMARY KEY）会增加许多事情的复杂性（插入、死锁等）。你能摆脱id并简单地使用这对(Name, TeamId)吗？（这可能涉及几个应用程序代码更改。）
UNIQUE
PRIMARY KEY
id
(Name, TeamId)
