Bug #96748 deadlock when doing SELECT FOR UPDATE and INSERT
Submitted: 4 Sep 2019 11:59 Modified: 4 Sep 2019 13:20
Reporter: Antony Dovgal Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: deadlock, insert, SELECT, UPDATE

[4 Sep 2019 11:59] Antony Dovgal
Description:
MySQL reports deadlock when two different sessions try to SELECT FOR UPDATE and then INSERT into the same table.

The bug #61502 looks similar, but it looks like the difference is that it refers to the gap lock, and this one is about insert intention.

The issue was successfully reproduced using fresh MySQL 8.0.17 Community Server

How to repeat:
1) Open two MySQL sessions (111 and 222), create a table:
CREATE TABLE `deadlocker` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`a_id` bigint unsigned NOT NULL,
`b_id` bigint unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique` (`a_id`,`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2) BEGIN transaction, SELECT a non-existent record for update in the first session.
3) BEGIN transaction, SELECT *another* non-existent record for update in the second session.
4) INSERT a new record in the first session.
5) INSERT a new record in the second session.
6) MySQL reports deadlock.

Step-by-step (pay attention to 111 and 222 prefixes for different sessions) reproducer:

111> use test
Database changed
111> CREATE TABLE `deadlocker` (
    ->   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    ->   `a_id` bigint unsigned NOT NULL,
    ->   `b_id` bigint unsigned NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `unique` (`a_id`,`b_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

111> BEGIN;
Query OK, 0 rows affected (0.00 sec)

111> SELECT * FROM deadlocker WHERE a_id = 1 AND b_id = 1 FOR UPDATE;
Empty set (0.00 sec)

222> BEGIN;
Query OK, 0 rows affected (0.00 sec)

222> SELECT * FROM deadlocker WHERE a_id = 1 AND b_id = 2 FOR UPDATE;
Empty set (0.01 sec)

111> INSERT INTO deadlocker (a_id, b_id) VALUES (1, 1);
Query OK, 1 row affected (3.83 sec)

222> INSERT INTO deadlocker (a_id, b_id) VALUES (1, 2);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Here's what InnoDB reports:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-01 20:23:23 0x7f96a021f700
*** (1) TRANSACTION:
TRANSACTION 183800, ACTIVE 17 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 8844, OS thread handle 140285040838400, query id 530884 localhost root update
INSERT INTO deadlocker (a_id, b_id) VALUES (1, 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 5 n bits 72 index unique of table `test`.`deadlocker` trx id 183800 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 183801, ACTIVE 8 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 8842, OS thread handle 140284908402432, query id 530921 localhost root update
INSERT INTO deadlocker (a_id, b_id) VALUES (1, 2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 168 page no 5 n bits 72 index unique of table `test`.`deadlocker` trx id 183801 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 168 page no 5 n bits 72 index unique of table `test`.`deadlocker` trx id 183801 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)
[4 Sep 2019 13:20] MySQL Verification Team
Hello Mr. Dovgal,

Thank you for your bug report.

However, this is not a bug,  but expected behaviour.

InnoDB is an ACID, MVCC and transactional storage engine. All such storage engines have a check for deadlocks. The fact that you got the deadlock error is a proof that InnoDB SE is working perfectly.

Deadlocks happen when, in the simplest case, transaction A locks row X1 and attempts to lock X2, while transaction B has locked row X2 and tries to get a lock on row X1. What you should do, is when you get this error, to resubmit each of the transactions that get this error. From your application side, to be clear.

Not a bug.