MySQL Bugs: #93115: SKIP LOCKED mode is preventing to UPDATE the very next row.

Bug #93115	SKIP LOCKED mode is preventing to UPDATE the very next row.
Submitted:	8 Nov 2018 8:19	Modified:	9 Nov 2018 2:44
Reporter:	Ramesh Sivaraman	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	debug

Description:
SKIP LOCKED mode is preventing to UPDATE the very next row from a different session.

Getting "Lock wait timeout exceeded" error if update the very next row after the selected rows.

Is this expected behaviour?

Session 1

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

8.0.13>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE SKIP LOCKED;
+---------+--------+
| seat_no | booked |
+---------+--------+
|       8 | NO     |
|       9 | NO     |
+---------+--------+
2 rows in set (0.00 sec)

8.0.13>UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 8 AND 9;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

8.0.13>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 ;
+---------+--------+
| seat_no | booked |
+---------+--------+
|       8 | YES    |
|       9 | YES    |
+---------+--------+
2 rows in set (0.00 sec)

8.0.13>

Session 2

8.0.13>SELECT * FROM seats WHERE seat_no BETWEEN 7 AND 10 ;
+---------+--------+
| seat_no | booked |
+---------+--------+
|       7 | NO     |
|       8 | NO     |
|       9 | NO     |
|      10 | NO     |
+---------+--------+
4 rows in set (0.01 sec)

8.0.13>UPDATE seats SET booked = 'YES' WHERE seat_no = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
8.0.13>

How to repeat:
Session 1

START TRANSACTION;
SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE SKIP LOCKED;
UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 8 AND 9;
SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 ;

Session 2

SELECT * FROM seats WHERE seat_no BETWEEN 7 AND 10 ;
UPDATE seats SET booked = 'YES' WHERE seat_no = 10;

Complete testcase

drop table seats;

CREATE TABLE seats (
  seat_no INT PRIMARY KEY,
  booked ENUM('YES', 'NO') DEFAULT 'NO'
) ;

INSERT INTO seats (seat_no) WITH RECURSIVE my_cte AS ( SELECT 1 AS n UNION ALL SELECT 1+n FROM my_cte WHERE n<100 ) SELECT * FROM my_cte;

START TRANSACTION;
SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE SKIP LOCKED;
UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 8 AND 9;
SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 ;

Session 2

UPDATE seats SET booked = 'YES' WHERE seat_no = 10;

HI,

Thank you for your report. However, this is not a bug .....

You have simply used the SKIP LOCKED in the wrong context.

Your first statement in the first transaction is a locking SELECT with SKIP LOCKED used. However, since not a single row is locked, and thus, this SELECT simply locks all rows without any problems. Then, UPDATE in your second transaction hits upon the lock set by SELECT and, hence, waits ......

Try with a different setup. Run first a long-running update and then a locking SELECT with SKIP LOCKED clause that will go through the same rows. There will be no waits for the SELECT.

Not a bug.

Hi,

Yes, I used in the wrong context. But my question is why only selected rows and very next row is getting locked with this statement? This SELECT statmnt is not locking all the rows. In the given example locked rows ids are 8, 9 and 10. We can update row id 7 without any issue.

Hi,

Please read our manual on the various locks that are set by InnoDB SE. Pay special attention to gap locks, insert intention locks and similar ones ......

You also have tools at your disposal to see what statement is waiting on which locks ....