-
Type:
Improvement
-
Status: In Progress
-
Priority:
Blocker
-
Resolution: Unresolved
-
Affects Version/s: Not 5.6, Not 5.7, 8.0.12-2rc1
-
Fix Version/s: 8.0.NEXT
-
Component/s: Documentation, MyRocks, TokuDB
-
Labels:None
"NOWAIT/SKIP LOCKED" feature is not working with RocksDB and TokuDB. These engines are not reading rows which are not locked. Is this expected with TokuDB and RocksDB ?
Testcase InnoDB.
Session 1 8.0.12>CREATE TABLE seats ( -> seat_no INT PRIMARY KEY, -> booked ENUM('YES', 'NO') DEFAULT 'NO' -> ) engine = innodb ; Query OK, 0 rows affected (0.03 sec) 8.0.12> 8.0.12>INSERT INTO seats (seat_no) WITH RECURSIVE my_cte AS ( SELECT 1 AS n UNION ALL SELECT 1+n FROM my_cte WHERE n<100 ) SELECT * FROM my_cte; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 8.0.12> 8.0.12>START TRANSACTION; Query OK, 0 rows affected (0.00 sec) 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE SKIP LOCKED; +---------+--------+ | seat_no | booked | +---------+--------+ | 8 | NO | | 9 | NO | +---------+--------+ 2 rows in set (0.00 sec) 8.0.12> 8.0.12>UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 8 AND 9; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 ; +---------+--------+ | seat_no | booked | +---------+--------+ | 8 | YES | | 9 | YES | +---------+--------+ 2 rows in set (0.00 sec)8.0.12> Session 2 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 1 AND 10 AND booked = 'NO' FOR UPDATE SKIP LOCKED; +---------+--------+ | seat_no | booked | +---------+--------+ | 1 | NO | | 2 | NO | | 3 | NO | | 4 | NO | | 5 | NO | | 6 | NO | | 7 | NO | +---------+--------+ 7 rows in set (0.00 sec)8.0.12>
Testcase TokuDB
Session 1 8.0.12>CREATE TABLE seats ( -> seat_no INT PRIMARY KEY, -> booked ENUM('YES', 'NO') DEFAULT 'NO' -> ) engine = tokudb; Query OK, 0 rows affected (0.02 sec) 8.0.12> 8.0.12>INSERT INTO seats (seat_no) WITH RECURSIVE my_cte AS ( SELECT 1 AS n UNION ALL SELECT 1+n FROM my_cte WHERE n<100 ) SELECT * FROM my_cte; Query OK, 100 rows affected (0.00 sec) Records: 100 Duplicates: 0 Warnings: 0 8.0.12> 8.0.12>START TRANSACTION; Query OK, 0 rows affected (0.00 sec) 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE SKIP LOCKED; +---------+--------+ | seat_no | booked | +---------+--------+ | 8 | NO | | 9 | NO | +---------+--------+ 2 rows in set (0.00 sec) 8.0.12> 8.0.12>UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 8 AND 9; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 ; +---------+--------+ | seat_no | booked | +---------+--------+ | 8 | YES | | 9 | YES | +---------+--------+ 2 rows in set (0.00 sec)8.0.12> Session 2 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 1 AND 15 AND booked = 'NO' FOR UPDATE SKIP LOCKED; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 8.0.12>
Testcase RocksDB
Session 1 8.0.12>CREATE TABLE seats ( -> seat_no INT PRIMARY KEY, -> booked ENUM('YES', 'NO') DEFAULT 'NO' -> ) engine = rocksdb ; Query OK, 0 rows affected (0.01 sec) 8.0.12> 8.0.12>INSERT INTO seats (seat_no) WITH RECURSIVE my_cte AS ( SELECT 1 AS n UNION ALL SELECT 1+n FROM my_cte WHERE n<100 ) SELECT * FROM my_cte; Query OK, 100 rows affected (0.00 sec) Records: 100 Duplicates: 0 Warnings: 0 8.0.12> 8.0.12>START TRANSACTION; Query OK, 0 rows affected (0.00 sec) 8.0.12>SELECT * FROM seats WHERE seat_no in (8,9) AND booked = 'NO' FOR UPDATE SKIP LOCKED; +---------+--------+ | seat_no | booked | +---------+--------+ | 8 | NO | | 9 | NO | +---------+--------+ 2 rows in set (0.00 sec)8.0.12> 8.0.12> 8.0.12>UPDATE seats SET booked = 'YES' WHERE seat_no in(8,9); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 ; +---------+--------+ | seat_no | booked | +---------+--------+ | 8 | YES | | 9 | YES | +---------+--------+ 2 rows in set (0.00 sec)8.0.12> Session 2 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 1 AND 15 AND booked = 'NO' FOR UPDATE SKIP LOCKED; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 8.0.12>
NOWAIT Testcase
InnoDB 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE NOWAIT; ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set. 8.0.12> 8.0.12> TokuDB 8.0.12> 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE NOWAIT; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 8.0.12> 8.0.12> RocksDB 8.0.12>SELECT * FROM seats WHERE seat_no BETWEEN 8 AND 9 AND booked = 'NO' FOR UPDATE NOWAIT; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 8.0.12>
- blocks
-
PS-5125 Doc issues required for 8.0 GA
-
- In Progress
-