1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
create table t1 (pk int not null primary key) engine=rocksdb;
insert into t1 values (1),(2),(3);
set autocommit=0;
begin;
select * from t1 where pk=1 for update;
pk
1
connect con1,localhost,root,,;
connection con1;
call mtr.add_suppression("Got snapshot conflict errors");
### Connection con1
set @@rocksdb_lock_wait_timeout=500;
set autocommit=0;
begin;
select * from t1 where pk=1 for update;;
connection default;
### Connection default
rollback;
connection con1;
pk
1
rollback;
connection default;
begin;
select * from t1 where pk=1 for update;
pk
1
connection con1;
### Connection con1
set @@rocksdb_lock_wait_timeout=2;
set autocommit=0;
begin;
select * from t1 where pk=1 for update;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
connection default;
rollback;
set autocommit=1;
connection con1;
drop table t1;
connection default;
#
# Now, test what happens if another transaction modified the record and committed
#
CREATE TABLE t1 (
id int primary key,
value int
) engine=rocksdb collate latin1_bin;
insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
connection con1;
BEGIN;
SELECT * FROM t1 WHERE id=3;
id value
3 3
connection default;
BEGIN;
UPDATE t1 SET value=30 WHERE id=3;
COMMIT;
connection con1;
SELECT * FROM t1 WHERE id=3 FOR UPDATE;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict)
ROLLBACK;
disconnect con1;
connection default;
drop table t1;
|