MySQL deadlock: upsert query acquiring gap lock twice?

Question

I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen.

Here is the deadlock output:

------------------------
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)

This is the table schema and indexes:

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)

This transaction is part of a 2 table modification query where another table is inserted before this gets called. There is no relation with the previous table with this table.

With that said, let's dig into this deadlock:

• We can see that it's a deadlock due to a gap lock. And that's clear because the index is Name which is a multi-column index using Name and TeamId.
• But interestingly, both transactions seem to be inserting the same data, which is what is puzzling me. The multi-column index on Name and TeamId will guarantee that they are always unique. And from looking at the code, it does not seem possible to me that it will try to insert the same data from 2 different transactions.

But even if they do, why does tx 2 try to acquire the same lock again when it already has that ?

Here is what I think the order of events happening

• Tx 2 gets a gap lock.
• Tx 1 tries to get the same gap lock.
• Tx 2 tries to again get the same gap lock ?

The last step is what is puzzling me. Why does an UPSERT statement acquire 2 gap locks ? From my reading of https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html:

INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

Ok, so there is one X lock and one gap lock, which is fine. But that's not what the deadlock output seems to say.

MySQL version is 5.7.28.

Answer 1

And from looking at the code, it does not seem possible to me that it will try to insert the same data from 2 different transactions.

A common mistake is

BEGIN;
SELECT ... WHERE ...;   -- fetch the row to see if it there
IF not there THEN
    INSERT ...;  -- since it was not there
ENDIF
COMMIT;

The fix is to change the select:

SELECT ... WHERE ... FOR UPDATE;

This grabs a lock(s) at the SELECT, where it can stall instead of deadlocking. (That is, innodb_lock_wait_time kicks in. Almost always that leads to both queries silently proceeding -- the first one is successful; the second one says "oops, that row exists" and does what your logic says to do for that case.

Another thing to consider: Having two UNIQUE keys (one being the PRIMARY KEY), increases the complexity of many things (inserts, deadlocks, etc). Can you get rid of id and simply use the pair (Name, TeamId)? (This may involve several application code changes.)