Debian Codegraph @

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

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