MySQL Bugs: #105655: Wait for the lock that has already been acquired.

Bug #105655	Wait for the lock that has already been acquired.
Submitted:	22 Nov 2021 9:01	Modified:	3 Feb 2022 7:10
Reporter:	SeWoong Jeon	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.0.25	OS:	CentOS (7.9)
Assigned to:		CPU Architecture:	x86
Tags:	deadlock

Description:
I got a deadlock message, which looks very strange.

First transaction has already acquired X Lock for a record, but it is waiting for X Lock for the same record.
(space id 120 page no 4 n bits 72 index PRIMARY of table mydb.tb_locking_key)

Although X locks and S locks cannot coexist, the second transaction holds an S lock on the record.
(space id 120 page no 4 n bits 72 index PRIMARY of table mydb.tb_locking_key)

How is this possible?

===== Table Description =====
CREATE TABLE `tb_locking_key` (
  `locking_key` varchar(128) NOT NULL,
  PRIMARY KEY (`locking_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

===== Deadlock Message =====
2021-11-22 16:58:37 140341095802624
*** (1) TRANSACTION:
TRANSACTION 421310084, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 6131072, OS thread handle 140265340016384, query id 13266700682 192.168.56.37 myuser updating
DELETE
            FROM tb_locking_key
        WHERE locking_key = '7263397241'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table `mydb`.`tb_locking_key` trx id 421310084 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 10; hex 37323633333937323431; asc 7263397241;;
 1: len 6; hex 0000191cadf5; asc       ;;
 2: len 7; hex 8100000ac80110; asc        ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table `mydb`.`tb_locking_key` trx id 421310084 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 10; hex 37323633333937323431; asc 7263397241;;
 1: len 6; hex 0000191cadf5; asc       ;;
 2: len 7; hex 8100000ac80110; asc        ;;

*** (2) TRANSACTION:
TRANSACTION 421310042, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 6130658, OS thread handle 140269315421952, query id 13266701560 192.168.56.36 myuser updating
DELETE
            FROM tb_locking_key
        WHERE locking_key = '7263397241'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table `mydb`.`tb_locking_key` trx id 421310042 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 10; hex 37323633333937323431; asc 7263397241;;
 1: len 6; hex 0000191cadf5; asc       ;;
 2: len 7; hex 8100000ac80110; asc        ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table `mydb`.`tb_locking_key` trx id 421310042 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 10; hex 37323633333937323431; asc 7263397241;;
 1: len 6; hex 0000191cadf5; asc       ;;
 2: len 7; hex 8100000ac80110; asc        ;;

*** WE ROLL BACK TRANSACTION (1)

How to repeat:
It occurs intermittently and is difficult to reproduce.

Hello and thanks for reporting.
This is a known issue - the "HOLDS THE LOCK(S):" section title is misleading, sorry.
It should rather be "HAS MADE A CONFLICTING REQUEST:", but we haven't changed it for backwards compatibility with tools which expect this exact wording.

The X Lock request in question has not been granted yet - it still waiting, which you can tell by looking at the last word ("waiting") of the line describing the state:

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table `mydb`.`tb_locking_key` trx id 421310084 lock_mode X locks rec but not gap waiting

Even though the request wasn't granted yet, it is still conflicting with the request of the second transaction, because InnoDB is trying to avoid starvation of the first transaction.
The lock queue for the record <space_id=120,page_no=4,heap_no=2> looks something like this:

[granted S-lock of Trx2] <-- [waiting X-lock of Trx1] <-- [waiting X-lock of Trx2]

This is to be expected in situations where Trx2 is trying to perform "lock escalation" from "S" to "X", but there's already Trx1 waiting for the lock. Current implementation of InnoDB tries to avoid starving Trx1, so it asks Trx2 to wait so that Trx1 gets its turn to get X lock, but this leads to a deadlock.
Arguably, this behaviour could be changed, and this was already contemplated recently in https://bugs.mysql.com/bug.php?id=101695 and years ago in https://bugs.mysql.com/bug.php?id=21356 but this sounds more like a feature request than a bug.

Hi Mr  Jeon,

Thank you for your bug report.

We agree with members of the InnoDB team on this issue. This report is a feature request, that is a duplicate of the feature request:

https://bugs.mysql.com/bug.php?id=21356

That feature request was filed long time ago by the members of the MySQL team. We agree that this feature request should get a higher priority. It would be nice if you would go to the above original report and click on "Affects Me" button at the right-hand upper corner of the corresponding item.

A duplicate.

The fix for Bug #21356 "Change lock priority so that the transaction holding S-lock gets X-lock first" will improve behaviour in scenarios like this.

I totally agree with your opinion.
Thank you.

Only a small addition.

The original feature request:

https://bugs.mysql.com/bug.php?id=21356

is much wider than just upgrading locks from shared to exclusive. It encompasses, actually, ANY lock upgrades and there are others, as described in our Reference Manual.

It is also worth noted that, when materialised, this algorithm would add a new feature to the theory of locks in ACID storage engines.