Debian Codegraph @

mysql-test/suite/ndb/t/ndb_lock.test

raw
1 -- source include/have_ndb.inc
2
3 connect (con1,localhost,root,,);
4 connect (con2,localhost,root,,);
5
6 --disable_warnings
7 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
8 --enable_warnings
9
10 #
11 # Transaction lock test to show that the NDB
12 # table handler is working properly with
13 # transaction locks
14 #
15
16 #
17 # Testing of scan isolation
18 #
19 connection con1;
20 create table t1 (x integer not null primary key, y varchar(32)) engine = ndb;
21 insert into t1 values (1,'one'), (2,'two');
22 select * from t1 order by x;
23
24 connection con2;
25 select * from t1 order by x;
26
27 connection con1;
28 start transaction;
29 insert into t1 values (3,'three');
30 select * from t1 order by x;
31
32 connection con2;
33 start transaction;
34 select * from t1 order by x;
35
36 connection con1;
37 commit;
38
39 connection con2;
40 select * from t1 order by x;
41 commit;
42
43 drop table t1;
44
45 ###
46 # Bug#6020
47 create table t1 (pk integer not null primary key, u int not null, o int not null,
48 unique(u), key(o)) engine = ndb;
49 insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
50
51 lock tables t1 write;
52 delete from t1 where pk = 1;
53 unlock tables;
54 select * from t1 order by pk;
55 insert into t1 values (1,1,1);
56
57 lock tables t1 write;
58 delete from t1 where u = 1;
59 unlock tables;
60 select * from t1 order by pk;
61 insert into t1 values (1,1,1);
62
63 lock tables t1 write;
64 delete from t1 where o = 1;
65 unlock tables;
66 select * from t1 order by pk;
67 insert into t1 values (1,1,1);
68
69 drop table t1;
70
71 # Lock for update
72
73 create table t1 (x integer not null primary key, y varchar(32), z integer, key(z)) engine = ndb;
74
75 insert into t1 values (1,'one',1);
76
77 # PK access
78 connection con1;
79 begin;
80 select * from t1 where x = 1 for update;
81
82 connection con2;
83 begin;
84 --error 1205
85 select * from t1 where x = 1 for update;
86 rollback;
87
88 connection con1;
89 rollback;
90 insert into t1 values (2,'two',2),(3,"three",3);
91 begin;
92 select * from t1 where x = 1 for update;
93
94 connection con2;
95 --error 1205
96 select * from t1 where x = 1 for update;
97 select * from t1 where x = 2 for update;
98 rollback;
99
100 connection con1;
101 commit;
102
103 # table scan
104 #
105 # Note that there are two distinct execution paths in which we unlock
106 # non-matching rows inspected during table scan - one that is used in
107 # case of filesort and one that used in rest of cases. Below we cover
108 # the latter (Bug #20390 "SELECT FOR UPDATE does not release locks of
109 # untouched rows in full table scans").
110 connection con1;
111 begin;
112 # We can't use "order by x" here as it will cause filesort
113 --replace_column 1 # 2 # 3 #
114 select * from t1 where y = 'one' or y = 'three' for update;
115
116 connection con2;
117 begin;
118 # Have to check with pk access here since scans take locks on
119 # all rows and then release them in chunks
120 select * from t1 where x = 2 for update;
121 --error 1205
122 select * from t1 where x = 1 for update;
123 rollback;
124
125 connection con1;
126 commit;
127
128 # And now the test for case with filesort
129 begin;
130 select * from t1 where y = 'one' or y = 'three' order by x for update;
131 connection con2;
132 begin;
133 select * from t1 where x = 2 for update;
134 --error 1205
135 select * from t1 where x = 1 for update;
136 rollback;
137
138 connection con1;
139 commit;
140
141 # index scan
142 connection con1;
143 begin;
144 select * from t1 where z > 1 and z < 3 for update;
145
146 connection con2;
147 begin;
148 # Have to check with pk access here since scans take locks on
149 # all rows and then release them in chunks
150 select * from t1 where x = 1 for update;
151 --error 1105,1205
152 select * from t1 where x = 2 for update;
153 rollback;
154
155 connection con1;
156 commit;
157
158 # share locking
159
160 # PK access
161 connection con1;
162 begin;
163 select * from t1 where x = 1 lock in share mode;
164
165 connection con2;
166 begin;
167 select * from t1 where x = 1 lock in share mode;
168 select * from t1 where x = 2 for update;
169 --error 1205
170 select * from t1 where x = 1 for update;
171 rollback;
172
173 connection con1;
174 commit;
175
176 # table scan
177 connection con1;
178 begin;
179 # We can't use "order by x" here as it will cause filesort
180 --replace_column 1 # 2 # 3 #
181 select * from t1 where y = 'one' or y = 'three' lock in share mode;
182
183 connection con2;
184 begin;
185 select * from t1 where y = 'one' lock in share mode;
186 # Have to check with pk access here since scans take locks on
187 # all rows and then release them in chunks
188 select * from t1 where x = 2 for update;
189 --error 1205
190 select * from t1 where x = 1 for update;
191 rollback;
192
193 connection con1;
194 commit;
195
196 # And the same test for case with filesort
197 connection con1;
198 begin;
199 select * from t1 where y = 'one' or y = 'three' order by x lock in share mode;
200
201 connection con2;
202 begin;
203 select * from t1 where y = 'one' lock in share mode;
204 select * from t1 where x = 2 for update;
205 --error 1205
206 select * from t1 where x = 1 for update;
207 rollback;
208
209 connection con1;
210 commit;
211
212 # index scan
213 connection con1;
214 begin;
215 select * from t1 where z > 1 and z < 3 lock in share mode;
216
217 connection con2;
218 begin;
219 select * from t1 where z = 1 lock in share mode;
220 # Have to check with pk access here since scans take locks on
221 # all rows and then release them in chunks
222 select * from t1 where x = 1 for update;
223 --error 1205
224 select * from t1 where x = 2 for update;
225 rollback;
226
227 connection con1;
228 commit;
229
230 drop table t1;
231
232 # End of 4.1 tests
233
234 #
235 # Bug #17812 Previous lock table for write causes "stray" lock
236 # although table is recreated
237 #
238 # this creating, locking, and dropping causes a subsequent hang
239 # on the delete below waiting for table t2 the locking in the
240 # "other" connection is relevant, as without it there is no problem
241 #
242 connection con1;
243 create table t3 (id2 int) engine=ndb;
244
245 connection con2;
246 lock tables t3 write;
247 unlock tables;
248
249 connection con1;
250 drop table t3;
251
252 connection con1;
253 create table t2 (id int, j int) engine=ndb;
254 insert into t2 values (2, 2);
255 create table t3 (id int) engine=ndb;
256
257 connection con2;
258 lock tables t3 read;
259
260 connection con1;
261 # here we get a hang before bugfix although we shouldn't
262 delete t2 from t2, t3 where t2.id = t3.id;
263
264 connection con2;
265 unlock tables;
266
267 connection con1;
268 drop table t2, t3;