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