Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.6.2, 11.7.2
-
None
-
Docker mariadb:latest
Version() 11.7.2-MariaDB-ubu2404
Description
This is also reported for MySQL: Unexpected Behavior with SELECT ... FOR UPDATE SKIP LOCKED and ORDER BY
SELECT ... FOR UPDATES SKIP LOCKED locks rows that are not selected when using WHERE or ORDER BY.
CREATE TABLE test ( id INT PRIMARY KEY, value VARCHAR(100) ); |
|
|
INSERT INTO test(id, value) VALUES |
(1, 'A'), |
(2, 'B'), |
(3, 'C'), |
(4, 'D'), |
(5, 'E'); |
As expected, without WHERE or ORDER BY
Session 1:
MariaDB [test]> START TRANSACTION; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> SELECT * FROM test_table LIMIT 1 FOR UPDATE SKIP LOCKED; |
+----+-------+ |
| id | value |
|
+----+-------+ |
| 1 | A |
|
+----+-------+ |
1 row in set (0.000 sec) |
Session 2:
MariaDB [test]> SELECT * FROM test_table FOR UPDATE SKIP LOCKED; |
+----+-------+ |
| id | value |
|
+----+-------+ |
| 2 | B |
|
| 3 | C |
|
| 4 | D |
|
| 5 | E |
|
+----+-------+ |
4 rows in set (0.000 sec) |
Not as expected, using WHERE clause
Rows 1 and 2 are locked, eventhough they're not selected in Session 1.
Session 1:
MariaDB [test]> START TRANSACTION; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> SELECT * FROM test_table WHERE value>'B' LIMIT 1 FOR UPDATE SKIP LOCKED; |
+----+-------+ |
| id | value |
|
+----+-------+ |
| 3 | C |
|
+----+-------+ |
1 row in set (0.000 sec) |
Session 2:
MariaDB [test]> SELECT * FROM test_table FOR UPDATE SKIP LOCKED; |
+----+-------+ |
| id | value |
|
+----+-------+ |
| 4 | D |
|
| 5 | E |
|
+----+-------+ |
2 rows in set (0.000 sec) |
Not as exepected, when ordering (in some cases):
All rows are locked, eventhough only 1 is selected in Session 1.
Session 1:
MariaDB [test]> START TRANSACTION; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> SELECT * FROM test_table order by value LIMIT 1 FOR UPDATE SKIP LOCKED; |
+----+-------+ |
| id | value |
|
+----+-------+ |
| 1 | A |
|
+----+-------+ |
1 row in set (0.000 sec) |
Session 2:
MariaDB [test]> SELECT * FROM test_table FOR UPDATE SKIP LOCKED; |
Empty set (0.000 sec) |
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to