MySQL Bugs: #21356: Change lock priority so that the transaction holding S-lock gets X-lock first

Bug #21356	Change lock priority so that the transaction holding S-lock gets X-lock first
Submitted:	31 Jul 2006 5:34	Modified:	27 Mar 2022 18:42
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	All	OS:	Any (All)
Assigned to:	Sunny Bains	CPU Architecture:	Any

Description:
This comes from Sinisa and Valeriy:

if a transaction T1 has an S-lock on a row and T1 wants to get an X-lock on the row, then the X-lock request should overtake an X-lock request by transaction T2 that does NOT have an S-lock on the row.

Note that we must be very careful that this does not break deadlock detection in lock0lock.c.

Note also that we cannot grant the X-lock if some transaction T3 is holding an S-lock.

How to repeat:
N/A

This is essentially a feature request.

This report is the original report for the following bug report:

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

Posted by developer:
 
Fixed as of the upcoming 8.0.29 release:

Transaction lock priority was modified so that a transaction holding a
shared lock and waiting to upgrade to an exclusive lock is prioritized
ahead of a another transaction waiting for an exclusive lock on the same
row. In other words, the transaction that already holds a shared lock is
granted an exclusive lock first. Some other transaction holding a shared
lock on the same row can still prevent an exclusive lock from being a
granted in this scenario. 

 The lock_rec_find_set_bit and
lock_rec_has_expl functions in the transaction lock system sources were
optimized.

After submit this bug report, deadlock example is changed on MySQL 8.0 manual.
So, current example is correct example, But at the time I saw deadlock example, following SQLs are listed in both MySQL 5.7 and 8.0 deadlock example manual.
Currently following example is listed only MySQL 5.7 manual.

============================================
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
============================================

Thanks.