Debian Codegraph @

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

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