|
1
by brian
clean slate |
1 |
# include/concurrent.inc |
2 |
#
|
|
3 |
# Concurrent tests for transactional storage engines, mainly in UPDATE's |
|
4 |
# Bug#3300 |
|
5 |
# Designed and tested by Sinisa Milivojevic, sinisa@mysql.com |
|
6 |
#
|
|
7 |
# two non-interfering UPDATE's not changing result set |
|
8 |
#
|
|
9 |
# The variable |
|
10 |
# $engine_type -- storage engine to be tested |
|
11 |
# has to be set before sourcing this script. |
|
12 |
#
|
|
13 |
# Last update: |
|
14 |
# 2006-08-02 ML test refactored |
|
15 |
# old name was t/innodb_concurrent.test |
|
16 |
# main code went into include/concurrent.inc |
|
17 |
#
|
|
18 |
||
19 |
connection default; |
|
20 |
eval SET SESSION STORAGE_ENGINE = $engine_type; |
|
21 |
--disable_warnings |
|
22 |
drop table if exists t1; |
|
23 |
--enable_warnings |
|
24 |
create table t1(eta int(11) not null, tipo int(11), c varchar(255)); |
|
25 |
connect (thread1, localhost, mysqltest,,); |
|
26 |
connection thread1; |
|
27 |
eval SET SESSION STORAGE_ENGINE = $engine_type; |
|
28 |
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); |
|
29 |
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); |
|
30 |
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); |
|
31 |
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); |
|
32 |
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); |
|
33 |
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); |
|
34 |
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); |
|
35 |
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); |
|
36 |
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); |
|
37 |
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); |
|
38 |
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); |
|
39 |
select get_lock("hello",1); |
|
40 |
connect (thread2, localhost, mysqltest,,); |
|
41 |
connection thread2; |
|
42 |
begin; |
|
43 |
send update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; |
|
44 |
sleep 1; |
|
45 |
connection thread1; |
|
46 |
begin; |
|
47 |
update t1 set eta=2 where tipo=22; |
|
48 |
select release_lock("hello"); |
|
49 |
select * from t1; |
|
50 |
connection thread2; |
|
51 |
reap; |
|
52 |
select * from t1; |
|
53 |
send commit; |
|
54 |
connection thread1; |
|
55 |
select * from t1; |
|
56 |
commit; |
|
57 |
select * from t1; |
|
58 |
connection thread2; |
|
59 |
reap; |
|
60 |
select * from t1; |
|
61 |
connection thread1; |
|
62 |
select * from t1; |
|
63 |
connection default; |
|
64 |
drop table t1; |
|
65 |
||
66 |
#
|
|
67 |
# two UPDATE's running and one changing result set |
|
68 |
#
|
|
69 |
#connect (thread1, localhost, mysqltest,,); |
|
70 |
connection thread1; |
|
71 |
create table t1(eta int(11) not null, tipo int(11), c varchar(255)); |
|
72 |
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); |
|
73 |
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); |
|
74 |
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); |
|
75 |
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); |
|
76 |
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); |
|
77 |
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); |
|
78 |
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); |
|
79 |
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); |
|
80 |
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); |
|
81 |
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); |
|
82 |
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); |
|
83 |
select get_lock("hello",10); |
|
84 |
#connect (thread2, localhost, mysqltest,,); |
|
85 |
connection thread2; |
|
86 |
begin; |
|
87 |
send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; |
|
88 |
sleep 1; |
|
89 |
connection thread1; |
|
90 |
begin; |
|
91 |
update t1 set tipo=1 where tipo=2; |
|
92 |
select release_lock("hello"); |
|
93 |
select * from t1; |
|
94 |
connection thread2; |
|
95 |
reap; |
|
96 |
select * from t1; |
|
97 |
send commit; |
|
98 |
connection thread1; |
|
99 |
select * from t1; |
|
100 |
commit; |
|
101 |
select * from t1; |
|
102 |
connection thread2; |
|
103 |
reap; |
|
104 |
select * from t1; |
|
105 |
connection thread1; |
|
106 |
select * from t1; |
|
107 |
connection default; |
|
108 |
drop table t1; |
|
109 |
||
110 |
||
111 |
#
|
|
112 |
# One UPDATE and one INSERT .... Monty's test |
|
113 |
#
|
|
114 |
||
115 |
#connect (thread1, localhost, mysqltest,,); |
|
116 |
connection thread1; |
|
117 |
create table t1 (a int not null, b int not null); |
|
118 |
insert into t1 values (1,1),(2,1),(3,1),(4,1); |
|
119 |
select get_lock("hello2",1000); |
|
120 |
#connect (thread2, localhost, mysqltest,,); |
|
121 |
connection thread2; |
|
122 |
begin; |
|
123 |
send update t1 set b=10+get_lock(concat("hello",a),1000)*0 where |
|
124 |
a=2; |
|
125 |
sleep 1; |
|
126 |
connection thread1; |
|
127 |
insert into t1 values (1,1); |
|
128 |
select release_lock("hello2"); |
|
129 |
select * from t1; |
|
130 |
connection thread2; |
|
131 |
reap; |
|
132 |
select * from t1; |
|
133 |
send commit; |
|
134 |
connection thread1; |
|
135 |
sleep 1; |
|
136 |
connection thread2; |
|
137 |
reap; |
|
138 |
connection default; |
|
139 |
drop table t1; |
|
140 |
||
141 |
#
|
|
142 |
# one UPDATE changing result set and SELECT ... FOR UPDATE |
|
143 |
#
|
|
144 |
#connect (thread1, localhost, mysqltest,,); |
|
145 |
connection thread1; |
|
146 |
create table t1(eta int(11) not null, tipo int(11), c varchar(255)); |
|
147 |
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); |
|
148 |
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); |
|
149 |
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); |
|
150 |
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); |
|
151 |
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); |
|
152 |
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); |
|
153 |
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); |
|
154 |
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); |
|
155 |
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); |
|
156 |
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); |
|
157 |
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); |
|
158 |
select get_lock("hello",10); |
|
159 |
#connect (thread2, localhost, mysqltest,,); |
|
160 |
connection thread2; |
|
161 |
begin; |
|
162 |
send select * from t1 where tipo=2 FOR UPDATE; |
|
163 |
sleep 1; |
|
164 |
connection thread1; |
|
165 |
begin; |
|
166 |
select release_lock("hello"); |
|
167 |
--error 1205 |
|
168 |
update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; |
|
169 |
select * from t1; |
|
170 |
connection thread2; |
|
171 |
reap; |
|
172 |
select * from t1; |
|
173 |
send commit; |
|
174 |
connection thread1; |
|
175 |
commit; |
|
176 |
connection thread2; |
|
177 |
reap; |
|
178 |
select * from t1; |
|
179 |
connection thread1; |
|
180 |
select * from t1; |
|
181 |
connection default; |
|
182 |
drop table t1; |
|
183 |
||
184 |
#
|
|
185 |
# one UPDATE not changing result set and SELECT ... FOR UPDATE |
|
186 |
#
|
|
187 |
#connect (thread1, localhost, mysqltest,,); |
|
188 |
connection thread1; |
|
189 |
create table t1(eta int(11) not null, tipo int(11), c varchar(255)); |
|
190 |
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); |
|
191 |
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); |
|
192 |
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); |
|
193 |
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); |
|
194 |
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); |
|
195 |
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); |
|
196 |
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); |
|
197 |
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); |
|
198 |
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); |
|
199 |
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); |
|
200 |
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); |
|
201 |
select get_lock("hello",10); |
|
202 |
#connect (thread2, localhost, mysqltest,,); |
|
203 |
connection thread2; |
|
204 |
begin; |
|
205 |
send select * from t1 where tipo=2 FOR UPDATE; |
|
206 |
sleep 1; |
|
207 |
connection thread1; |
|
208 |
begin; |
|
209 |
select release_lock("hello"); |
|
210 |
--error 1205 |
|
211 |
update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; |
|
212 |
select * from t1; |
|
213 |
connection thread2; |
|
214 |
reap; |
|
215 |
select * from t1; |
|
216 |
send commit; |
|
217 |
connection thread1; |
|
218 |
commit; |
|
219 |
connection thread2; |
|
220 |
reap; |
|
221 |
select * from t1; |
|
222 |
connection thread1; |
|
223 |
select * from t1; |
|
224 |
connection default; |
|
225 |
drop table t1; |
|
226 |
||
227 |
#
|
|
228 |
# two SELECT ... FOR UPDATE |
|
229 |
#
|
|
230 |
#connect (thread1, localhost, mysqltest,,); |
|
231 |
connection thread1; |
|
232 |
create table t1(eta int(11) not null, tipo int(11), c varchar(255)); |
|
233 |
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); |
|
234 |
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); |
|
235 |
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); |
|
236 |
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); |
|
237 |
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); |
|
238 |
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); |
|
239 |
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); |
|
240 |
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); |
|
241 |
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); |
|
242 |
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); |
|
243 |
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); |
|
244 |
select get_lock("hello",10); |
|
245 |
#connect (thread2, localhost, mysqltest,,); |
|
246 |
connection thread2; |
|
247 |
begin; |
|
248 |
send select * from t1 where tipo=2 FOR UPDATE; |
|
249 |
sleep 1; |
|
250 |
connection thread1; |
|
251 |
begin; |
|
252 |
select release_lock("hello"); |
|
253 |
--error 1205 |
|
254 |
select * from t1 where tipo=1 FOR UPDATE; |
|
255 |
connection thread2; |
|
256 |
reap; |
|
257 |
select * from t1; |
|
258 |
send commit; |
|
259 |
connection thread1; |
|
260 |
commit; |
|
261 |
connection thread2; |
|
262 |
reap; |
|
263 |
select * from t1; |
|
264 |
connection thread1; |
|
265 |
select * from t1; |
|
266 |
connection default; |
|
267 |
drop table t1; |
|
268 |
||
269 |
||
270 |
#
|
|
271 |
# one UPDATE changing result set and DELETE |
|
272 |
#
|
|
273 |
#connect (thread1, localhost, mysqltest,,); |
|
274 |
connection thread1; |
|
275 |
create table t1(eta int(11) not null, tipo int(11), c varchar(255)); |
|
276 |
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); |
|
277 |
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); |
|
278 |
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); |
|
279 |
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); |
|
280 |
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); |
|
281 |
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); |
|
282 |
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); |
|
283 |
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); |
|
284 |
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); |
|
285 |
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); |
|
286 |
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); |
|
287 |
select get_lock("hello",10); |
|
288 |
#connect (thread2, localhost, mysqltest,,); |
|
289 |
connection thread2; |
|
290 |
begin; |
|
291 |
send delete from t1 where tipo=2; |
|
292 |
sleep 1; |
|
293 |
connection thread1; |
|
294 |
begin; |
|
295 |
select release_lock("hello"); |
|
296 |
--error 1205 |
|
297 |
update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; |
|
298 |
select * from t1; |
|
299 |
connection thread2; |
|
300 |
reap; |
|
301 |
select * from t1; |
|
302 |
send commit; |
|
303 |
connection thread1; |
|
304 |
commit; |
|
305 |
connection thread2; |
|
306 |
reap; |
|
307 |
select * from t1; |
|
308 |
connection thread1; |
|
309 |
select * from t1; |
|
310 |
connection default; |
|
311 |
drop table t1; |
|
312 |
||
313 |
||
314 |
#
|
|
315 |
# one UPDATE not changing result set and DELETE |
|
316 |
#
|
|
317 |
#connect (thread1, localhost, mysqltest,,); |
|
318 |
connection thread1; |
|
319 |
create table t1(eta int(11) not null, tipo int(11), c varchar(255)); |
|
320 |
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); |
|
321 |
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); |
|
322 |
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); |
|
323 |
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); |
|
324 |
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); |
|
325 |
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); |
|
326 |
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); |
|
327 |
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); |
|
328 |
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); |
|
329 |
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); |
|
330 |
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); |
|
331 |
select get_lock("hello",10); |
|
332 |
#connect (thread2, localhost, mysqltest,,); |
|
333 |
connection thread2; |
|
334 |
begin; |
|
335 |
send delete from t1 where tipo=2; |
|
336 |
sleep 1; |
|
337 |
connection thread1; |
|
338 |
begin; |
|
339 |
select release_lock("hello"); |
|
340 |
update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; |
|
341 |
select * from t1; |
|
342 |
connection thread2; |
|
343 |
reap; |
|
344 |
select * from t1; |
|
345 |
send commit; |
|
346 |
connection thread1; |
|
347 |
commit; |
|
348 |
connection thread2; |
|
349 |
reap; |
|
350 |
select * from t1; |
|
351 |
connection thread1; |
|
352 |
select * from t1; |
|
353 |
connection default; |
|
354 |
sleep 1; |
|
355 |
drop table t1; |
|
356 |
disconnect thread1; |
|
357 |
disconnect thread2; |