Bug #115226 Unexpected Behavior with SELECT ... FOR UPDATE SKIP LOCKED and ORDER BY
Submitted: 5 Jun 10:30 Modified: 5 Jun 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

[5 Jun 10:30] Rahman Khanipour
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)
[5 Jun 12:04] MySQL Verification Team
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.
[5 Jun 12:49] Rahman Khanipour
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)