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
|
set @prior_rocksdb_lock_wait_timeout = @@rocksdb_lock_wait_timeout;
set @prior_rocksdb_deadlock_detect = @@rocksdb_deadlock_detect;
set global rocksdb_lock_wait_timeout = 100000;
set global rocksdb_deadlock_detect = ON;
create table t (i int primary key);
create table r1 (id int primary key, value int);
insert into r1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
create table r2 like r1;
insert into r2 select * from r1;
begin;
update r2 set value=100 where id=9;
begin;
update r1 set value=100 where id=8;
select * from r2 for update;;
select * from r1 for update;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
rollback;
id value
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
rollback;
begin;
insert into t values (1);
begin;
insert into t values (2);
begin;
insert into t values (3);
select * from t where i = 2 for update;
select * from t where i = 3 for update;
select * from t;
i
3
insert into t values (4), (1);
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
# Statement should be rolled back
select * from t;
i
3
rollback;
i
rollback;
i
rollback;
set global rocksdb_lock_wait_timeout = @prior_rocksdb_lock_wait_timeout;
set global rocksdb_deadlock_detect = @prior_rocksdb_deadlock_detect;
drop table t,r1,r2;
|
