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
|
# Establish connection con1 (user=root)
# Establish connection con2 (user=root)
# Establish connection con3 (user=root)
# Drop test table
drop table if exists t;
# Create test table
create table t(a INT PRIMARY KEY, b INT) engine=InnoDB;
# Insert two rows to test table
insert into t values(2,1);
insert into t values(1,2);
# Switch to connection con1
BEGIN;
SELECT b FROM t WHERE a=1 FOR UPDATE;
b
2
# Switch to connection con2
BEGIN;
SELECT b FROM t WHERE a=2 FOR UPDATE;
b
1
# Switch to connection con1
SELECT b FROM t WHERE a=2 FOR UPDATE;
# Switch to connection con2
SELECT b FROM t WHERE a=1 FOR UPDATE;
# Switch to connection con1
b
1
# Switch to connection con2
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
# Switch to connection con3
1
# Drop test table
drop table t;
|