MySQL Bugs: #96748: deadlock when doing SELECT FOR UPDATE and INSERT

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

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)

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.

I'm experiencing the same issue and while I understand that deadlocks are expected, I don't understand how this scenario causes a deadlock. I would like to avoid failed queries so it would be nice to know what exactly I have to do to prevent this deadlock from happening.

I don't see how the second transaction is preventing the first from completing and releasing its lock as the second transaction doesn't appear to have acquired any locks yet when it runs the first query (select ... for update).

Hang on, I see now. I ran some tests and it looks like both transactions succeed on creating the lock at the same time because there's no rows to lock on. I kinda feel like that could be considered a bug, or at least unwanted behavior.

Hi,

We are happy that you have got it .....

Next, you should always use latest release, which is now 8.0.32.

Last, but not least, P_S has a nice way of presenting InnoDB locks. Just read a manual on the tables that are available there in the latest releases.

See for example, sub-paragraph 27.12.13.1.