2
# Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout
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);
13
SELECT b FROM t2 WHERE b=7 FOR UPDATE;
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
31
# Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT
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);
40
select * from t1 where b=5 for update;
43
insert ignore into t1 (b) select a as b from t1;
44
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
48
set autocommit=default;
51
# Bug #37183 insert ignore into .. select ... hangs
52
# after deadlock was encountered
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;
58
update t1 set v=id*2 where id=1;
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
69
# Bug#41756 Strange error messages about locks from InnoDB
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.
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);
97
# Demonstrate that for the SELECT statement
98
# used later in the test JT_EQ_REF access method is used.
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;
117
possible_keys PRIMARY
144
select_type UNION RESULT
154
# Demonstrate that the reported SELECT statement
155
# no longer produces warnings.
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;
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.
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;
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.
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);
190
# Record (2, null) is locked. This is actually unnecessary,
191
# because the previous select returned no rows.
192
# Just demonstrate the effect.
195
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
198
# Switching to connection default
200
# Show that the original contents of t1 is intact:
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.
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
261
# Switching to connection con1
263
# Since there is another distinct record in the derived table
264
# the previous matching record in t1 -- (2,null) -- was unlocked.
266
# We will need the contents of the table again.
292
# Switching to connection default
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.
303
# Verify that JT_EQ_REF is used.
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;
321
possible_keys PRIMARY
348
select_type UNION RESULT
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;
362
# Switching to connection con1
364
# We should not be able to delete record (3,1) from t1,
365
# (previously it was possible).
367
delete from t1 where a=3;
368
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
369
# Switching to connection default
371
set @@session.tx_isolation=default;
added
removed
mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result
