Debian Codegraph @

mysql-test/suite/innodb/t/innodb_replace.test

raw
1 --source include/have_debug_sync.inc
2
3 --echo #
4 --echo #Bug#11759688 52020: InnoDB can still deadlock
5 --echo #on just INSERT...ON DUPLICATE KEY
6 --echo #a.k.a. Bug#7975 deadlock without any locking, simple select and update
7 --echo #
8
9 CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
10
11 INSERT INTO t1 VALUES(3,1);
12
13 connect (con1,localhost,root,,);
14 connect (con2,localhost,root,,);
15 connection con1;
16
17 BEGIN;
18 # normal INSERT of a duplicate should only S-lock the existing record (3,1)
19 SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
20 --send
21 INSERT INTO t1 VALUES(3,2);
22
23 connection default;
24 SET DEBUG_SYNC='now WAIT_FOR insert1';
25 # this should S-lock (3,1); no conflict
26 SELECT * FROM t1 LOCK IN SHARE MODE;
27 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
28 # this should X-lock (3,1), conflicting with con1
29 --send
30 SELECT * FROM t1 FOR UPDATE;
31
32 connection con2;
33 # Check that the above SELECT is blocked
34 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
35 SET DEBUG_SYNC='now SIGNAL select1';
36
37 connection con1;
38 --error ER_DUP_ENTRY
39 reap;
40 # We are still holding an S-lock on (3,1) after the failed INSERT.
41 # The following will upgrade it to an X-lock, which used to cause a
42 # deadlock, but with the new "by-pasing blocked by us" heuristic, it
43 # should succeed, without aborting the blocked SELECT.
44 INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
45 COMMIT;
46
47 connection default;
48 reap;
49 connection con1;
50
51 SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
52 --send
53 REPLACE INTO t1 VALUES(3,4);
54
55 connection default;
56 SET DEBUG_SYNC='now WAIT_FOR insert2';
57 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
58 SELECT * FROM t1;
59 --send
60 SELECT * FROM t1 LOCK IN SHARE MODE;
61
62 connection con2;
63 # Check that the above SELECT is blocked because of X lock.
64 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
65 SET DEBUG_SYNC='now SIGNAL select2';
66
67 connection con1;
68 reap;
69
70 SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
71 --send
72 INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
73
74 connection default;
75 reap;
76 SET DEBUG_SYNC='now WAIT_FOR insert3';
77 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
78 --send
79 SELECT b FROM t1 LOCK IN SHARE MODE;
80
81 connection con2;
82 # Check that the above SELECT is blocked because of X lock.
83 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
84 SET DEBUG_SYNC='now SIGNAL select3';
85
86 connection default;
87 reap;
88
89 connection con1;
90 reap;
91 SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
92 --send
93 LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
94
95 connection default;
96 SET DEBUG_SYNC='now WAIT_FOR insert4';
97 # this should S-lock (3,1); no conflict
98 SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
99 # this should X-lock (3,1), conflicting with con1
100 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
101 --send
102 SELECT b FROM t1 WHERE a=3 FOR UPDATE;
103
104 connection con2;
105 # Check that the above SELECT is blocked
106 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
107 SET DEBUG_SYNC='now SIGNAL select4';
108
109 connection default;
110 reap;
111
112 connection con1;
113 --error ER_DUP_ENTRY
114 reap;
115 SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
116 --send
117 LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
118
119 connection default;
120 SET DEBUG_SYNC='now WAIT_FOR insert5';
121 SELECT * FROM t1;
122 # this should S-lock; no conflict
123 SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
124 # this should X-lock, conflicting with the S-lock of the IGNORE in con1
125 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
126 --send
127 SELECT * FROM t1 WHERE a=3 FOR UPDATE;
128
129 connection con2;
130 # Check that the above SELECT is blocked
131 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
132 SET DEBUG_SYNC='now SIGNAL select5';
133
134 connection con1;
135 reap;
136 connection default;
137 reap;
138
139 connection con1;
140 SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
141 --send
142 LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
143
144 connection default;
145 SET DEBUG_SYNC='now WAIT_FOR insert6';
146 SELECT * FROM t1;
147 # this should conflict with the X-lock acquired by the REPLACE
148 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
149 --send
150 SELECT a,b FROM t1 LOCK IN SHARE MODE;
151
152 connection con2;
153 # Check that the above SELECT is blocked
154 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
155 SET DEBUG_SYNC='now SIGNAL select6';
156
157 connection con1;
158 reap;
159 connection default;
160 reap;
161
162 disconnect con1;
163 disconnect con2;
164
165 connection default;
166 SET DEBUG_SYNC='RESET';
167 DROP TABLE t1;
168
169 # Test case for TEMPORARY TABLE, with several unique indexes
170 START TRANSACTION;
171
172 CREATE TEMPORARY TABLE t1 (a INT, b CHAR(10), d VARCHAR(100), c INT, UNIQUE INDEX(a), UNIQUE INDEX(b), UNIQUE INDEX(d)) ENGINE = InnoDB;
173
174 INSERT INTO t1 VALUES(2, 'abcde', 'qwerty', 200);
175
176 REPLACE INTO t1 VALUES(2, 'QWERTY', 'ZXCVBB', 300);
177
178 SELECT * FROM t1;
179
180 COMMIT;
181
182 DROP TABLE t1;
183
184 --echo #
185 --echo # Bug#22884631 FAILING ASSERTION: TRX->DUPLICATES == 0
186 --echo #
187 --connect (con1,localhost,root,,)
188 --connect (con2,localhost,root,,)
189
190 SELECT @@table_open_cache_instances;
191
192 CREATE TABLE t1(a INT);
193 CREATE TABLE t2(a INT);
194 INSERT INTO t1 VALUES(1);
195 INSERT INTO t2 VALUES(2);
196
197 --echo # Connection con1
198 connection con1;
199 INSERT INTO t1 VALUES(3);
200 SET DEBUG_SYNC = 'RESET';
201 SET DEBUG_SYNC = 'ib_after_row_insert WAIT_FOR go_ahead';
202 --send REPLACE INTO t2 VALUES(3)
203
204 --echo # Connection con2
205 connection con2;
206 --error ER_WRONG_VALUE_COUNT_ON_ROW
207 INSERT INTO t1 VALUES(70, 80);
208 SET DEBUG_SYNC= 'now SIGNAL go_ahead';
209
210 --echo # Connection con1
211 connection con1;
212 --reap
213 DROP TABLE t1;
214 DROP TABLE t2;
215
216 SET DEBUG_SYNC= 'RESET';
217
218 disconnect con1;
219 disconnect con2;