Debian Codegraph @

mysql-test/suite/perfschema/t/transaction.test

raw
1 ############# suite/perfschema/t/transaction.test ####################
2 # #
3 # Test processing of transaction events by the Performance Schema, #
4 # including explicit/implicit transactions, access modes, isolation #
5 # levels, statement counts and state transitions. #
6 # #
7 # #
8 ######################################################################
9
10 --source include/have_perfschema.inc
11 --source include/not_embedded.inc
12 --source include/have_innodb.inc
13 --source include/no_protocol.inc
14 --disable_query_log
15 --source ../include/transaction_setup.inc
16 --enable_query_log
17 set global binlog_format=ROW;
18
19 --echo #
20 --echo # ========================================================================
21 --echo # STEP 1 - SETUP
22 --echo # ========================================================================
23
24 --echo #
25 --echo # Control thread
26 --echo #
27 --connection default
28 SET SESSION AUTOCOMMIT= 1;
29 eval $get_thread_id;
30 let $default_thread_id= `SELECT @my_thread_id`;
31
32 --echo #
33 --echo # Connection 1
34 --echo #
35 connect(con1, localhost, root,,);
36 --disable_query_log
37 eval $get_thread_id;
38 let $con1_thread_id= `SELECT @my_thread_id`;
39 --enable_query_log
40 SET SESSION AUTOCOMMIT= 0;
41 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
42
43 --disable_parsing
44 --echo #
45 --echo # Connection 2
46 --echo #
47 connect(con2, localhost, root,,);
48 --disable_query_log
49 eval $get_thread_id;
50 let $con2_thread_id= `SELECT @my_thread_id`;
51 --enable_query_log
52 SET SESSION AUTOCOMMIT= 0;
53 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
54 --enable_parsing
55
56 --connection default
57 --disable_query_log
58 eval SET @con1_thread_id= $con1_thread_id;
59 #eval SET @con2_thread_id= $con2_thread_id;
60 --enable_query_log
61
62 --echo #
63 --echo # Create test tables, one transactional and one non-transactional
64 --echo #
65 --connection default
66
67 --disable_warnings
68 DROP TABLE IF EXISTS t1;
69 DROP TABLE IF EXISTS nt1;
70 --enable_warnings
71 CREATE TABLE t1 (s1 int, s2 varchar(64)) ENGINE=INNODB;
72 CREATE TABLE nt1 (s1 int, s2 varchar(64)) ENGINE=MYISAM;
73
74 --echo #
75 --echo # Disable all events from the control thread
76 --echo #
77 --disable_query_log
78 UPDATE performance_schema.threads
79 SET instrumented='NO' WHERE processlist_id = CONNECTION_ID();
80 --enable_query_log
81
82 --echo #
83 --echo # Clear transaction tables
84 --echo #
85 CALL clear_transaction_tables();
86
87 --echo #
88 --echo # ========================================================================
89 --echo # STEP 2 - BASIC TRANSACTION
90 --echo # ========================================================================
91 --echo #
92 --connection con1
93 SELECT @@global.transaction_isolation;
94 SELECT @@global.autocommit;
95 SELECT @@global.binlog_format;
96 SELECT @@transaction_isolation;
97 SELECT @@autocommit;
98 SELECT @@binlog_format;
99
100 --echo #
101 --echo # STEP 2.1 - START/COMMIT
102 --echo #
103 START TRANSACTION;
104 INSERT INTO t1 VALUES (101, 'COMMITTED');
105 COMMIT;
106
107 --echo #
108 --echo # STEP 2.2 - ROLLBACK
109 --echo #
110 START TRANSACTION;
111 INSERT INTO t1 VALUES (102, 'ROLLED BACK');
112 ROLLBACK;
113
114 --echo #
115 --echo ## Expect 1 committed and 1 rolled back transaction
116 --connection default
117 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
118 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
119 CALL clear_transaction_tables();
120
121 --echo #
122 --echo # STEP 2.3 - COMMIT AND CHAIN
123 --echo #
124 --connection con1
125 START TRANSACTION;
126 INSERT INTO t1 VALUES (103, 'COMMIT AND CHAIN');
127 COMMIT AND CHAIN;
128 INSERT INTO t1 VALUES (104, 'COMMIT AND CHAIN');
129 COMMIT;
130
131 --echo #
132 --echo ## Expect 2 committed transactions
133 --connection default
134 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 2);
135 CALL clear_transaction_tables();
136
137 --echo #
138 --echo # ========================================================================
139 --echo # STEP 3 - ISOLATION LEVEL
140 --echo # ========================================================================
141 --echo #
142 --echo # connection con1
143 --connection con1
144 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
145 START TRANSACTION;
146 INSERT INTO t1 VALUES (301, 'SERIALIZABLE');
147 COMMIT;
148
149 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
150 START TRANSACTION;
151 INSERT INTO t1 VALUES (302, 'REPEATABLE READ');
152 COMMIT;
153
154 ## NOTE - InnoDB requires binlog_format = ROW for READ COMMITTED/UNCOMMITTED
155 SELECT @@binlog_format INTO @binlog_save;
156 SET SESSION BINLOG_FORMAT=ROW;
157 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
158 START TRANSACTION;
159 INSERT INTO t1 VALUES (303, 'READ COMMITTED');
160 COMMIT;
161
162 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
163 START TRANSACTION;
164 INSERT INTO t1 VALUES (304, 'READ UNCOMMITTED');
165 COMMIT;
166
167 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
168 SET binlog_format= @binlog_save;
169
170 --echo #
171 --connection default
172 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'SERIALIZABLE', 'NO', 0, 0, 0, 1);
173 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
174 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'READ COMMITTED', 'NO', 0, 0, 0, 1);
175 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', 'READ UNCOMMITTED', 'NO', 0, 0, 0, 1);
176 CALL clear_transaction_tables();
177
178 --echo #
179 --echo # ========================================================================
180 --echo # STEP 4 - ACCESS MODE
181 --echo # ========================================================================
182 --echo #
183 --echo # STEP 4.1 - READ ONLY, TIMING ENABLED
184 --echo #
185 --echo #
186 --echo # connection con1
187 --connection con1
188 SET SESSION TRANSACTION READ WRITE;
189 START TRANSACTION;
190 INSERT INTO t1 VALUES (410, 'READ ONLY');
191 INSERT INTO t1 VALUES (411, 'READ ONLY');
192 INSERT INTO t1 VALUES (412, 'READ ONLY');
193 INSERT INTO t1 VALUES (413, 'READ ONLY');
194 COMMIT;
195
196 SET SESSION TRANSACTION READ ONLY;
197 START TRANSACTION;
198 SELECT * FROM t1 ORDER BY s1;
199 COMMIT;
200
201 --echo #
202 --echo ## Expect 1 read only, committed transaction in events_transactions_history
203 --connection default
204 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ ONLY', 'REPEATABLE READ', 'NO', 0, 0, 0, 1);
205 CALL clear_transaction_tables();
206
207 --echo #
208 --echo #
209 --echo # STEP 4.2 - READ ONLY, TIMING DISABLED
210 --echo #
211 --echo #
212 --echo ## Disable timing stats for 'transaction'
213 UPDATE performance_schema.setup_instruments SET timed = 'NO' WHERE name = 'transaction';
214 --echo #
215 TRUNCATE performance_schema.events_transactions_summary_global_by_event_name;
216
217 --echo # connection con1
218 --connection con1
219 START TRANSACTION;
220 SELECT * FROM t1 ORDER BY s1;
221 COMMIT;
222 --echo #
223 SET SESSION TRANSACTION READ WRITE;
224
225 --connection default
226 --echo #
227 --echo ## Expect 1 event, 0 stats
228 SELECT * FROM performance_schema.events_transactions_summary_global_by_event_name;
229 --echo #
230 --echo ## Restore setup_instruments
231 UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name = 'transaction';
232
233 --echo #
234 --echo # ========================================================================
235 --echo # STEP 5 - IMPLICIT START
236 --echo # ========================================================================
237 --echo # When AUTOCOMMIT is disabled, the first statement following a committed
238 --echo # transaction marks the start of a new transaction. Subsequent statements will
239 --echo # be part of the transaction until it is committed.
240 --echo #
241 --connection con1
242 SET SESSION AUTOCOMMIT = 0;
243 INSERT INTO t1 VALUES (501, 'IMPLICIT START');
244
245 --echo #
246 --echo ## Expect 1 active transaction in events_transactions_current
247 --connection default
248 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
249
250 --connection con1
251 INSERT INTO t1 VALUES (502, 'IMPLICIT START');
252 COMMIT;
253
254 --echo #
255 --echo ## Expect one committed transaction in events_transactions_current and history
256 --connection default
257 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
258 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
259 CALL clear_transaction_tables();
260
261 --echo #
262 --echo # ========================================================================
263 --echo # STEP 6 - IMPLICIT COMMIT (DDL, ETC)
264 --echo # ========================================================================
265 --echo # Transactions are implicitly ended by DDL statements, locking statements
266 --echo # and server administration commands.
267 --echo #
268 --connection con1
269 SET SESSION AUTOCOMMIT = 0;
270 INSERT INTO t1 VALUES (601, 'IMPLICIT COMMIT');
271
272 --echo #
273 --echo ## Expect one active transaction in events_transactions_current, zero events in history
274 --connection default
275 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
276 CALL transaction_verifier(1, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', 'NO', 0, 0, 0, 0);
277
278 --connection con1
279 INSERT INTO t1 VALUES (602, 'IMPLICIT COMMIT');
280
281 --echo ## Issue a DDL statement to force a commmit
282 CREATE TABLE t2 (s1 INT, s2 VARCHAR(64)) ENGINE=INNODB;
283
284 --echo #
285 --echo ## Expect 0 active transactions, 1 committed transaction
286 --connection default
287 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 0);
288 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
289 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
290 CALL clear_transaction_tables();
291 DROP TABLE test.t2;
292
293 --echo #
294 --echo # ========================================================================
295 --echo # STEP 7 - XA TRANSACTIONS
296 --echo # ========================================================================
297 --echo #
298 --echo # STEP 7.1 - XA START
299 --echo #
300 --connection con1
301 XA START 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
302
303 --echo #
304 --echo ## Expect 1 active XA transaction, state ACTIVE
305 --connection default
306
307 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'ACTIVE', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
308
309 --echo #
310 --echo # STEP 7.2 - XA END
311 --echo #
312 --connection con1
313 INSERT INTO t1 VALUES (701, 'XA');
314 XA END 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
315
316 --echo #
317 --echo ## Expect 1 active XA transaction, state IDLE
318 --connection default
319 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'IDLE', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
320
321 --echo #
322 --echo #
323 --echo # STEP 7.3 - XA PREPARE
324 --echo #
325 --connection con1
326 XA PREPARE 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
327
328 --echo #
329 --echo ## Expect 1 active XA transaction, state PREPARED
330 --connection default
331 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'PREPARED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
332
333 --echo #
334 --echo #
335 --echo # STEP 7.4 - XA COMMIT
336 --echo #
337 --connection con1
338 XA COMMIT 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 1234567890;
339
340 --echo #
341 --echo ## Expect 1 committed XA transaction, state COMMITTED in current and history
342 --connection default
343 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
344 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'XA_CON1_GTRID_001', 'XA_CON1_BQUAL_001', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
345
346 CALL clear_transaction_tables();
347
348 --echo #
349 --echo #
350 --echo # STEP 7.5 - XA ROLLBACK
351 --echo #
352 --connection con1
353 XA START 'XA_CON1_002';
354 INSERT INTO t1 VALUES (702, 'XA');
355 XA END 'XA_CON1_002';
356 XA PREPARE 'XA_CON1_002';
357
358 --echo #
359 --echo ## Expect 1 active XA transaction, state PREPARED
360 --connection default
361 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, 'XA_CON1_002', '', 'PREPARED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
362
363 --connection con1
364 XA ROLLBACK 'XA_CON1_002';
365
366 --echo #
367 --echo ## Expect 1 XA transaction, state ROLLBACK ONLY in current and history
368 --connection default
369 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, 'XA_CON1_002', '', 'ROLLBACK ONLY', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
370 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, 'XA_CON1_002', '', 'ROLLBACK ONLY', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
371 CALL clear_transaction_tables();
372 SELECT * FROM performance_schema.events_transactions_current ORDER BY event_id;
373
374 --echo #
375 --echo # STEP 7.6 - XA TRANSACTION - LONG GTRID AND BQUAL
376 --echo #
377 --connection con1
378 XA START 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
379 INSERT INTO t1 VALUES (703, 'XA LONG');
380 XA END 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
381 XA PREPARE 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
382 XA COMMIT 'GTRID_6789012345678901234567890123456789012345678901234567890123','BQUAL_6789012345678901234567890123456789012345678901234567890123',1234567890;
383
384 --echo #
385 --echo ## Expect 1 committed XA transaction, state COMMITTED in current and history
386 --connection default
387 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', 'BQUAL_6789012345678901234567890123456789012345678901234567890123', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
388 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', 'BQUAL_6789012345678901234567890123456789012345678901234567890123', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
389 CALL clear_transaction_tables();
390
391 --echo #
392 --echo # STEP 7.7 - XA TRANSACTION - LONG GTRID AND BINARY BQUAL
393 --echo #
394 --connection con1
395 XA START 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
396 INSERT INTO t1 VALUES (704, 'XA LONG/BINARY');
397 XA END 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
398 XA PREPARE 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
399 XA COMMIT 'GTRID_6789012345678901234567890123456789012345678901234567890123',0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233,1234567890;
400 --echo #
401 --echo ## Expect 1 committed XA transaction, state COMMITTED in current and history
402 --connection default
403 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', '0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
404 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 1234567890, 'GTRID_6789012345678901234567890123456789012345678901234567890123', '0x425155414C5FA5A53839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233', 'COMMITTED', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
405 CALL clear_transaction_tables();
406
407 --echo #
408 --echo # ========================================================================
409 --echo # STEP 8 - TRANSACTIONAL AND NON-TRANSACTIONAL TABLES
410 --echo # ========================================================================
411 --echo #
412
413 ## Statements that work with non-transactional engines have no effect on the
414 ## transaction state of the connection. For implicit transactions,
415 ## the transaction event begins with the first statement that uses a
416 ## transactional engine. This means that statements operating exclusively on
417 ## non-transactional tables will be ignored, even following START TRANSACTION.
418
419 --connection con1
420 SET SESSION AUTOCOMMIT = 0;
421 SELECT * FROM performance_schema.events_transactions_current ORDER BY event_id;
422
423 --echo #
424 --echo #
425 --echo # STEP 8.1 - UPDATE NON-TRANSACTIONAL TABLE
426 --echo #
427 INSERT INTO nt1 VALUES (801, 'NON-TRANSACTIONAL');
428
429 --echo #
430 --echo ## Expect 0 transactions in events_transactions_current
431 --connection default
432 CALL transaction_verifier(0, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
433
434 --connection con1
435 COMMIT;
436
437 --echo #
438 --echo ## Expect 0 transactions in events_transactions_history
439 --connection default
440 CALL transaction_verifier(1, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
441
442 --echo #
443 --echo #
444 --echo # STEP 8.2 - UPDATE TRANSACTIONAL AND NON-TRANSACTIONAL TABLES
445 --echo #
446 --echo #
447 --echo ## First non-transactional...
448 --echo #
449 --connection con1
450 INSERT INTO nt1 VALUES (802, 'NON-TRANSACTIONAL');
451
452 --echo #
453 --echo ## Expect 0 transactions in events_transactions_current
454 --connection default
455 CALL transaction_verifier(0, @con1_thread_id, 'transaction', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
456
457 --echo #
458 --echo ## Now transactional. Transaction should be started.
459 --connection con1
460 INSERT INTO t1 VALUES (802, 'TRANSACTIONAL');
461
462 --echo #
463 --echo ## Expect 1 transaction in events_transactions_current
464 --connection default
465 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
466
467 ## Commit
468 --echo #
469 --connection con1
470 COMMIT;
471
472 --echo #
473 --echo ## Expect 1 committed transaction in events_transactions_current and history
474 --connection default
475 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
476 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
477 CALL clear_transaction_tables();
478
479 --echo #
480 --echo # ========================================================================
481 --echo # STEP 9 - SAVEPOINTS
482 --echo # ========================================================================
483 --echo #
484 --echo # STEP 9.1 - SAVEPOINT 1
485 --echo #
486 --connection con1
487 START TRANSACTION;
488 INSERT INTO t1 VALUES (901, 'SAVEPOINT');
489 SAVEPOINT SVP001;
490
491 --echo #
492 --echo ## Expect 1 active transaction with 1 savepoint
493 --connection default
494 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 1, 0, 0, 1);
495
496 --echo #
497 --echo #
498 --echo # STEP 9.2 - SAVEPOINTS 2 and 3
499 --echo #
500 --connection con1
501 INSERT INTO t1 VALUES (902, 'SAVEPOINT');
502 SAVEPOINT SVP002;
503 INSERT INTO t1 VALUES (903, 'SAVEPOINT');
504 SAVEPOINT SVP003;
505 INSERT INTO t1 VALUES (904, 'SAVEPOINT');
506 SELECT COUNT(*) FROM t1 WHERE s1 > 900;
507
508 --echo #
509 --echo #
510 --echo # STEP 9.3 - ROLLBACK TO SAVEPOINT 2
511 --echo #
512 --connection con1
513 ROLLBACK TO SVP002;
514
515 --echo #
516 --echo ## Expect 1 active transaction with 3 savepoints, 1 rollback to savepoint
517 --connection default
518 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 3, 1, 0, 1);
519
520 --echo #
521 --echo #
522 --echo # STEP 9.4 - RELEASE SAVEPOINT 1
523 --echo #
524 --connection con1
525 RELEASE SAVEPOINT SVP001;
526
527 --echo #
528 --echo ## Expect 1 active transaction with 3 savepoints, 1 rollback to savepoint, 1 release savepoint
529 --connection default
530 CALL transaction_verifier(0, @con1_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 3, 1, 1, 1);
531
532 --echo #
533 --echo # STEP 9.5 - COMMIT
534 --echo #
535 --connection con1
536 COMMIT;
537
538 --echo #
539 --echo ## Expect 1 committed transaction with 3 savepoints, 1 rollback to savepoint, 1 release savepoint
540 --connection default
541 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', 'READ WRITE', '', 'NO', 3, 1, 1, 1);
542 CALL clear_transaction_tables();
543
544 --echo #
545 --echo # ========================================================================
546 --echo # STEP 10 - GTIDs
547 --echo # ========================================================================
548 --echo # GTIDs are tested in transaction_gtid.test.
549
550 --echo #
551 --echo # ========================================================================
552 --echo # STEP 11 - MISCELLANY
553 --echo # ========================================================================
554 --echo #
555 --echo # STEP 11.1 - TRUNCATE DURING ACTIVE TRANSACTION
556 --echo #
557 --echo #
558 --echo # Verify that truncating events_transactions_current during an active transaction
559 --echo # does not leave an orphaned transaction event, and that the row index to
560 --echo # events_transactions_history is reset to 0.
561 --echo #
562 --connection con1
563 START TRANSACTION;
564 INSERT INTO t1 VALUES (1110, 'INSERT 1110');
565 --connection default
566 TRUNCATE performance_schema.events_transactions_current;
567 --connection con1
568 --echo #
569 COMMIT;
570 --echo #
571 START TRANSACTION;
572 INSERT INTO t1 VALUES (1111, 'INSERT 1111');
573 COMMIT;
574
575 --echo #
576 --echo ## Expect 1 transaction for connection 1
577 --connection default
578 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', '', '', '', 0, 0, 0, 1);
579 CALL clear_transaction_tables();
580
581 --echo #
582 --echo #
583 --echo # STEP 11.2 - DISABLE THREAD INSTRUMENTATION
584 --echo #
585 --connection default
586 UPDATE performance_schema.setup_consumers
587 SET enabled = 'NO'
588 WHERE name = 'thread_instrumentation';
589 --echo #
590 TRUNCATE performance_schema.events_transactions_summary_global_by_event_name;
591
592 --connection con1
593 --echo #
594 START TRANSACTION;
595 INSERT INTO t1 VALUES (1120, 'INSERT 1120');
596 COMMIT;
597
598 --connection default
599 --echo #
600 --echo ## Expect 1 event with non-zero summary stats
601 --replace_column 3 sum_timer_wait 4 min_timer_wait 5 avg_timer_wait
602 SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, COUNT_READ_WRITE
603 FROM performance_schema.events_transactions_summary_global_by_event_name
604 WHERE count_star = 1 and sum_timer_wait != 0;
605
606 --echo #
607 --echo ## Disable timing stats for 'transaction'
608 UPDATE performance_schema.setup_instruments SET timed = 'NO' WHERE name = 'transaction';
609 --echo #
610 TRUNCATE performance_schema.events_transactions_summary_global_by_event_name;
611
612 --connection default
613 --echo #
614 START TRANSACTION;
615 INSERT INTO t1 VALUES (1121, 'INSERT 1121');
616 COMMIT;
617
618 --connection default
619 --echo #
620 --echo ## Expect 1 event, 0 stats
621 SELECT * FROM performance_schema.events_transactions_summary_global_by_event_name;
622
623 --echo #
624 --echo ## Restore setup_consumers and setup_instruments
625 UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'thread_instrumentation';
626 UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name = 'transaction';
627 --echo #
628 DELETE FROM t1;
629 CALL clear_history();
630
631 --echo #
632 --echo #
633 --echo # STEP 11.3 - STATEMENT ROLLBACK - AUTOCOMMIT OFF - BINLOG FORMAT 'STATEMENT'
634 --echo #
635 --connection con1
636 SET SESSION binlog_format = STATEMENT;
637 SET SESSION AUTOCOMMIT = 0;
638
639 # A transaction with a rolled back statement should not show as rolled back.
640 #
641 # Force a statement rollback by attempting to update a transactional table
642 # and a non-replicatable table with binlog_format = STATEMENT.
643
644 --echo #
645 START TRANSACTION;
646 INSERT INTO t1 VALUES (1130, 'INSERT 1130');
647 --echo #
648 --echo ## Expect binlog statement mode error
649 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
650 UPDATE t1, performance_schema.setup_instruments pfs
651 SET t1.s1 = 1, pfs.timed = 'NO';
652 --echo #
653 COMMIT;
654 --echo #
655 SET SESSION AUTOCOMMIT = 1;
656
657 --echo #
658 --echo ## Expect 1 committed transaction
659 --connection default
660 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'COMMITTED', 0, '', '', '', '', '', '', 'NO', 0, 0, 0, 1);
661 --echo #
662 DELETE FROM t1;
663 CALL clear_history();
664
665 --echo #
666 --echo #
667 --echo # STEP 11.4 - STATEMENT ROLLBACK - AUTOCOMMIT ON - BINLOG FORMAT 'STATEMENT'
668 --echo #
669 --connection con1
670 SET SESSION binlog_format = STATEMENT;
671 SET SESSION AUTOCOMMIT = 1;
672
673 # A rolled back autocommit statement should be recorded as a rolled back transaction
674 #
675 # Force a statement rollback by attempting to update a transactional table
676 # and a non-replicatable table with binlog_format = STATEMENT.
677 --echo #
678 --echo ## Expect binlog statement mode error
679 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
680 UPDATE t1, performance_schema.setup_instruments pfs
681 SET t1.s1 = 1, pfs.timed = 'NO';
682
683 --echo #
684 --echo ## Expect 1 rolled back transaction
685 --connection default
686 CALL transaction_verifier(1, @con1_thread_id, 'transaction', 'ROLLED BACK', 0, '', '', '', '', '', '', 'YES', 0, 0, 0, 1);
687 CALL clear_history();
688
689 --disable_parsing
690 # TODO: Add wait timer
691 --echo #
692 --echo #
693 --echo # STEP 11.5 - DROPPED CONNECTION DURING TRANSACTION
694 --echo #
695 --connection con2
696 START TRANSACTION;
697 INSERT INTO t1 VALUES (1150, 'DROP CONNECTION');
698 --echo #
699 --echo ## Expect 1 active transaction for connection 2
700 --connection default
701 CALL transaction_verifier(0, @con2_thread_id, 'transaction', 'ACTIVE', 0, '', '', '', '', 'READ WRITE', '', 'NO', 0, 0, 0, 1);
702
703 --echo #
704 --echo ## Drop connection
705 --connection con2
706 --disconnect con2
707 --source include/wait_until_disconnected.inc
708 --connection default
709
710 --echo #
711 --echo ## Expect 0 transactions for connection 2
712 CALL transaction_verifier(0, @con2_thread_id, '', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
713 CALL transaction_verifier(1, @con2_thread_id, '', '', 0, '', '', '', '', '', '', '', 0, 0, 0, 0);
714 CALL clear_transaction_tables();
715
716 --enable_parsing
717
718 --echo #
719 --echo # ========================================================================
720 --echo # CLEAN UP
721 --echo # ========================================================================
722 --echo #
723
724 --disconnect con1
725 ##--disconnect con2
726
727 --connection default
728 DROP TABLE t1;
729 DROP TABLE nt1;
730
731 --source ../include/transaction_cleanup.inc
732 set global binlog_format=default;