In this blog post, we’ll look at MyRocks and the LOCK IN SHARE MODE.
Those who attended the March 30th webinar “MyRocks Troubleshooting” might remember our discussion with Yoshinori on LOCK IN SHARE MODE.
I did more tests, and I can confirm that his words are true: LOCK IN SHARE MODE works in MyRocks.
This quick example demonstrates this. The initial setup:
1 2 3 4 5 6 7 | CREATE TABLE t ( id int(11) NOT NULL, f varchar(100) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=ROCKSDB; insert into t values(12345, 'value1'), (54321, 'value2'); |
In session 1:
1 2 3 4 5 6 7 8 9 10 | session 1> begin; Query OK, 0 rows affected (0.00 sec) session 1> select * from t where id=12345 lock in share mode; +-------+--------+ | id | f | +-------+--------+ | 12345 | value1 | +-------+--------+ 1 row in set (0.01 sec) |
In session 2:
1 2 3 4 5 | session 2> begin; Query OK, 0 rows affected (0.00 sec) session 2> update t set f='value3' where id=12345; ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
However, in the webinar I wanted to remind everyone about the differences between LOCK IN SHARE MODE and FOR UPDATE. To do so, I added the former to my “session 2” test for the webinar. Once I did, it ignores the lock set in “session 1”. I can update a row and commit:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | session 2> select * from t where id=12345 lock in share mode; +-------+--------+ | id | f | +-------+--------+ | 12345 | value1 | +-------+--------+ 1 row in set (0.00 sec) session 2> update t set f='value3' where id=12345; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 session 2> commit; Query OK, 0 rows affected (0.02 sec) |
I reported this behavior here, and also at Percona Jira bugs database: MYR-107. In Facebook, this bug is already fixed.
This test clearly demonstrates that it is fixed in Facebook. In “session 1”:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | session1> CREATE TABLE `t` ( -> `id` int(11) NOT NULL, -> `f` varchar(100) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=ROCKSDB; Query OK, 0 rows affected (0.00 sec) session1> insert into t values(12345, 'value1'), (54321, 'value2'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 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) |
And now in another session:
1 2 3 4 5 6 7 8 9 10 11 12 13 | session2> begin; Query OK, 0 rows affected (0.00 sec) session2> select * from t where id=12345 lock in share mode; +-------+--------+ | id | f | +-------+--------+ | 12345 | value1 | +-------+--------+ 1 row in set (0.00 sec) session2> update t set f='value3' where id=12345; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t.PRIMARY |
If you want to test the fix with the Facebook MySQL build, you need to update submodules to download the patch: git submodule update.
