Debian Codegraph @

mysql-test/suite/sys_vars/r/transaction_isolation_func.result

raw
1 #
2 # WL#9636: Rename tx_{isolation,isolation} variables to transaction_*
3 #
4 # Setup.
5 SET @session_transaction_isolation = @@SESSION.transaction_isolation;
6 SET @global_transaction_isolation = @@GLOBAL.transaction_isolation;
7 # Creating connections
8 connect con0,localhost,root,,;
9 connection con0;
10 SET SESSION AUTOCOMMIT = OFF;
11 connect con1, localhost, root,,;
12 connection con1;
13 SET SESSION AUTOCOMMIT = OFF;
14 connection default;
15 # Creating tables
16 CREATE TABLE t1 (a int PRIMARY KEY, b int) ENGINE=INNODB;
17 INSERT INTO t1 VALUES(2, 2);
18 INSERT INTO t1 VALUES(4, 4);
19 INSERT INTO t1 VALUES(6, 6);
20 INSERT INTO t1 VALUES(8, 8);
21 INSERT INTO t1 VALUES(16, 16);
22 INSERT INTO t1 VALUES(18, 18);
23 INSERT INTO t1 VALUES(20, 20);
24 INSERT INTO t1 VALUES(22, 22);
25 INSERT INTO t1 VALUES(24, 24);
26 # Testing for value READ-UNCOMMITTED
27 connection con0;
28 SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
29 set binlog_format=mixed;
30 Warnings:
31 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
32 connection con1;
33 SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
34 set binlog_format=mixed;
35 Warnings:
36 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
37 # Testing WHERE on keys using IN clause
38 connection con0;
39 START TRANSACTION;
40 SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE;
41 a b
42 2 2
43 4 4
44 6 6
45 8 8
46 UPDATE t1 SET b = 10 WHERE a IN (2,4,6,8);
47 connection con1;
48 START TRANSACTION;
49 SELECT * FROM t1;
50 a b
51 2 10
52 4 10
53 6 10
54 8 10
55 16 16
56 18 18
57 20 20
58 22 22
59 24 24
60 INSERT INTO t1 VALUES(1, 1);
61 INSERT INTO t1 VALUES(3, 3);
62 SELECT * FROM t1;
63 a b
64 1 1
65 2 10
66 3 3
67 4 10
68 6 10
69 8 10
70 16 16
71 18 18
72 20 20
73 22 22
74 24 24
75 COMMIT;
76 connection con0;
77 DELETE FROM t1 WHERE a = 1 OR a = 3;
78 COMMIT;
79 # Testing WHERE on keys using # on even rows
80 connection con0;
81 START TRANSACTION;
82 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
83 a b
84 2 10
85 4 10
86 6 10
87 8 10
88 16 16
89 18 18
90 20 20
91 22 22
92 24 24
93 UPDATE t1 SET b = 10 WHERE a % 2 = 0;
94 connection con1;
95 START TRANSACTION;
96 SELECT * FROM t1;
97 a b
98 2 10
99 4 10
100 6 10
101 8 10
102 16 10
103 18 10
104 20 10
105 22 10
106 24 10
107 INSERT INTO t1 VALUES(23, 23);
108 INSERT INTO t1 VALUES(25, 25);
109 SELECT * FROM t1;
110 a b
111 2 10
112 4 10
113 6 10
114 8 10
115 16 10
116 18 10
117 20 10
118 22 10
119 23 23
120 24 10
121 25 25
122 COMMIT;
123 connection con0;
124 COMMIT;
125 # Testing for value READ-COMMITTED
126 connection con0;
127 SET SESSION transaction_isolation = 'READ-COMMITTED';
128 connection con1;
129 SET SESSION transaction_isolation = 'READ-COMMITTED';
130 # Testing WHERE on keys using % on even rows
131 connection con0;
132 START TRANSACTION;
133 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
134 a b
135 2 10
136 4 10
137 6 10
138 8 10
139 16 10
140 18 10
141 20 10
142 22 10
143 24 10
144 UPDATE t1 SET b = 11 WHERE a % 2 = 0;
145 connection con1;
146 START TRANSACTION;
147 SELECT * FROM t1;
148 a b
149 2 10
150 4 10
151 6 10
152 8 10
153 16 10
154 18 10
155 20 10
156 22 10
157 23 23
158 24 10
159 25 25
160 INSERT INTO t1 VALUES(5, 5);
161 INSERT INTO t1 VALUES(7, 7);
162 SELECT * FROM t1;
163 a b
164 2 10
165 4 10
166 5 5
167 6 10
168 7 7
169 8 10
170 16 10
171 18 10
172 20 10
173 22 10
174 23 23
175 24 10
176 25 25
177 COMMIT;
178 connection con0;
179 COMMIT;
180 # Testing for value REPEATABLE-READ
181 connection con0;
182 SET SESSION transaction_isolation = 'REPEATABLE-READ';
183 connection con1;
184 SET SESSION transaction_isolation = 'REPEATABLE-READ';
185 # Testing WHERE on keys using % on even rows
186 connection con0;
187 START TRANSACTION;
188 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
189 a b
190 2 11
191 4 11
192 6 11
193 8 11
194 16 11
195 18 11
196 20 11
197 22 11
198 24 11
199 UPDATE t1 SET b = 12 WHERE a % 2 = 0;
200 connection con1;
201 START TRANSACTION;
202 SELECT * FROM t1;
203 a b
204 2 11
205 4 11
206 5 5
207 6 11
208 7 7
209 8 11
210 16 11
211 18 11
212 20 11
213 22 11
214 23 23
215 24 11
216 25 25
217 INSERT INTO t1 VALUES(9, 9);
218 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
219 INSERT INTO t1 VALUES(13, 13);
220 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
221 Expected error "Lock wait timeout"
222 SELECT * FROM t1;
223 a b
224 2 11
225 4 11
226 5 5
227 6 11
228 7 7
229 8 11
230 16 11
231 18 11
232 20 11
233 22 11
234 23 23
235 24 11
236 25 25
237 COMMIT;
238 ** Connection con0 **
239 connection con0;
240 COMMIT;
241 # Testing WHERE on keys using IN clause
242 connection con0;
243 START TRANSACTION;
244 SELECT * FROM t1 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0 FOR UPDATE;
245 a b
246 5 5
247 7 7
248 23 23
249 25 25
250 UPDATE t1 SET b = 13 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0;
251 connection con1;
252 START TRANSACTION;
253 SELECT * FROM t1;
254 a b
255 2 12
256 4 12
257 5 5
258 6 12
259 7 7
260 8 12
261 16 12
262 18 12
263 20 12
264 22 12
265 23 23
266 24 12
267 25 25
268 INSERT INTO t1 VALUES(9, 9);
269 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
270 INSERT INTO t1 VALUES(13, 13);
271 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
272 Expected error "Lock wait timeout"
273 SELECT * FROM t1;
274 a b
275 2 12
276 4 12
277 5 5
278 6 12
279 7 7
280 8 12
281 16 12
282 18 12
283 20 12
284 22 12
285 23 23
286 24 12
287 25 25
288 COMMIT;
289 connection con0;
290 COMMIT;
291 # Testing WHERE on keys using IN clause
292 connection con0;
293 START TRANSACTION;
294 SELECT * FROM t1 WHERE a IN (2,4,6,8) = 0 FOR UPDATE;
295 a b
296 5 13
297 7 13
298 16 12
299 18 12
300 20 12
301 22 12
302 23 13
303 24 12
304 25 13
305 UPDATE t1 SET b = 14 WHERE a IN (2,4,6,8) = 0;
306 connection con1;
307 START TRANSACTION;
308 SELECT * FROM t1;
309 a b
310 2 12
311 4 12
312 5 13
313 6 12
314 7 13
315 8 12
316 16 12
317 18 12
318 20 12
319 22 12
320 23 13
321 24 12
322 25 13
323 INSERT INTO t1 VALUES(9, 9);
324 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
325 INSERT INTO t1 VALUES(13, 13);
326 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
327 Expected error "Lock wait timeout"
328 SELECT * FROM t1;
329 a b
330 2 12
331 4 12
332 5 13
333 6 12
334 7 13
335 8 12
336 16 12
337 18 12
338 20 12
339 22 12
340 23 13
341 24 12
342 25 13
343 COMMIT;
344 connection con0;
345 COMMIT;
346 # Testing for value SERIALIZABLE
347 connection con0;
348 SET SESSION transaction_isolation = 'SERIALIZABLE';
349 connection con1;
350 SET SESSION transaction_isolation = 'SERIALIZABLE';
351 # Testing WHERE on keys using # on even rows
352 connection con0;
353 START TRANSACTION;
354 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
355 a b
356 2 12
357 4 12
358 6 12
359 8 12
360 16 14
361 18 14
362 20 14
363 22 14
364 24 14
365 UPDATE t1 SET b = 15 WHERE a % 2 = 0;
366 connection con1;
367 START TRANSACTION;
368 SELECT * FROM t1;
369 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
370 INSERT INTO t1 VALUES(15, 15);
371 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
372 INSERT INTO t1 VALUES(17, 17);
373 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
374 SELECT * FROM t1;
375 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
376 COMMIT;
377 connection con0;
378 COMMIT;
379 # Session data integrity check & GLOBAL Value check
380 SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED';
381 connect con_int1,localhost,root,,;
382 connection con_int1;
383 SELECT @@SESSION.transaction_isolation;
384 @@SESSION.transaction_isolation
385 READ-UNCOMMITTED
386 READ-UNCOMMITTED Expected
387 SET SESSION transaction_isolation = 'SERIALIZABLE';
388 connect con_int2,localhost,root,,;
389 connection con_int2;
390 SELECT @@SESSION.transaction_isolation;
391 @@SESSION.transaction_isolation
392 READ-UNCOMMITTED
393 READ-UNCOMMITTED Expected
394 SET SESSION transaction_isolation = 'REPEATABLE-READ';
395 connection con_int2;
396 SELECT @@SESSION.transaction_isolation;
397 @@SESSION.transaction_isolation
398 REPEATABLE-READ
399 REPEATABLE-READ Expected
400 connection con_int1;
401 SELECT @@SESSION.transaction_isolation;
402 @@SESSION.transaction_isolation
403 SERIALIZABLE
404 SERIALIZABLE Expected
405 SELECT @@GLOBAL.transaction_isolation;
406 @@GLOBAL.transaction_isolation
407 READ-UNCOMMITTED
408 READ-UNCOMMITTED Expected
409 connection default;
410 Disconnecting Connections con_int1, con_int2
411 disconnect con_int1;
412 disconnect con_int2;
413 # Cleanup
414 SET @@SESSION.transaction_isolation = @session_transaction_isolation;
415 SET @@GLOBAL.transaction_isolation = @global_transaction_isolation;
416 connection default;
417 Disconnecting Connections con0, con1
418 disconnect con0;
419 disconnect con1;
420 DROP TABLE t1;