Debian Codegraph @

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

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