Debian Codegraph @

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

raw
1 --echo #
2 --echo # WL#9636: Rename tx_{isolation,isolation} variables to transaction_*
3 --echo #
4
5 --echo # Setup.
6 SET @session_transaction_isolation = @@SESSION.transaction_isolation;
7 SET @global_transaction_isolation = @@GLOBAL.transaction_isolation;
8
9 --echo # Creating connections
10
11 --enable_connect_log
12 connect (con0,localhost,root,,);
13 connection con0;
14
15 SET SESSION AUTOCOMMIT = OFF;
16
17 connect (con1, localhost, root,,);
18 connection con1;
19
20 SET SESSION AUTOCOMMIT = OFF;
21
22 connection default;
23
24 --echo # Creating tables
25
26 CREATE TABLE t1 (a int PRIMARY KEY, b int) ENGINE=INNODB;
27
28 INSERT INTO t1 VALUES(2, 2);
29 INSERT INTO t1 VALUES(4, 4);
30 INSERT INTO t1 VALUES(6, 6);
31 INSERT INTO t1 VALUES(8, 8);
32 INSERT INTO t1 VALUES(16, 16);
33 INSERT INTO t1 VALUES(18, 18);
34 INSERT INTO t1 VALUES(20, 20);
35 INSERT INTO t1 VALUES(22, 22);
36 INSERT INTO t1 VALUES(24, 24);
37
38 --echo # Testing for value READ-UNCOMMITTED
39 connection con0;
40 SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
41 set binlog_format=mixed;
42
43 connection con1;
44 SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
45 set binlog_format=mixed;
46
47 --echo # Testing WHERE on keys using IN clause
48 connection con0;
49
50 START TRANSACTION;
51
52 SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE;
53 UPDATE t1 SET b = 10 WHERE a IN (2,4,6,8);
54
55 connection con1;
56
57 START TRANSACTION;
58
59 SELECT * FROM t1;
60
61 INSERT INTO t1 VALUES(1, 1);
62 INSERT INTO t1 VALUES(3, 3);
63
64 SELECT * FROM t1;
65
66 COMMIT;
67
68 connection con0;
69
70 DELETE FROM t1 WHERE a = 1 OR a = 3;
71
72 COMMIT;
73
74 --echo # Testing WHERE on keys using # on even rows
75
76 connection con0;
77
78 START TRANSACTION;
79
80 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
81 UPDATE t1 SET b = 10 WHERE a % 2 = 0;
82
83 connection con1;
84
85 START TRANSACTION;
86
87 SELECT * FROM t1;
88
89 INSERT INTO t1 VALUES(23, 23);
90
91 INSERT INTO t1 VALUES(25, 25);
92
93 SELECT * FROM t1;
94
95 COMMIT;
96
97 connection con0;
98
99 COMMIT;
100
101 --echo # Testing for value READ-COMMITTED
102 connection con0;
103 SET SESSION transaction_isolation = 'READ-COMMITTED';
104
105 connection con1;
106 SET SESSION transaction_isolation = 'READ-COMMITTED';
107
108 --echo # Testing WHERE on keys using % on even rows
109 connection con0;
110
111 START TRANSACTION;
112
113 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
114 UPDATE t1 SET b = 11 WHERE a % 2 = 0;
115
116 connection con1;
117
118 START TRANSACTION;
119
120 SELECT * FROM t1;
121
122 INSERT INTO t1 VALUES(5, 5);
123 INSERT INTO t1 VALUES(7, 7);
124
125 SELECT * FROM t1;
126
127 COMMIT;
128
129 connection con0;
130
131 COMMIT;
132
133 --echo # Testing for value REPEATABLE-READ
134 connection con0;
135 SET SESSION transaction_isolation = 'REPEATABLE-READ';
136
137 connection con1;
138 SET SESSION transaction_isolation = 'REPEATABLE-READ';
139
140 --echo # Testing WHERE on keys using % on even rows
141 connection con0;
142
143 START TRANSACTION;
144
145 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
146 UPDATE t1 SET b = 12 WHERE a % 2 = 0;
147
148 connection con1;
149
150 START TRANSACTION;
151
152 SELECT * FROM t1;
153
154 --error ER_LOCK_WAIT_TIMEOUT
155 INSERT INTO t1 VALUES(9, 9);
156 --error ER_LOCK_WAIT_TIMEOUT
157 INSERT INTO t1 VALUES(13, 13);
158 --echo Expected error "Lock wait timeout"
159
160 SELECT * FROM t1;
161
162 COMMIT;
163
164 --echo ** Connection con0 **
165 connection con0;
166
167 COMMIT;
168
169 --echo # Testing WHERE on keys using IN clause
170 connection con0;
171
172 START TRANSACTION;
173
174 SELECT * FROM t1 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0 FOR UPDATE;
175 UPDATE t1 SET b = 13 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0;
176
177 connection con1;
178
179 START TRANSACTION;
180
181 SELECT * FROM t1;
182
183 --error ER_LOCK_WAIT_TIMEOUT
184 INSERT INTO t1 VALUES(9, 9);
185 --error ER_LOCK_WAIT_TIMEOUT
186 INSERT INTO t1 VALUES(13, 13);
187 --echo Expected error "Lock wait timeout"
188
189 SELECT * FROM t1;
190
191 COMMIT;
192
193 connection con0;
194
195 COMMIT;
196
197 --echo # Testing WHERE on keys using IN clause
198 connection con0;
199
200 START TRANSACTION;
201
202 SELECT * FROM t1 WHERE a IN (2,4,6,8) = 0 FOR UPDATE;
203 UPDATE t1 SET b = 14 WHERE a IN (2,4,6,8) = 0;
204
205 connection con1;
206
207 START TRANSACTION;
208
209 SELECT * FROM t1;
210
211 --error ER_LOCK_WAIT_TIMEOUT
212 INSERT INTO t1 VALUES(9, 9);
213 --error ER_LOCK_WAIT_TIMEOUT
214 INSERT INTO t1 VALUES(13, 13);
215 --echo Expected error "Lock wait timeout"
216
217 SELECT * FROM t1;
218
219 COMMIT;
220
221 connection con0;
222
223 COMMIT;
224
225 --echo # Testing for value SERIALIZABLE
226 connection con0;
227 SET SESSION transaction_isolation = 'SERIALIZABLE';
228
229 connection con1;
230 SET SESSION transaction_isolation = 'SERIALIZABLE';
231
232 --echo # Testing WHERE on keys using # on even rows
233 connection con0;
234
235 START TRANSACTION;
236
237 SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
238 UPDATE t1 SET b = 15 WHERE a % 2 = 0;
239
240 connection con1;
241
242 START TRANSACTION;
243
244 --error ER_LOCK_WAIT_TIMEOUT
245 SELECT * FROM t1;
246
247 --error ER_LOCK_WAIT_TIMEOUT
248 INSERT INTO t1 VALUES(15, 15);
249 --error ER_LOCK_WAIT_TIMEOUT
250 INSERT INTO t1 VALUES(17, 17);
251
252 --error ER_LOCK_WAIT_TIMEOUT
253 SELECT * FROM t1;
254
255 COMMIT;
256
257 connection con0;
258
259 COMMIT;
260
261
262 --echo # Session data integrity check & GLOBAL Value check
263
264 SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED';
265 connect (con_int1,localhost,root,,);
266
267 connection con_int1;
268 SELECT @@SESSION.transaction_isolation;
269 --echo READ-UNCOMMITTED Expected
270
271 SET SESSION transaction_isolation = 'SERIALIZABLE';
272
273 connect (con_int2,localhost,root,,);
274
275 connection con_int2;
276 SELECT @@SESSION.transaction_isolation;
277 --echo READ-UNCOMMITTED Expected
278
279 SET SESSION transaction_isolation = 'REPEATABLE-READ';
280
281 connection con_int2;
282 SELECT @@SESSION.transaction_isolation;
283 --echo REPEATABLE-READ Expected
284
285 connection con_int1;
286 SELECT @@SESSION.transaction_isolation;
287 --echo SERIALIZABLE Expected
288
289
290 SELECT @@GLOBAL.transaction_isolation;
291 --echo READ-UNCOMMITTED Expected
292
293 connection default;
294
295 --echo Disconnecting Connections con_int1, con_int2
296 disconnect con_int1;
297 disconnect con_int2;
298
299 --echo # Cleanup
300
301 SET @@SESSION.transaction_isolation = @session_transaction_isolation;
302 SET @@GLOBAL.transaction_isolation = @global_transaction_isolation;
303
304 connection default;
305 --echo Disconnecting Connections con0, con1
306 disconnect con0;
307 disconnect con1;
308 --disable_connect_log
309
310 DROP TABLE t1;