2
#Bug#11759688 52020: InnoDB can still deadlock
3
#on just INSERT...ON DUPLICATE KEY
4
#a.k.a. Bug#7975 deadlock without any locking, simple select and update
6
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
7
INSERT INTO t1 VALUES(3,1);
9
SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
10
INSERT INTO t1 VALUES(3,2);
11
SET DEBUG_SYNC='now WAIT_FOR insert1';
12
SELECT * FROM t1 LOCK IN SHARE MODE;
15
SELECT * FROM t1 FOR UPDATE;
16
SET DEBUG_SYNC='now SIGNAL select1';
17
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
18
INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
19
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
21
SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
22
REPLACE INTO t1 VALUES(3,4);
23
SET DEBUG_SYNC='now WAIT_FOR insert2';
27
SELECT * FROM t1 LOCK IN SHARE MODE;
28
SET DEBUG_SYNC='now SIGNAL select2';
29
SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
30
INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
33
SET DEBUG_SYNC='now WAIT_FOR insert3';
34
SELECT b FROM t1 LOCK IN SHARE MODE;
35
SET DEBUG_SYNC='now SIGNAL select3';
38
SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
39
LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
40
SET DEBUG_SYNC='now WAIT_FOR insert4';
41
SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
44
SELECT b FROM t1 WHERE a=3 FOR UPDATE;
45
SET DEBUG_SYNC='now SIGNAL select4';
48
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
49
SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
50
LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
51
SET DEBUG_SYNC='now WAIT_FOR insert5';
55
SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
58
SELECT * FROM t1 WHERE a=3 FOR UPDATE;
59
SET DEBUG_SYNC='now SIGNAL select5';
62
SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
63
LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
64
SET DEBUG_SYNC='now WAIT_FOR insert6';
70
SELECT a,b FROM t1 LOCK IN SHARE MODE;
71
SET DEBUG_SYNC='now SIGNAL select6';
76
SET DEBUG_SYNC='RESET';