Debian Codegraph @

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

raw
1
2 --echo #
3 --echo # Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout
4 --echo # without error
5 --echo #
6
7 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)) ENGINE=InnoDB;
8
9 INSERT INTO t1 (a,b) VALUES (1070109,99);
10
11 CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB;
12
13 INSERT INTO t2 (b,a) VALUES (7,1070109);
14
15 SELECT * FROM t1;
16
17 BEGIN;
18
19 SELECT b FROM t2 WHERE b=7 FOR UPDATE;
20
21 CONNECT (addconroot, localhost, root,,);
22 CONNECTION addconroot;
23
24 BEGIN;
25
26 --error ER_LOCK_WAIT_TIMEOUT
27 SELECT b FROM t2 WHERE b=7 FOR UPDATE;
28
29 --error ER_LOCK_WAIT_TIMEOUT
30 INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7));
31
32 --error ER_LOCK_WAIT_TIMEOUT
33 UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7);
34
35 --error ER_LOCK_WAIT_TIMEOUT
36 DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7);
37
38 SELECT * FROM t1;
39
40 CONNECTION default;
41 DISCONNECT addconroot;
42
43 DROP TABLE t2, t1;
44
45 --echo # End of 5.0 tests
46
47 --echo #
48 --echo # Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT
49 --echo # FOR UPDATE
50 --echo #
51 --disable_warnings
52 drop table if exists t1;
53 --enable_warnings
54 create table t1 (a int primary key auto_increment,
55 b int, index(b)) engine=innodb;
56 insert into t1 (b) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
57 set autocommit=0;
58 begin;
59 select * from t1 where b=5 for update;
60 connect (con1, localhost, root,,);
61 connection con1;
62 --error ER_LOCK_WAIT_TIMEOUT
63 insert ignore into t1 (b) select a as b from t1;
64 connection default;
65 --echo # Cleanup
66 --echo #
67 disconnect con1;
68 commit;
69 set autocommit=default;
70 drop table t1;
71
72 --echo #
73 --echo # Bug #37183 insert ignore into .. select ... hangs
74 --echo # after deadlock was encountered
75 --echo #
76 connect (con1,localhost,root,,);
77 create table t1(id int primary key,v int)engine=innodb;
78 insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
79 create table t2 like t1;
80
81 --connection con1
82 begin;
83 update t1 set v=id*2 where id=1;
84
85 --connection default
86 begin;
87 update t1 set v=id*2 where id=2;
88
89 --connection con1
90 --error 1205
91 update t1 set v=id*2 where id=2;
92
93 --connection default
94 --error 1205
95 insert ignore into t2 select * from t1 where id=1;
96 rollback;
97
98 --connection con1
99 rollback;
100
101 --connection default
102 disconnect con1;
103 drop table t1, t2;
104
105 --echo #
106 --echo # Bug#41756 Strange error messages about locks from InnoDB
107 --echo #
108 --echo # In the default transaction isolation mode, ha_innodb::unlock_row()
109 --echo # does nothing. Thus in order to reproduce the condition
110 --echo # that led to the warning, one needs to relax isolation by
111 --echo # setting a weaker transaction_isolation value
112 set @@session.transaction_isolation="read-committed";
113
114 --echo # Prepare data. We need a table with a unique index,
115 --echo # for join_read_key to be used. The other column
116 --echo # allows to control what passes WHERE clause filter.
117 create table t1 (a int primary key, b int) engine=innodb stats_persistent=0;
118 --echo # Let's make sure t1 has sufficient amount of rows
119 --echo # to exclude JT_ALL access method when reading it,
120 --echo # i.e. make sure that JT_EQ_REF(a) is always preferred.
121 insert into t1 values (1,1), (2,null), (3,1), (4,1),
122 (5,1), (6,1), (7,1), (8,1), (9,1), (10,1),
123 (11,1), (12,1), (13,1), (14,1), (15,1),
124 (16,1), (17,1), (18,1), (19,1), (20,1);
125 --echo #
126 --echo # Demonstrate that for the SELECT statement
127 --echo # used later in the test JT_EQ_REF access method is used.
128 --echo #
129 --vertical_results
130 explain
131 select 1 from t1 natural join (select 2 as a, 1 as b union all
132 select 2 as a, 2 as b) as t2 for update;
133 --horizontal_results
134 --echo #
135 --echo # Demonstrate that the reported SELECT statement
136 --echo # no longer produces warnings.
137 --echo #
138 select 1 from t1 natural join (select 2 as a, 1 as b union all
139 select 2 as a, 2 as b) as t2 for update;
140 commit;
141 --echo #
142 --echo # Demonstrate that due to lack of inter-sweep "reset" function,
143 --echo # we keep some non-matching records locked, even though we know
144 --echo # we could unlock them.
145 --echo # To do that, show that if there is only one distinct value
146 --echo # for a in t2 (a=2), we will keep record (2,null) in t1 locked.
147 --echo # But if we add another value for "a" to t2, say 6,
148 --echo # join_read_key cache will be pruned at least once,
149 --echo # and thus record (2, null) in t1 will get unlocked.
150 --echo #
151 begin;
152 select 1 from t1 natural join (select 2 as a, 1 as b union all
153 select 2 as a, 2 as b) as t2 for update;
154 connect (con1,localhost,root,,);
155 --echo #
156 --echo # Switching to connection con1
157 connection con1;
158 --echo # We should be able to delete all records from t1 except (2, null),
159 --echo # since they were not locked.
160 begin;
161 --echo # Delete in series of 3 records so that full scan
162 --echo # is not used and we're not blocked on record (2,null)
163 --skip_if_hypergraph # Does not support indexes.
164 delete from t1 where a in (1,3,4);
165 --skip_if_hypergraph # Does not support indexes.
166 delete from t1 where a in (5,6,7);
167 --skip_if_hypergraph # Does not support indexes.
168 delete from t1 where a in (8,9,10);
169 --skip_if_hypergraph # Does not support indexes.
170 delete from t1 where a in (11,12,13);
171 --skip_if_hypergraph # Does not support indexes.
172 delete from t1 where a in (14,15,16);
173 --skip_if_hypergraph # Does not support indexes.
174 delete from t1 where a in (17,18);
175 --skip_if_hypergraph # Does not support indexes.
176 delete from t1 where a in (19,20);
177 --echo #
178 --echo # Record (2, null) is locked. This is actually unnecessary,
179 --echo # because the previous select returned no rows.
180 --echo # Just demonstrate the effect.
181 --echo #
182 --skip_if_hypergraph # Does not always lock the given row; depends on the plan.
183 --error ER_LOCK_WAIT_TIMEOUT
184 delete from t1;
185 rollback;
186 --echo #
187 --echo # Switching to connection default
188 connection default;
189 --echo #
190 --echo # Show that the original contents of t1 is intact:
191 select * from t1;
192 commit;
193 --echo #
194 --echo # Have a one more record in t2 to show that
195 --echo # if join_read_key cache is purned, the current
196 --echo # row under the cursor is unlocked (provided, this row didn't
197 --echo # match the partial WHERE clause, of course).
198 --echo # Sic: the result of this test dependent on the order of retrieval
199 --echo # of records --echo # from the derived table, if !
200 --echo # We use DELETE to disable the JOIN CACHE. This DELETE modifies no
201 --echo # records. It also should leave no InnoDB row locks.
202 --echo #
203 begin;
204 delete t1.* from t1 natural join (select 2 as a, 2 as b union all
205 select 0 as a, 0 as b) as t2;
206 --echo # Demonstrate that nothing was deleted form t1
207 select * from t1;
208 --echo #
209 --echo # Switching to connection con1
210 connection con1;
211 begin;
212 --echo # Since there is another distinct record in the derived table
213 --echo # the previous matching record in t1 -- (2,null) -- was unlocked.
214 --skip_if_hypergraph # Could get lock timeout, depending on the plan of the
215 # multi-table delete statement above.
216 delete from t1;
217 --echo # We will need the contents of the table again.
218 rollback;
219 select * from t1;
220 commit;
221 --echo #
222 --echo # Switching to connection default
223 connection default;
224 rollback;
225 begin;
226 --echo #
227 --echo # Before this patch, we could wrongly unlock a record
228 --echo # that was cached and later used in a join. Demonstrate that
229 --echo # this is no longer the case.
230 --echo # Sic: this test is also order-dependent (i.e. the
231 --echo # the bug would show up only if the first record in the union
232 --echo # is retreived and processed first.
233 --echo #
234 --echo # Verify that JT_EQ_REF is used.
235 --vertical_results
236 explain
237 select 1 from t1 natural join (select 3 as a, 2 as b union all
238 select 3 as a, 1 as b) as t2 for update;
239 --horizontal_results
240 --echo # Lock the record.
241 select 1 from t1 natural join (select 3 as a, 2 as b union all
242 select 3 as a, 1 as b) as t2 for update;
243 --echo # Switching to connection con1
244 connection con1;
245 --echo #
246 --echo # We should not be able to delete record (3,1) from t1,
247 --echo # (previously it was possible).
248 --echo #
249 --error ER_LOCK_WAIT_TIMEOUT
250 delete from t1 where a=3;
251 --echo # Switching to connection default
252 connection default;
253 commit;
254
255 disconnect con1;
256 set @@session.transaction_isolation=default;
257 drop table t1;
258
259 --echo #
260 --echo # End of 5.1 tests
261 --echo #