MySQL Bugs: #115246: Unexpected Behaviour with SELECT ... SKIP LOCKED and ORDER BY and LIMIT

Bug #115246	Unexpected Behaviour with SELECT ... SKIP LOCKED and ORDER BY and LIMIT
Submitted:	6 Jun 2024 15:30	Modified:	7 Jun 2024 12:41
Reporter:	Rahman Khanipour	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi team,

(I report the issue before but i explain it incorrectly and I think it was closed.) 

I investigated it more and I can say that it seams there is a bug in MySql behaviour.

There is an unexpected behaviour when using the SELECT ... LIMIT x FOR UPDATE SKIP LOCKED statement in combination with an ORDER BY clause. 

if we have a select query with limit clause and FOR UPDATE SKIP LOCKED the engine lock only the limited rows. but If add an ORDER BY clause, the engine will lock all the records. 

I also check it with PostgreSQL and it's working correctly there

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');

If I have SELECT query like this without 'ORDER BY' it only lock row 1 and 2:

SELECT * FROM test_table limit 2 FOR UPDATE SKIP LOCKED;
-- (it only lock rows 1 and 2)

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

SELECT * FROM test_table ORDER BY value limit 2 FOR UPDATE SKIP LOCKED;
-- (Expected to lock rows 1 and 2 again but db lock all rows)

Real Example:

result of 'SELECT * from performance_schema.data_locks;' in a select with limit 2 without 'order by':
'select * from outbox_message limit 2 for update skip locked'

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281472781675736:1070:281472695063472	2126	48	92	testdb	outbox_message				281472695063472	TABLE	IX	GRANTED	
INNODB	281472781675736:4:4:6:281472695060480	2126	48	92	testdb	outbox_message			PRIMARY	281472695060480	RECORD	X	GRANTED	'0e4a6d5f-aa2b-429d-8cf8-6ecaefd35993'
INNODB	281472781675736:4:4:9:281472695060480	2126	48	92	testdb	outbox_message			PRIMARY	281472695060480	RECORD	X	GRANTED	'1294b612-cd59-4abf-810d-ea1698d74375'

result of 'SELECT * from performance_schema.data_locks;' in a select with 'limit 2' and 'order by':
'select * from outbox_message order by send_after limit 2 for update skip locked'

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281473421634776:1070:281473433260976	2126	48	93	testdb	outbox_message				281473433260976	TABLE	IX	GRANTED	
INNODB	281473421634776:4:4:1:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	supremum pseudo-record
INNODB	281473421634776:4:4:2:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'3283dd40-aad5-4c23-b08f-6d12aab247cd'
INNODB	281473421634776:4:4:3:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'5e243665-ccef-4b11-83eb-83772d042eff'
INNODB	281473421634776:4:4:4:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'6aad814b-868e-4fad-8601-24bcf5fa5830'
INNODB	281473421634776:4:4:5:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'1294b612-cd59-4abf-810d-ea1698d74375'
INNODB	281473421634776:4:4:6:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'922d3f07-d89f-4e19-a188-2349ac7506b9'
INNODB	281473421634776:4:4:7:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'94692e9e-32e8-4517-9055-99f78da93d16'
INNODB	281473421634776:4:4:8:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'fa515f91-89c5-423d-b7ce-5969a3d1a359'
INNODB	281473421634776:4:4:9:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'2c65841f-3025-4ea7-a130-5201e8cc7ef2'
INNODB	281473421634776:4:4:10:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'f2cc7aa4-18b1-4d30-95a7-a094bfcf3534'
INNODB	281473421634776:4:4:11:281473433257984	2126	48	93	testdb	outbox_message			PRIMARY	281473433257984	RECORD	X	GRANTED	'0e4a6d5f-aa2b-429d-8cf8-6ecaefd35993'

HI Mr. Khanipour,

Thank you for your bug report.

However, it is not a bug.

Without ORDER BY, query simply locks first two rows. But since you do not have enough rows and you do not have an index on the column on which ORDER BY is executed, InnoDB has to scan all rows, in order to sort the smallest rows.

When InnoDB scans rows, it locks them all.

It is all explained in our Reference Manual.

Not a bug.

Hi team,

Thank you for your response.

However, I must reiterate my concern regarding the behavior observed. Your response suggests that the issue is related to the lack of an index on the column used in the ORDER BY clause. However, I would like to clarify a few points:

 - Index on send_after: We do have an index on the send_after column, which should theoretically optimize the sorting operation and prevent the need to lock all rows.

 - High Load Scenario: The example provided was simplified for clarity. In our actual use case, we are running this query on a significantly larger dataset, and the unexpected behavior severely impacts our application by DeadLock issue and we want to sort data to avoid deadlocks

 - In PostgreSQL, the same query with ORDER BY does not lock all rows. It locks only the rows within the specified limit, maintaining consistency regardless of the presence of the ORDER BY clause.

Hi Mr. Khanipour,

When there is no index on the ORDER BY rows or if the number of rows is not large enough, index is not used, because it is faster to scan all rows.

Hence, it causes all rows to e locked.

Please, read our entire Reference Manual.

Not a bug.

Hi team,

Thank you for your response. However, I would like to address the rationale provided and clarify why this behavior seems illogical and inconsistent with the intended use of the SKIP LOCKED feature.

- Clarification:
Regardless of whether the engine scans all rows due to the lack of an index or for performance reasons, it is not logical for it to lock all rows. According to your documentation:
"This SKIP LOCKED feature was introduced to better manage situations where you have tables with hot row contention. This issue frequently presents itself in scenarios such as worker threads all accessing the same tables trying to find new work, and ecommerce websites trying to keep accurate inventory counts."

- Example from Documentation:
Referring to your reference documents https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-..., let's consider the example of sorting seats based on a field. Are you suggesting that if the number of seats is not large enough or if there is no index on the sorting field, MySQL will lock all seats? This behavior is counterproductive. The purpose of the SKIP LOCKED feature is to handle hot rows efficiently, not to exacerbate locking issues by locking more rows than necessary.

- Logical Inconsistency:
Locking all rows simply because they are scanned contradicts the purpose of SKIP LOCKED. The expected behavior is to lock only the rows within the specified limit, even when an ORDER BY clause is present. This ensures efficient row-level locking and better concurrency management.

- Conclusion:
The current behavior negatively impacts performance and concurrency, especially in high-load scenarios with large datasets. I urge you to reconsider this issue and align the locking mechanism with the intended use of SKIP LOCKED to maintain logical consistency and application performance.

Thank you for your attention to this matter.

Best regards,

Thank you Mr. Khanipour, for your interest in MySQL.

MySQL is just following all standards that are valid for a transactional database engine.

Hence, we have to follow not just SQL standard, but also standards for the transactional processing, MVCC, ACID and so on .......

Throughout our history as a transactional engine, we have made all necessary changes to the locking, which are safe. We can not introduce the changes that would make MVCC unreliable and erroneous. 

We do recommend  you to read all the available books on the subject, so you could understand it better.

Not a bug.