Debian Codegraph @

mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result

raw
1 #
2 # Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout
3 # without error
4 #
5 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)) ENGINE=InnoDB;
6 INSERT INTO t1 (a,b) VALUES (1070109,99);
7 CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB;
8 INSERT INTO t2 (b,a) VALUES (7,1070109);
9 SELECT * FROM t1;
10 a b
11 1070109 99
12 BEGIN;
13 SELECT b FROM t2 WHERE b=7 FOR UPDATE;
14 b
15 7
16 CONNECT addconroot, localhost, root,,;
17 connection addconroot;
18 BEGIN;
19 SELECT b FROM t2 WHERE b=7 FOR UPDATE;
20 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
21 INSERT IGNORE INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7));
22 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
23 UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7);
24 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
25 DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7);
26 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
27 SELECT * FROM t1;
28 a b
29 1070109 99
30 connection default;
31 disconnect addconroot;
32 DROP TABLE t2, t1;
33 # End of 5.0 tests
34 #
35 # Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT
36 # FOR UPDATE
37 #
38 create table t1 (a int primary key auto_increment,
39 b int, index(b)) engine=innodb;
40 insert into t1 (b) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
41 set autocommit=0;
42 begin;
43 select * from t1 where b=5 for update;
44 a b
45 5 5
46 connect con1, localhost, root,,;
47 connection con1;
48 insert ignore into t1 (b) select a as b from t1;
49 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
50 connection default;
51 # Cleanup
52 #
53 disconnect con1;
54 commit;
55 set autocommit=default;
56 drop table t1;
57 #
58 # Bug #37183 insert ignore into .. select ... hangs
59 # after deadlock was encountered
60 #
61 connect con1,localhost,root,,;
62 create table t1(id int primary key,v int)engine=innodb;
63 insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
64 create table t2 like t1;
65 connection con1;
66 begin;
67 update t1 set v=id*2 where id=1;
68 connection default;
69 begin;
70 update t1 set v=id*2 where id=2;
71 connection con1;
72 update t1 set v=id*2 where id=2;
73 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
74 connection default;
75 insert ignore into t2 select * from t1 where id=1;
76 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
77 rollback;
78 connection con1;
79 rollback;
80 connection default;
81 disconnect con1;
82 drop table t1, t2;
83 #
84 # Bug#41756 Strange error messages about locks from InnoDB
85 #
86 # In the default transaction isolation mode,
87 # handler::unlock_row() in InnoDB does nothing.
88 # Thus in order to reproduce the condition that led to the
89 # warning, one needs to relax isolation by either
90 # setting a weaker transaction_isolation value, or by turning on
91 # the unsafe replication switch.
92 set @@session.transaction_isolation="read-committed";
93 # Prepare data. We need a table with a unique index,
94 # for join_read_key to be used. The other column
95 # allows to control what passes WHERE clause filter.
96 create table t1 (a int primary key, b int) engine=innodb;
97 # Let's make sure t1 has sufficient amount of rows
98 # to exclude JT_ALL access method when reading it,
99 # i.e. make sure that JT_EQ_REF(a) is always preferred.
100 insert into t1 values (1,1), (2,null), (3,1), (4,1),
101 (5,1), (6,1), (7,1), (8,1), (9,1), (10,1),
102 (11,1), (12,1), (13,1), (14,1), (15,1),
103 (16,1), (17,1), (18,1), (19,1), (20,1);
104 #
105 # Demonstrate that for the SELECT statement
106 # used later in the test JT_EQ_REF access method is used.
107 #
108 explain
109 select 1 from t1 natural join (select 2 as a, 1 as b union all
110 select 2 as a, 2 as b) as t2 for update;
111 id 1
112 select_type PRIMARY
113 table <derived2>
114 type ALL
115 possible_keys NULL
116 key NULL
117 key_len NULL
118 ref NULL
119 rows 2
120 Extra
121 id 1
122 select_type PRIMARY
123 table t1
124 type eq_ref
125 possible_keys PRIMARY
126 key PRIMARY
127 key_len 4
128 ref t2.a
129 rows 1
130 Extra Using where
131 id 2
132 select_type DERIVED
133 table NULL
134 type NULL
135 possible_keys NULL
136 key NULL
137 key_len NULL
138 ref NULL
139 rows NULL
140 Extra No tables used
141 id 3
142 select_type UNION
143 table NULL
144 type NULL
145 possible_keys NULL
146 key NULL
147 key_len NULL
148 ref NULL
149 rows NULL
150 Extra No tables used
151 #
152 # Demonstrate that the reported SELECT statement
153 # no longer produces warnings.
154 #
155 select 1 from t1 natural join (select 2 as a, 1 as b union all
156 select 2 as a, 2 as b) as t2 for update;
157 1
158 commit;
159 #
160 # Demonstrate that due to lack of inter-sweep "reset" function,
161 # we keep some non-matching records locked, even though we know
162 # we could unlock them.
163 # To do that, show that if there is only one distinct value
164 # for a in t2 (a=2), we will keep record (2,null) in t1 locked.
165 # But if we add another value for "a" to t2, say 6,
166 # join_read_key cache will be pruned at least once,
167 # and thus record (2, null) in t1 will get unlocked.
168 #
169 begin;
170 select 1 from t1 natural join (select 2 as a, 1 as b union all
171 select 2 as a, 2 as b) as t2 for update;
172 1
173 connect con1,localhost,root,,;
174 connection con1;
175 # We should be able to delete all records from t1 except (2, null),
176 # since they were not locked.
177 begin;
178 # Delete in series of 3 records so that full scan
179 # is not used and we're not blocked on record (2,null)
180 delete from t1 where a in (1,3,4);
181 delete from t1 where a in (5,6,7);
182 delete from t1 where a in (8,9,10);
183 delete from t1 where a in (11,12,13);
184 delete from t1 where a in (14,15,16);
185 delete from t1 where a in (17,18);
186 delete from t1 where a in (19,20);
187 #
188 # Record (2, null) is locked. This is actually unnecessary,
189 # because the previous select returned no rows.
190 # Just demonstrate the effect.
191 #
192 delete from t1;
193 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
194 rollback;
195 connection default;
196 #
197 # Show that the original contents of t1 is intact:
198 select * from t1;
199 a b
200 1 1
201 2 NULL
202 3 1
203 4 1
204 5 1
205 6 1
206 7 1
207 8 1
208 9 1
209 10 1
210 11 1
211 12 1
212 13 1
213 14 1
214 15 1
215 16 1
216 17 1
217 18 1
218 19 1
219 20 1
220 commit;
221 #
222 # Have a one more record in t2 to show that
223 # if join_read_key cache is purned, the current
224 # row under the cursor is unlocked (provided, this row didn't
225 # match the partial WHERE clause, of course).
226 # Sic: the result of this test dependent on the order of retrieval
227 # of records --echo # from the derived table, if !
228 # We use DELETE to disable the JOIN CACHE. This DELETE modifies no
229 # records. It also should leave no InnoDB row locks.
230 #
231 begin;
232 delete t1.* from t1 natural join (select 2 as a, 2 as b union all
233 select 0 as a, 0 as b) as t2;
234 # Demonstrate that nothing was deleted form t1
235 select * from t1;
236 a b
237 1 1
238 2 NULL
239 3 1
240 4 1
241 5 1
242 6 1
243 7 1
244 8 1
245 9 1
246 10 1
247 11 1
248 12 1
249 13 1
250 14 1
251 15 1
252 16 1
253 17 1
254 18 1
255 19 1
256 20 1
257 connection con1;
258 begin;
259 # Since there is another distinct record in the derived table
260 # the previous matching record in t1 -- (2,null) -- was unlocked.
261 delete from t1;
262 # We will need the contents of the table again.
263 rollback;
264 select * from t1;
265 a b
266 1 1
267 2 NULL
268 3 1
269 4 1
270 5 1
271 6 1
272 7 1
273 8 1
274 9 1
275 10 1
276 11 1
277 12 1
278 13 1
279 14 1
280 15 1
281 16 1
282 17 1
283 18 1
284 19 1
285 20 1
286 commit;
287 connection default;
288 rollback;
289 begin;
290 #
291 # Before this patch, we could wrongly unlock a record
292 # that was cached and later used in a join. Demonstrate that
293 # this is no longer the case.
294 # Sic: this test is also order-dependent (i.e. the
295 # the bug would show up only if the first record in the union
296 # is retreived and processed first.
297 #
298 # Verify that JT_EQ_REF is used.
299 explain
300 select 1 from t1 natural join (select 3 as a, 2 as b union all
301 select 3 as a, 1 as b) as t2 for update;
302 id 1
303 select_type PRIMARY
304 table <derived2>
305 type ALL
306 possible_keys NULL
307 key NULL
308 key_len NULL
309 ref NULL
310 rows 2
311 Extra
312 id 1
313 select_type PRIMARY
314 table t1
315 type eq_ref
316 possible_keys PRIMARY
317 key PRIMARY
318 key_len 4
319 ref t2.a
320 rows 1
321 Extra Using where
322 id 2
323 select_type DERIVED
324 table NULL
325 type NULL
326 possible_keys NULL
327 key NULL
328 key_len NULL
329 ref NULL
330 rows NULL
331 Extra No tables used
332 id 3
333 select_type UNION
334 table NULL
335 type NULL
336 possible_keys NULL
337 key NULL
338 key_len NULL
339 ref NULL
340 rows NULL
341 Extra No tables used
342 # Lock the record.
343 select 1 from t1 natural join (select 3 as a, 2 as b union all
344 select 3 as a, 1 as b) as t2 for update;
345 1
346 1
347 connection con1;
348 #
349 # We should not be able to delete record (3,1) from t1,
350 # (previously it was possible).
351 #
352 delete from t1 where a=3;
353 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
354 connection default;
355 commit;
356 disconnect con1;
357 set @@session.transaction_isolation=default;
358 drop table t1;
359 #
360 # End of 5.1 tests
361 #