1
--source include/have_innodb.inc
2
--source include/have_debug_sync.inc
5
--echo #Bug#11759688 52020: InnoDB can still deadlock
6
--echo #on just INSERT...ON DUPLICATE KEY
7
--echo #a.k.a. Bug#7975 deadlock without any locking, simple select and update
10
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
12
INSERT INTO t1 VALUES(3,1);
14
connect (con1,localhost,root,,);
15
connect (con2,localhost,root,,);
19
# normal INSERT of a duplicate should only S-lock the existing record (3,1)
20
SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
22
INSERT INTO t1 VALUES(3,2);
25
SET DEBUG_SYNC='now WAIT_FOR insert1';
26
# this should S-lock (3,1); no conflict
27
SELECT * FROM t1 LOCK IN SHARE MODE;
28
# this should X-lock (3,1), conflicting with con1
30
SELECT * FROM t1 FOR UPDATE;
33
# Check that the above SELECT is blocked
35
select count(*) = 1 from information_schema.processlist
36
where state = 'Sending data' and
37
info = 'SELECT * FROM t1 FOR UPDATE';
38
--source include/wait_condition.inc
39
SET DEBUG_SYNC='now SIGNAL select1';
44
# We are still holding an S-lock on (3,1) after the failed INSERT.
45
# The following will upgrade it to an X-lock, causing a deadlock.
46
# InnoDB should resolve the deadlock by aborting the blocked SELECT.
47
INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
50
--error ER_LOCK_DEADLOCK
55
SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
57
REPLACE INTO t1 VALUES(3,4);
60
SET DEBUG_SYNC='now WAIT_FOR insert2';
63
SELECT * FROM t1 LOCK IN SHARE MODE;
66
# Check that the above SELECT is blocked because of X lock.
68
select count(*) = 1 from information_schema.processlist
69
where state = 'Sending data' and
70
info = 'SELECT * FROM t1 LOCK IN SHARE MODE';
71
--source include/wait_condition.inc
72
SET DEBUG_SYNC='now SIGNAL select2';
77
SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
79
INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
83
SET DEBUG_SYNC='now WAIT_FOR insert3';
85
SELECT b FROM t1 LOCK IN SHARE MODE;
88
# Check that the above SELECT is blocked because of X lock.
90
select count(*) = 1 from information_schema.processlist
91
where state = 'Sending data' and
92
info = 'SELECT b FROM t1 LOCK IN SHARE MODE';
93
--source include/wait_condition.inc
94
SET DEBUG_SYNC='now SIGNAL select3';
101
SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
103
LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
106
SET DEBUG_SYNC='now WAIT_FOR insert4';
107
# this should S-lock (3,1); no conflict
108
SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
109
# this should X-lock (3,1), conflicting with con1
111
SELECT b FROM t1 WHERE a=3 FOR UPDATE;
114
# Check that the above SELECT is blocked
116
select count(*) = 1 from information_schema.processlist
117
where state = 'statistics' and
118
info = 'SELECT b FROM t1 WHERE a=3 FOR UPDATE';
119
--source include/wait_condition.inc
120
SET DEBUG_SYNC='now SIGNAL select4';
128
SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
130
LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
133
SET DEBUG_SYNC='now WAIT_FOR insert5';
135
# this should S-lock; no conflict
136
SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
137
# this should X-lock, conflicting with the S-lock of the IGNORE in con1
139
SELECT * FROM t1 WHERE a=3 FOR UPDATE;
142
# Check that the above SELECT is blocked
144
select count(*) = 1 from information_schema.processlist
145
where state = 'statistics' and
146
info = 'SELECT * FROM t1 WHERE a=3 FOR UPDATE';
147
--source include/wait_condition.inc
148
SET DEBUG_SYNC='now SIGNAL select5';
156
SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
158
LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
161
SET DEBUG_SYNC='now WAIT_FOR insert6';
163
# this should conflict with the X-lock acquired by the REPLACE
165
SELECT a,b FROM t1 LOCK IN SHARE MODE;
168
# Check that the above SELECT is blocked
170
select count(*) = 1 from information_schema.processlist
171
where state = 'Sending data' and
172
info = 'SELECT a,b FROM t1 LOCK IN SHARE MODE';
173
--source include/wait_condition.inc
174
SET DEBUG_SYNC='now SIGNAL select6';
185
SET DEBUG_SYNC='RESET';