1 --source include/have_innodb_plugin.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';