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

[Sveta Smirnova]

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)

[George Ormond Lorch III (gl-az)]

Tracked in https://jira.percona.com/browse/TDB-9

[George Ormond Lorch III (gl-az)]

Tracked in https://jira.percona.com/browse/TDB-9, marking it as opinion here as there is no other accurate matching state.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1793