Debian Codegraph @

mysql-test/suite/innodb/t/high_prio_trx_7.test

raw
1 --echo # Scenario:1
2 --echo # T1= ({W(B)}, HIGH_PRIORITY)
3 --echo # T2= ( Try to take table level lock )
4 --echo # Outcome: T2 waits for t1 to acquire table lock
5
6 --source include/have_debug.inc
7 --source include/count_sessions.inc
8
9 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
10 INSERT INTO t1 VALUES (0);
11
12 --connect(con1,localhost,root,,test)
13 --connect(con2,localhost,root,,test)
14
15 --echo
16 --echo # On connection 1
17 --connection con1
18 let $con1_id=`SELECT CONNECTION_ID()`;
19 --source include/start_transaction_high_prio.inc
20 UPDATE t1 SET c1=1 WHERE c1=0;
21
22 --echo
23 --echo # On connection 2
24 --connection con2
25 let $con2_id=`SELECT CONNECTION_ID()`;
26 --send LOCK TABLE t1 WRITE/* From connection 2 */
27
28 --echo
29 --echo # On connection 1
30 --connection con1
31 --disable_query_log
32 eval SET @con2_id = $con2_id;
33 --enable_query_log
34 let $wait_timeout = 10;
35 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
36 WHERE id = @con2_id AND INFO = 'LOCK TABLE t1 WRITE/* From connection 2 */';
37 --source include/wait_condition.inc
38 --sleep 1
39 COMMIT;
40
41 --echo
42 --echo # On connection 2.
43 --connection con2
44 --reap
45 SELECT * FROM t1;
46 UNLOCK TABLES;
47
48 --connection default
49 --let $assert_text= 'There is a 1 in t1'
50 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 1, count, 1] = 1
51 --source include/assert.inc
52
53
54 DROP TABLE t1;
55
56 --disconnect con1
57 --disconnect con2
58
59
60 --echo # Scenario:2
61 --echo # T1= (LOCK TABLE)
62 --echo # T2= ({ W(B)},HIGH_PRIORITY)
63 --echo # Outcome : high prio transaction T2 waits for T1.
64
65 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
66 INSERT INTO t1 VALUES (0);
67
68 --connect(con1,localhost,root,,test)
69 --connect(con2,localhost,root,,test)
70
71 --echo
72 --echo # On connection 2
73 --connection con2
74 let $con2_id=`SELECT CONNECTION_ID()`;
75 LOCK TABLE t1 WRITE/* From connection 2 */;
76 UPDATE t1 SET c1=2 WHERE c1=0;
77
78
79 --echo
80 --echo # On connection 1
81 --connection con1
82 let $con1_id=`SELECT CONNECTION_ID()`;
83 --source include/start_transaction_high_prio.inc
84 --send UPDATE t1 SET c1=1 WHERE c1=0
85
86 --echo # On connection 2
87 --connection con2
88 --disable_query_log
89 eval SET @con1_id = $con1_id;
90 --enable_query_log
91 let $wait_timeout = 10;
92 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
93 WHERE id = @con1_id AND INFO = 'UPDATE t1 SET c1=1 WHERE c1=0';
94 --source include/wait_condition.inc
95 --sleep 1
96 COMMIT;
97 UNLOCK TABLES;
98
99 --connection con1
100 --reap
101 COMMIT;
102
103 --connection default
104 SELECT * FROM t1;
105 DROP TABLE t1;
106
107 --disconnect con1
108 --disconnect con2
109
110
111 --echo # Scenario:3
112 --echo # T1= ({R(B)}, HIGH_PRIORITY)
113 --echo # T2= ({ R(B)},HIGH_PRIORITY)
114 --echo # Outcome: Both T1 and T2 continue as they are read only.
115
116 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
117 INSERT INTO t1 VALUES (0);
118
119 --connect(con1,localhost,root,,test)
120 --connect(con2,localhost,root,,test)
121
122 --echo
123 --echo # On connection 1
124 --connection con1
125 --disable_query_log
126 SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
127 --enable_query_log
128 START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
129 --disable_query_log
130 SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
131 --enable_query_log
132 SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
133
134 --echo
135 --echo # On connection 2
136 --connection con2
137 SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
138 --enable_query_log
139 START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
140 --disable_query_log
141 SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
142 --enable_query_log
143 SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
144 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
145 UPDATE t1 SET c1=1 WHERE c1=0;
146
147 --connection con1
148 COMMIT;
149 --connection con2
150 COMMIT;
151
152 --connection default
153 --let $assert_text= 'There is a 1 in t1'
154 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 0, count, 1] = 1
155 --source include/assert.inc
156
157
158 DROP TABLE t1;
159
160 --disconnect con1
161 --disconnect con2
162
163
164 --echo # Scenario:4
165 --echo # Start of transaction causes table locks acquired with LOCK TABLES to be released
166 --echo # LOCK TABLES Implicitly commit any active transaction
167
168 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
169 CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
170 INSERT INTO t1 VALUES (0);
171 INSERT INTO t2 VALUES (0);
172
173 --connect(con1,localhost,root,,test)
174 --connection con1
175 LOCK TABLE t2 WRITE;
176 --echo # In_use is 1
177 SHOW OPEN TABLES LIKE 't2';
178 --source include/start_transaction_high_prio.inc
179 --echo # In_use is 0 , its released due to start transaction statement
180 SHOW OPEN TABLES LIKE 't2';
181 UPDATE t1 SET c1=1;
182 --echo # Implicitly commits active transaction
183 LOCK TABLE t1 READ;
184 UNLOCK TABLES;
185
186
187 --connection default
188 --let $assert_text= 'There is a 1 in t1'
189 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 1, count, 1] = 1
190 --source include/assert.inc
191
192
193 DROP TABLES t1,t2;
194
195 --disconnect con1
196
197
198 --echo # Scenario:5
199 --echo # Multiple concurrent high prio transactions with the same priority.
200 --echo # Outcome : Transaction which is marked high prio first completes first and other high prio txns wait
201
202 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
203 INSERT INTO t1 VALUES (0);
204
205 --connect(con1,localhost,root,,test)
206 --connect(con2,localhost,root,,test)
207 --connect(con3,localhost,root,,test)
208 --connect(con4,localhost,root,,test)
209 --connect(con5,localhost,root,,test)
210
211 --connection con1
212 --source include/start_transaction_high_prio.inc
213 --echo # connection 1:
214 # statement rollback won't make txn as non high prio
215 --error ER_DUP_ENTRY
216 INSERT INTO t1 VALUES (0);
217 UPDATE t1 SET c1=1 WHERE c1=0;
218
219 --connection con2
220 --source include/start_transaction_high_prio.inc
221 --echo # connection 2: wait - update
222 let $con2_id=`SELECT CONNECTION_ID()`;
223 --send UPDATE t1 SET c1=2 WHERE c1=1 /* From connection 2 */
224
225 --connection con3
226 --source include/start_transaction_high_prio.inc
227 --echo # connection 3: wait - delete
228 --disable_query_log
229 eval SET @con2_id = $con2_id;
230 --enable_query_log
231 let $wait_timeout = 10;
232 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
233 WHERE id = @con2_id AND INFO = 'UPDATE t1 SET c1=2 WHERE c1=1 /* From connection 2 */'
234 AND STATE = 'updating';
235 --source include/wait_condition.inc
236 let $con3_id=`SELECT CONNECTION_ID()`;
237 --sleep 1
238 --send DELETE FROM t1 WHERE c1=1 /* From connection 3 */
239
240 --connection con4
241 --source include/start_transaction_high_prio.inc
242 --echo # connection 4: wait - insert
243 --disable_query_log
244 eval SET @con3_id = $con3_id;
245 --enable_query_log
246 let $wait_timeout = 10;
247 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
248 WHERE id = @con3_id AND INFO = 'DELETE FROM t1 WHERE c1=1 /* From connection 3 */'
249 AND STATE = 'updating';
250 --source include/wait_condition.inc
251 --echo # wait over
252 let $con4_id=`SELECT CONNECTION_ID()`;
253 --sleep 1
254 --send INSERT INTO t1 VALUES (1) /* From connection 4 */
255
256 --connection con5
257 --source include/start_transaction_high_prio.inc
258 --echo # connection 5: Insert goes but as it new row
259 INSERT INTO t1 VALUES (50);
260
261 --connection con1
262 --echo # connection 1:
263 --disable_query_log
264 eval SET @con4_id = $con4_id;
265 --enable_query_log
266 let $wait_timeout = 10;
267 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
268 WHERE id = @con4_id AND INFO = 'INSERT INTO t1 VALUES (1) /* From connection 4 */';
269 --source include/wait_condition.inc
270 --sleep 1
271 COMMIT;
272
273 --connection con2
274 --echo # connection 2: Reap UPDATE t1 SET c1=2 WHERE c1=1
275 --reap
276 COMMIT;
277
278 --connection con3
279 --echo # connection 3: Reap DELETE FROM t1 WHERE c1=1
280 --reap
281 COMMIT;
282
283 --connection con4
284 --echo # connection 4: Reap INSERT INTO t1 VALUES (1)
285 --reap
286 COMMIT;
287
288 --connection con1
289 INSERT INTO t1 VALUES (3);
290 UPDATE t1 SET c1=4 WHERE c1=2;
291 let $con1_id=`SELECT CONNECTION_ID()`;
292
293 # con1 has already done commit hence its no longer high prio txn
294 # Insert waits for con5 to complete.
295 --send INSERT INTO t1 VALUES (50)
296
297 --connection con5
298 # Check con1 insert is waiting
299 --disable_query_log
300 eval SET @con1_id = $con1_id;
301 --enable_query_log
302 let $wait_timeout = 10;
303 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
304 WHERE id = @con1_id AND INFO = 'INSERT INTO t1 VALUES (50)';
305 --source include/wait_condition.inc
306 --sleep 1
307 COMMIT;
308
309 --connection con1
310 --error ER_DUP_ENTRY
311 --reap
312
313 --connection default
314 --let $assert_text= 'There is a 3 in t1'
315 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 3, count, 1] = 1
316 --source include/assert.inc
317 --echo # Expected values : 1,3,4,50
318 SELECT * FROM t1 order by c1;
319
320 DROP TABLES t1;
321
322 --disconnect con1
323 --disconnect con2
324 --disconnect con3
325 --disconnect con4
326 --disconnect con5
327
328
329 --echo # Scenario:6
330 --echo # Mark XA transaction as high prio.
331
332 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
333 INSERT INTO t1 VALUES (0),(1);
334
335 --connect(con1,localhost,root,,test)
336 --connect(con2,localhost,root,,test)
337
338 --connection con2
339 START TRANSACTION;
340 UPDATE t1 SET c1=11 WHERE c1=0;
341
342 --connection con1
343 --disable_query_log
344 SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
345 --enable_query_log
346 XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
347 --disable_query_log
348 SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
349 --enable_query_log
350 UPDATE t1 SET c1=10 WHERE c1=0;
351 XA END 'xa_high_prio';
352 XA PREPARE 'xa_high_prio';
353 XA COMMIT 'xa_high_prio';
354
355 --connection con2
356 --error ER_ERROR_DURING_COMMIT
357 COMMIT;
358
359 --connection default
360 SELECT * FROM t1;
361
362 DROP TABLE t1;
363
364 --disconnect con1
365 --disconnect con2
366
367 --echo # Scenario:7
368 --echo # Multiple XA transaction as high prio.
369 --echo # Outcome : detect deadlock .
370
371 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
372 INSERT INTO t1 VALUES (0),(1);
373
374 --connect(con1,localhost,root,,test)
375 --connect(con2,localhost,root,,test)
376 --connect(con3,localhost,root,,test)
377
378 --connection con2
379 START TRANSACTION;
380 UPDATE t1 SET c1=11 WHERE c1=0;
381
382 --connection con1
383 --disable_query_log
384 SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
385 --enable_query_log
386 XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
387 --disable_query_log
388 SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
389 --enable_query_log
390 UPDATE t1 SET c1=10 WHERE c1=0;
391
392 --connection con3
393 --disable_query_log
394 SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
395 --enable_query_log
396 XA START 'second_xa_high_prio' /* HIGH PRIORITY */ ;
397 --disable_query_log
398 SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
399 --enable_query_log
400 --send UPDATE t1 SET c1=13 WHERE c1=0
401
402 --connection con1
403 XA END 'xa_high_prio';
404 XA PREPARE 'xa_high_prio';
405 XA COMMIT 'xa_high_prio';
406 --disconnect con1
407
408 --connection con2
409 --error ER_ERROR_DURING_COMMIT
410 COMMIT;
411 --disconnect con2
412
413 --connection con3
414 --echo Reap UPDATE t1 SET c1=13 WHERE c1=0;
415 --reap
416 --disconnect con3
417
418 --connection default
419 SELECT * FROM t1;
420
421 DROP TABLE t1;
422
423 --echo #
424 --echo # Bug#21075892 SHUTDOWN HANG, WAITING FOR ASYNC ROLLBACK TO FINISH
425 --echo #
426
427 --connect(con1,localhost,root,,test)
428
429 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE=InnoDB;
430 INSERT INTO t1 values(10, 10), (20, 20), (30, 30);
431
432 --echo #connection 0
433 --connection default
434 START TRANSACTION;
435 --echo #Lock record (col1 = 20)
436 UPDATE t1 set col2 = 25 where col1 = 20;
437
438 --echo #connection 1
439 --connection con1
440 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
441 --source include/start_transaction_high_prio.inc
442
443 --echo #Semi-consistent read: Attempt lock (col1 = 20) and skip in SQL
444 UPDATE t1 SET col2 = 20 where col1 > 10 and col1 <30 and col2 = 30;
445 COMMIT WORK;
446
447 --echo #connection 0
448 --connection default
449 ROLLBACK WORK;
450 DROP TABLE t1;
451
452 --disconnect con1
453
454 --source include/wait_until_count_sessions.inc