Bug #97089 Bug: row lock not released when chaining two or more "order by"

• Submitted: 2 Oct 2019 17:37
• Modified: 3 Oct 2019 13:42
• Reporter: Chương Lê
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 8.0.16
• OS: Linux (ubuntu 19.10)
• CPU Architecture: Any

[2 Oct 2019 17:37] Chương Lê

Description:
When chaining order by at least two times, row lock mechanism seem broken - return wrong number of rows

Take a look at the example below:

How to repeat:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

CREATE DATABASE test_database;

USE test_database;

CREATE TABLE `test_table` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`key`)
);

INSERT INTO test_table (`key`, `value`) values (1, 2);
INSERT INTO test_table (`key`, `value`) values (3, 4);

Now open two mysql shells:

shell 1:

USE test_database;
BEGIN;
SELECT * FROM test_table ORDER BY `key`, `value` LIMIT 1 FOR UPDATE SKIP LOCKED;
+-----+-------+
| key | value |
+-----+-------+
| 1   | 2     |
+-----+-------+
1 row in set (0.00 sec)

shell 2:

USE test_database;
BEGIN;
SELECT * FROM test_table ORDER BY `key`, `value` LIMIT 1 FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)

Suggested fix:
on shell 2

It is suppose to return

+-----+-------+
| key | value |
+-----+-------+
| 3   | 4     |
+-----+-------+
1 row in set (0.00 sec)

Was my assumption right ?

(Sorry for putting this S1 - critical, though you would never look if I didn't do so)

[3 Oct 2019 13:19] MySQL Verification Team

Hi Mr. Le,

Thank you for your bug report.

First of all, for your information, we do not prioritise bug processing according to the reported severity. That is because final severity is set by us, not by the reporter.

Regarding your report, this is expected behaviour, as described in the chapter 15.7.2 of our Reference Manual. Simply, locked row are counted in the LIMIT. This is so by design.

If you think that this behaviour should be changed, so that LIMIT returns any further row, if available, then we could make this a feature request.

Decision is entirely upon you .....

[3 Oct 2019 13:40] MySQL Verification Team

Hi Mr. Le,

It was concluded that this is a valid feature request, hence it is verified as such.

Verified ........