Solution for Insert Intention Locks in MySQL

Question

I have very simple table:

CREATE TABLE `d` (
    `id` int(11) DEFAULT NULL,
    UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

without records:

select * from d;
Empty set (0,01 sec)

Then I try to open two transactions in different sessions:

Session #1:

begin;
Query OK, 0 rows affected (0,00 sec)

select * from d where id = 100 for update;
Empty set (0,00 sec)

Session #2:

begin;
Query OK, 0 rows affected (0,00 sec)

select * from d where id = 700 for update;
Empty set (0,00 sec)

Now I try to insert new record in Session #2 and session "freezes":

insert into d values (700);

And when I try to do the same (with another id field) in Session #1 it crashes:

insert into d values (100); --> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction in Session #1
insert into d values (700); --> Query OK, 1 row affected (4,08 sec) in Session #2

How can I to fix the deadlock? InnoDB status is:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-06 15:59:25 0x70000350d000
*** (1) TRANSACTION:
TRANSACTION 43567, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 123145358217216, query id 89 localhost root update
insert into d values (700)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43567 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 43568, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145357938688, query id 90 localhost root update
insert into d values (100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 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 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 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)

Answer 1

This deadlock error is a bug in the MySQL InnoDB engine that has not been fixed for 12 years. (Bug #25847: https://bugs.mysql.com/bug.php?id=25847, workaround: How do I lock on an InnoDB row that doesn't exist yet?) It is not related to Unique Key. Running this query will also result in the same Deadlock error.

Session #1: CREATE TABLE t (id int) ENGINE=InnoDB;
Session #1: SET AUTOCOMMIT = 0;
Session #1: SELECT id FROM t WHERE id = 1 FOR UPDATE;
Session #2: SET AUTOCOMMIT = 0;
Session #2: SELECT id FROM t WHERE id = 2 FOR UPDATE;
Session #1: INSERT INTO t (id) VALUES (1); -- Hang
Session #2: INSERT INTO t (id) VALUES (2); -- Session #1: OK, Session #2: Deadlock found when trying to get lock; try restarting transaction

InnoDB Status is the same:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-24 00:25:31 0x1638
*** (1) TRANSACTION:
TRANSACTION 1287, ACTIVE 62 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 9444, query id 143 localhost ::1 root update
INSERT INTO t (id) VALUES (1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1287 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 1288, ACTIVE 19 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 9, OS thread handle 5688, query id 145 localhost ::1 root update
INSERT INTO t (id) VALUES (2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1288 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 23 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1288 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)

Answer 2

Note that starting with MySQL 8.0.1, the performance schema exposes innodb data locks.

See https://dev.mysql.com/doc/refman/8.0/en/data-locks-table.html

See https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html

In this example, after the first select in session 1 alone the locks are:

mysql> select * from performance_schema.data_locks \G
*************************** 1. row ***************************                                                                                                              
               ENGINE: INNODB                                                                                                                                               
       ENGINE_LOCK_ID: 1808:76                                                                                                                                              
ENGINE_TRANSACTION_ID: 1808                                                                                                                                                 
            THREAD_ID: 35                                                                                                                                                   
             EVENT_ID: 13081                                                                                                                                                
        OBJECT_SCHEMA: test                                                                                                                                                 
          OBJECT_NAME: d                                                                                                                                                    
       PARTITION_NAME: NULL                                                                                                                                                 
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139756088373592
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1808:2:5:1
ENGINE_TRANSACTION_ID: 1808
            THREAD_ID: 35
             EVENT_ID: 13111
        OBJECT_SCHEMA: test
          OBJECT_NAME: d
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: id
OBJECT_INSTANCE_BEGIN: 139756088370552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record <--- HERE
2 rows in set (0.00 sec)

This is not a solution to the deadlock itself, but having visibility on the locks taken goes a long way to understand the problem.

Here, both SELECT FOR UPDATE lock the "suprenum" record, because id 100 and 700 are greater than the biggest ID in the table (it's empty).

Once more records are present (say at ID = 500), both queries should execute concurrently, as a different gap in IDs will be locked.