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

TokuDB does not lock rows for writes with LOCK IN SHARE MODE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.6.31-77.0, 5.7.17-11
    • Fix Version/s: 5.6.37-82.2, 5.7.21-20
    • Component/s: TokuDB
    • Labels:
      None

      Description

      If I use LOCK IN SHARE MODE for SELECT I expect behavior, similar to which InnoDB has (https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html) or error, or exclusive locks. But nothing happens: TokuDB just locks rows as if it was simple SELECT statement.

      How to repeat:

      session1> show create table t;
      +-------+------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------+
      | t | CREATE TABLE `t` (
        `id` int(11) NOT NULL,
        `f` varchar(100) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=TokuDB DEFAULT CHARSET=latin1 |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.06 sec)
      
      session1> create table i like t;
      Query OK, 0 rows affected (0.50 sec)
      
      session1> alter table i engine=innodb;
      Query OK, 0 rows affected (0.64 sec)
      Records: 0 Duplicates: 0 Warnings: 0
      
      session1> insert into i select * from t;
      Query OK, 2 rows affected (0.06 sec)
      Records: 2 Duplicates: 0 Warnings: 0
      
      session1> select * from i;
      +-------+--------+
      | id | f |
      +-------+--------+
      | 12345 | value1 |
      | 54321 | value2 |
      +-------+--------+
      2 rows in set (0.00 sec)
      
      session1> select * from t;
      +-------+--------+
      | id | f |
      +-------+--------+
      | 12345 | value1 |
      | 54321 | value2 |
      +-------+--------+
      2 rows in set (0.00 sec)
      
      session1> begin;
      Query OK, 0 rows affected (0.00 sec)
      
      session1> select * from i where id=12345 lock in share mode;
      +-------+--------+
      | id | f |
      +-------+--------+
      | 12345 | value1 |
      +-------+--------+
      1 row in set (0.00 sec)
      In another session:
      session2> begin;
      Query OK, 0 rows affected (0.00 sec)
      
      session2> update i set f='value3' where id=12345;
      ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
      session2> rollback;
      Query OK, 0 rows affected (0.00 sec)
      

      ====
      This works as expected: UPDATE is not allowed on InnoDB table.

      But it is with TokuDB.
      ====

      session1> rollback;
      Query OK, 0 rows affected (0.00 sec)
      
      session1> begin;
      Query OK, 0 rows affected (0.00 sec)
      
      session1> select * from t where id=12345 lock in share mode;
      +-------+--------+
      | id | f |
      +-------+--------+
      | 12345 | value1 |
      +-------+--------+
      1 row in set (0.00 sec)
      
      

      In another session:

      session2> begin;
      Query OK, 0 rows affected (0.00 sec)
      
      session2> update t set f='value3' where id=12345;
      Query OK, 1 row affected (0.00 sec)
      Rows matched: 1 Changed: 1 Warnings: 0
      session2> select * from information_schema.tokudb_locks\G
      *************************** 1. row ***************************
                     locks_trx_id: 505
            locks_mysql_thread_id: 238
                      locks_dname: ./test/t-main
                   locks_key_left: ff39300000
                  locks_key_right: 0139300000
               locks_table_schema: test
                 locks_table_name: t
      locks_table_dictionary_name: main
      *************************** 2. row ***************************
                     locks_trx_id: 505
            locks_mysql_thread_id: 238
                      locks_dname: ./test/t-main
                   locks_key_left: 0039300000
                  locks_key_right: 0039300000
               locks_table_schema: test
                 locks_table_name: t
      locks_table_dictionary_name: main
      2 rows in set (0.00 sec)
      

      ====
      Lock certainly set, but it is not exclusive or somehow preventing writes. This is same as if I will just use SELECT:
      ====

      session1> rollback;
      Query OK, 0 rows affected (0.00 sec)
      
      session1> begin;
      Query OK, 0 rows affected (0.00 sec)
      
      session1> select * from t where id=12345;
      +-------+--------+
      | id | f |
      +-------+--------+
      | 12345 | value1 |
      +-------+--------+
      1 row in set (0.00 sec)
      

      And in another session:

      session2> begin;
      Query OK, 0 rows affected (0.00 sec)
      
      session2> update t set f='value3' where id=12345;
      Query OK, 1 row affected (0.00 sec)
      Rows matched: 1 Changed: 1 Warnings: 0
      
      session2> select * from information_schema.tokudb_locks\G
      *************************** 1. row ***************************
                     locks_trx_id: 514
            locks_mysql_thread_id: 238
                      locks_dname: ./test/t-main
                   locks_key_left: ff39300000
                  locks_key_right: 0139300000
               locks_table_schema: test
                 locks_table_name: t
      locks_table_dictionary_name: main
      *************************** 2. row ***************************
                     locks_trx_id: 514
            locks_mysql_thread_id: 238
                      locks_dname: ./test/t-main
                   locks_key_left: 0039300000
                  locks_key_right: 0039300000
               locks_table_schema: test
                 locks_table_name: t
      locks_table_dictionary_name: main
      2 rows in set (0.00 sec)
      

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              jun.yuan Jun Yuan (Inactive)
              Reporter:
              sveta.smirnova Sveta Smirnova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: