Debian Codegraph @

mysql-test/suite/sys_vars/t/transaction_isolation_func.test

raw
1 ############## mysql-test\suite\sys_vars\t\transaction_isolation_func.test ##############
2 # #
3 # Variable Name: transaction_isolation #
4 # Scope: GLOBAL & SESSION #
5 # Access Type: Dynamic #
6 # Data Type: ENUMERATION #
7 # Default Value: REPEATABLE-READ #
8 # Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE #
9 # #
10 # #
11 # Creation Date: 2008-02-25 #
12 # Author: Sharique Abdullah #
13 # #
14 # Description: Test Cases of Dynamic System Variable "transaction_isolation" #
15 # that checks behavior of this variable in the following ways #
16 # * Functionality based on different values #
17 # #
18 # Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html #
19 # #
20 #########################################################################################
21
22 --source include/have_innodb.inc
23
24 --echo ** Setup **
25 #
26 # Setup
27 #
28
29 SET @session_transaction_isolation = @@SESSION.transaction_isolation;
30 SET @global_transaction_isolation = @@GLOBAL.transaction_isolation;
31
32 #
33 # Creating connections
34 #
35
36 connect (con0,localhost,root,,);
37
38 connection con0;
39
40 SET SESSION AUTOCOMMIT = OFF;
41
42 connect (con1, localhost, root,,);
43
44 connection con1;
45
46 SET SESSION AUTOCOMMIT = OFF;
47
48 connection default;
49
50 #
51 # Creating tables
52 #
53
54 CREATE TABLE t1 (a int PRIMARY KEY, b int) ENGINE=INNODB;
55
56 INSERT INTO t1 VALUES(2, 2);
57 INSERT INTO t1 VALUES(4, 4);
58 INSERT INTO t1 VALUES(6, 6);
59 INSERT INTO t1 VALUES(8, 8);
60 INSERT INTO t1 VALUES(16, 16);
61 INSERT INTO t1 VALUES(18, 18);
62 INSERT INTO t1 VALUES(20, 20);
63 INSERT INTO t1 VALUES(22, 22);
64 INSERT INTO t1 VALUES(24, 24);
65
66 --echo '#----------------------------FN_DYNVARS_184_01--------------------------------------#'
67 #
68 # Testing for value READ-UNCOMMITTED
69 #
70 connection con0;
71 SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
72 set binlog_format=mixed;
73
74 connection con1;
75 SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
76 set binlog_format=mixed;
77
78 #
79 # Testing WHERE on keys using IN clause
80 #
81 connection con0;
82
83 START TRANSACTION;
84
85 SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE;
86 UPDATE t1 SET b = 10 WHERE a IN (2,4,6,8);
87
88 connection con1;
89
90 START TRANSACTION;
91
92 SELECT * FROM t1;
93
94 INSERT INTO t1 VALUES(1, 1);
95 INSERT INTO t1 VALUES(3, 3);
96
97 SELECT * FROM t1;
98
99 COMMIT;
100
101 connection con0;
102
103 DELETE FROM t1 WHERE a = 1 OR a = 3;
104
105 COMMIT;
106
107 --echo '#----------------------------FN_DYNVARS_184_02--------------------------------------#'
108 #
109 # Testing WHERE on keys using # on even rows
110 #
111
112 connection con0;
113
114 START TRANSACTION;
115
116 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
117 UPDATE t1 SET b = 10 WHERE a % 2 = 0;
118
119 connection con1;
120
121 START TRANSACTION;
122
123 SELECT * FROM t1;
124
125 INSERT INTO t1 VALUES(23, 23);
126
127 INSERT INTO t1 VALUES(25, 25);
128
129 SELECT * FROM t1;
130
131 COMMIT;
132
133 connection con0;
134
135 COMMIT;
136
137 --echo '#----------------------------FN_DYNVARS_184_03--------------------------------------#'
138 #
139 # Testing for value READ-COMMITTED
140 #
141 connection con0;
142 SET SESSION transaction_isolation = 'READ-COMMITTED';
143
144 connection con1;
145 SET SESSION transaction_isolation = 'READ-COMMITTED';
146
147 #
148 # Testing WHERE on keys using % on even rows
149 #
150 connection con0;
151
152 START TRANSACTION;
153
154 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
155 UPDATE t1 SET b = 11 WHERE a % 2 = 0;
156
157 connection con1;
158
159 START TRANSACTION;
160
161 SELECT * FROM t1;
162
163 INSERT INTO t1 VALUES(5, 5);
164 INSERT INTO t1 VALUES(7, 7);
165
166 SELECT * FROM t1;
167
168 COMMIT;
169
170 connection con0;
171
172 COMMIT;
173
174
175 --echo '#----------------------------FN_DYNVARS_184_04--------------------------------------#'
176 #
177 # Testing for value REPEATABLE-READ
178 #
179 connection con0;
180 SET SESSION transaction_isolation = 'REPEATABLE-READ';
181
182 connection con1;
183 SET SESSION transaction_isolation = 'REPEATABLE-READ';
184
185 #
186 # Testing WHERE on keys using % on even rows
187 #
188 connection con0;
189
190 START TRANSACTION;
191
192 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
193 UPDATE t1 SET b = 12 WHERE a % 2 = 0;
194
195 connection con1;
196
197 START TRANSACTION;
198
199 SELECT * FROM t1;
200
201 --error ER_LOCK_WAIT_TIMEOUT
202 INSERT INTO t1 VALUES(9, 9);
203 --error ER_LOCK_WAIT_TIMEOUT
204 INSERT INTO t1 VALUES(13, 13);
205 --echo Expected error "Lock wait timeout"
206
207 SELECT * FROM t1;
208
209 COMMIT;
210
211 connection con0;
212
213 COMMIT;
214
215 --echo '#----------------------------FN_DYNVARS_184_05--------------------------------------#'
216 #
217 # Testing WHERE on keys using IN clause
218 #
219 connection con0;
220
221 START TRANSACTION;
222
223 SELECT * FROM t1 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0 FOR UPDATE;
224 UPDATE t1 SET b = 13 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0;
225
226 connection con1;
227
228 START TRANSACTION;
229
230 SELECT * FROM t1;
231
232 --error ER_LOCK_WAIT_TIMEOUT
233 INSERT INTO t1 VALUES(9, 9);
234 --error ER_LOCK_WAIT_TIMEOUT
235 INSERT INTO t1 VALUES(13, 13);
236 --echo Expected error "Lock wait timeout"
237
238 SELECT * FROM t1;
239
240 COMMIT;
241
242 connection con0;
243
244 COMMIT;
245
246 --echo '#----------------------------FN_DYNVARS_184_06--------------------------------------#'
247 #
248 # Testing WHERE on keys using IN clause
249 #
250 connection con0;
251
252 START TRANSACTION;
253
254 SELECT * FROM t1 WHERE a IN (2,4,6,8) = 0 FOR UPDATE;
255 UPDATE t1 SET b = 14 WHERE a IN (2,4,6,8) = 0;
256
257 connection con1;
258
259 START TRANSACTION;
260
261 SELECT * FROM t1;
262
263 --error ER_LOCK_WAIT_TIMEOUT
264 INSERT INTO t1 VALUES(9, 9);
265 --error ER_LOCK_WAIT_TIMEOUT
266 INSERT INTO t1 VALUES(13, 13);
267 --echo Expected error "Lock wait timeout"
268
269 SELECT * FROM t1;
270
271 COMMIT;
272
273 connection con0;
274
275 COMMIT;
276
277 --echo '#----------------------------FN_DYNVARS_184_07--------------------------------------#'
278 #
279 # Testing for value SERIALIZABLE
280 #
281 connection con0;
282 SET SESSION transaction_isolation = 'SERIALIZABLE';
283
284 connection con1;
285 SET SESSION transaction_isolation = 'SERIALIZABLE';
286
287 #
288 # Testing WHERE on keys using # on even rows
289 #
290 connection con0;
291
292 START TRANSACTION;
293
294 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
295 UPDATE t1 SET b = 15 WHERE a % 2 = 0;
296
297 connection con1;
298
299 START TRANSACTION;
300
301 --error ER_LOCK_WAIT_TIMEOUT
302 SELECT * FROM t1;
303
304 --error ER_LOCK_WAIT_TIMEOUT
305 INSERT INTO t1 VALUES(15, 15);
306 --error ER_LOCK_WAIT_TIMEOUT
307 INSERT INTO t1 VALUES(17, 17);
308
309 --error ER_LOCK_WAIT_TIMEOUT
310 SELECT * FROM t1;
311
312 COMMIT;
313
314 connection con0;
315
316 COMMIT;
317
318
319 --echo '#----------------------------FN_DYNVARS_184_08--------------------------------------#'
320 #
321 # Session data integrity check & GLOBAL Value check
322 #
323
324 SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED';
325 connect (con_int1,localhost,root,,);
326
327 connection con_int1;
328 SELECT @@SESSION.transaction_isolation;
329 --echo READ-UNCOMMITTED Expected
330
331 SET SESSION transaction_isolation = 'SERIALIZABLE';
332
333 connect (con_int2,localhost,root,,);
334
335 connection con_int2;
336 SELECT @@SESSION.transaction_isolation;
337 --echo READ-UNCOMMITTED Expected
338
339 SET SESSION transaction_isolation = 'REPEATABLE-READ';
340
341 connection con_int2;
342 SELECT @@SESSION.transaction_isolation;
343 --echo REPEATABLE-READ Expected
344
345 connection con_int1;
346 SELECT @@SESSION.transaction_isolation;
347 --echo SERIALIZABLE Expected
348
349
350 SELECT @@GLOBAL.transaction_isolation;
351 --echo READ-UNCOMMITTED Expected
352
353 connection default;
354
355 disconnect con_int1;
356 disconnect con_int2;
357
358 #
359 # Cleanup
360 #
361
362 SET @@SESSION.transaction_isolation = @session_transaction_isolation;
363 SET @@GLOBAL.transaction_isolation = @global_transaction_isolation;
364
365 connection default;
366 disconnect con0;
367 disconnect con1;
368
369 DROP TABLE t1;