MySQL deadlock: upsert query acquiring gap lock twice

deadlockMySQLmysql-5.7

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.

Best Answer

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

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks

Mysql – Meaning of ‘locks rec but not gap waiting’ in deadlock report

Question 1: InnoDB is trying to get an exclusive lock on the row (it's actually a lock on the clustered index record, the PK), but not the surrounding gap (a gap lock). You can read more about record and gap locks here: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks

The exclusive lock request is coming from the "for update" clause added to the SELECT statement. Keep in mind that when using the repeatable-read isolation level (select @@session.tx_isolation), which is the default, the exclusive lock will be placed on every row examined for the SELECT, not just those that are returned (those that match all the predicates in the WHERE clause). So you may be able to reduce the number of rows examined, and thus the locks held which will greatly reduce the chances of conflicts, by using a composite index on: processing, delivered, failed, deliver_after

I don't know what the cardinality (number of unique values) is for those columns, but you should generally list them in the composite index from highest cardinality to lowest.

Question 2: 31 is the number of fields in the index record (or row, logically) that it's trying/waiting to get an exclusive lock on. InnoDB uses a clustered PK, so the records in the clustered index (the PK) contain the fields for each user defined column--29 fields in the case of your push_notifications table: id ... category--and there are always two additional internal fields for each InnoDB table: a 6-byte transaction ID field and a 7-byte roll pointer field (these are used for MVCC). So the 31 items you see listed are the fields contained within the index record (or the row, logically) that we're waiting for the lock on.

Just FYI: You can get further breakdowns of InnoDB locks using new Information_Schema tables in 5.6+: https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-transactions.html

There's also some great reports in the new SYS schema, which is included with the server in MySQL 5.7+ (hasn't quite made its way into the online manual yet though): https://github.com/MarkLeith/mysql-sys#innodb_lock_waits--xinnodb_lock_waits

You can install the latest SYS schema in MySQL 5.6 manually, if it's of interest: https://github.com/MarkLeith/mysql-sys/blob/master/sys_56.sql

For more info on it: http://www.slideshare.net/Leithal/performance-schema-and-sys-schema-in-mysql-57

I hope this helps!

Best Answer

Related Solutions

Mysql – Clarification on thesql deadlock situation

SUGGESTION

Mysql – Meaning of ‘locks rec but not gap waiting’ in deadlock report

Related Question