Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5001

"NOWAIT/SKIP LOCKED" feature is not working with RocksDB and TokuDB

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Done
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: Not 5.6, Not 5.7, 8.0.12-2rc1
    • Fix Version/s: 8.0.15-5
    • Component/s: Documentation, MyRocks, TokuDB
    • Labels:
      None

      Description

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

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  borys.belinsky Borys Belinsky
                  Reporter:
                  ramesh.sivaraman Ramesh Sivaraman
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 3 hours, 26 minutes
                    3h 26m