MySQL Bugs: #115226: Unexpected Behavior with SELECT ... FOR UPDATE SKIP LOCKED and ORDER BY

Bug #115226	Unexpected Behavior with SELECT ... FOR UPDATE SKIP LOCKED and ORDER BY
Submitted:	5 Jun 2024 10:30	Modified:	5 Jun 2024 12:49
Reporter:	Rahman Khanipour	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There appears to be a bug in MySQL when using the SELECT ... FOR UPDATE SKIP LOCKED statement in combination with an ORDER BY clause. The expected behavior is that only the final selected rows should be locked. However, adding an ORDER BY clause seems to cause MySQL to lock all rows in the table, rather than just the rows that are actually selected.

How to repeat:
### Create test table:
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    value VARCHAR(100)
);

INSERT INTO test_table (id, value) VALUES
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E');

### Session 1:
START TRANSACTION;

-- Select rows with locking, with ORDER BY
SELECT * FROM test_table WHERE value > 'B' ORDER BY id FOR UPDATE SKIP LOCKED;

-- (Expected to lock rows 3, 4, and 5, but seems to lock all rows)

### Session 2:
START TRANSACTION;

-- Try to select any row
SELECT * FROM test_table WHERE value = 'A' FOR UPDATE NOWAIT;

-- (Unexpectedly fails due to locks held by Session 1)

Hi Mr. Khanipour,

Thank you for your bug report.

However, we do not see why should this be considered as a bug.

First SELECT is given the option that it, that current select, skips all locks. It is not given instruction to allow other concurrent transactions to skip locks.

Hence, lock wait in your case is quite expected one.

This is all explained in our Reference Manual, which can be found on https://dev.mysql.com.

Not a bug.

Thank you for your response.

But I think I should explain it more

If I have SELECT query like this without 'ORDER BY' it only lock row 3, 4, 5:

SELECT * FROM test_table WHERE value > 'B' FOR UPDATE SKIP LOCKED;
-- (it only lock rows 3, 4, and 5)

But If I add ORDER BY to the SELECT it will lock all rows 1, 2, 3, 4, 5

SELECT * FROM test_table WHERE value > 'B' ORDER BY value FOR UPDATE SKIP LOCKED;
-- (Expected to lock rows 3, 4, and 5, but seems to lock all rows)