-
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
-
