← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/include/concurrent.inc

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/include/concurrent.inc
 
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;