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