Debian Codegraph @

mysql-test/suite/perfschema/r/transaction.result

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