26.12.7 Performance Schema Transaction Tables

The Performance Schema instruments transactions. Within the event hierarchy, wait events nest within stage events, which nest within statement events, which nest within transaction events.

These tables store transaction events:

The following sections describe the transaction event tables. There are also summary tables that aggregate information about transaction events; see Section 26.12.17.5, “Transaction Summary Tables”.

For more information about the relationship between the three transaction event tables, see Section 26.9, “Performance Schema Tables for Current and Historical Events”.

Configuring Transaction Event Collation

To control whether to collect transaction events, set the state of the relevant instruments and consumers:

The transaction instrument and the events_transactions_current and events_transactions_history transaction consumers are enabled by default:

To control transaction event collection at server startup, use lines like these in your my.cnf file:

To control transaction event collection at runtime, update the setup_instruments and setup_consumers tables:

To collect transaction events only for specific transaction event tables, enable the transaction instrument but only the transaction consumers corresponding to the desired tables.

For additional information about configuring event collection, see Section 26.3, “Performance Schema Startup Configuration”, and Section 26.4, “Performance Schema Runtime Configuration”.

Inhoudsopgave Haut

Transaction Boundaries

In MySQL Server, transactions start explicitly with these statements:

Transactions also start implicitly. For example, when the autocommit system variable is enabled, the start of each statement starts a new transaction.

When autocommit is disabled, the first statement following a committed transaction marks the start of a new transaction. Subsequent statements are part of the transaction until it is committed.

Transactions explicitly end with these statements:

Transactions also end implicitly, by execution of DDL statements, locking statements, and server administration statements.

In the following discussion, references to START TRANSACTION also apply to BEGIN, XA START, and XA BEGIN. Similarly, references to COMMIT and ROLLBACK apply to XA COMMIT and XA ROLLBACK, respectively.

The Performance Schema defines transaction boundaries similarly to that of the server. The start and end of a transaction event closely match the corresponding state transitions in the server:

There are subtle implications to this approach:

To illustrate, consider the following scenario:

1. SET autocommit = OFF;
2. CREATE TABLE t1 (a INT) ENGINE = InnoDB;
3. START TRANSACTION;                       -- Transaction 1 START
4. INSERT INTO t1 VALUES (1), (2), (3);
5. CREATE TABLE t2 (a INT) ENGINE = MyISAM; -- Transaction 1 COMMIT
                                            -- (implicit; DDL forces commit)
6. INSERT INTO t2 VALUES (1), (2), (3);     -- Update nontransactional table
7. UPDATE t2 SET a = a + 1;                 -- ... and again
8. INSERT INTO t1 VALUES (4), (5), (6);     -- Write to transactional table
                                            -- Transaction 2 START (implicit)
9. COMMIT;                                  -- Transaction 2 COMMIT

From the perspective of the server, Transaction 1 ends when table t2 is created. Transaction 2 does not start until a transactional table is accessed, despite the intervening updates to nontransactional tables.

From the perspective of the Performance Schema, Transaction 2 starts when the server transitions into an active transaction state. Statements 6 and 7 are not included within the boundaries of Transaction 2, which is consistent with how the server writes transactions to the binary log.

Inhoudsopgave Haut

Transaction Instrumentation

Three attributes define transactions:

To reduce complexity of the transaction instrumentation and to ensure that the collected transaction data provides complete, meaningful results, all transactions are instrumented independently of access mode, isolation level, or autocommit mode.

To selectively examine transaction history, use the attribute columns in the transaction event tables: ACCESS_MODE, ISOLATION_LEVEL, and AUTOCOMMIT.

The cost of transaction instrumentation can be reduced various ways, such as enabling or disabling transaction instrumentation according to user, account, host, or thread (client connection).

Inhoudsopgave Haut

Transactions and Nested Events

The parent of a transaction event is the event that initiated the transaction. For an explicitly started transaction, this includes the START TRANSACTION and COMMIT AND CHAIN statements. For an implicitly started transaction, it is the first statement that uses a transactional engine after the previous transaction ends.

In general, a transaction is the top-level parent to all events initiated during the transaction, including statements that explicitly end the transaction such as COMMIT and ROLLBACK. Exceptions are statements that implicitly end a transaction, such as DDL statements, in which case the current transaction must be committed before the new statement is executed.

Inhoudsopgave Haut

Transactions and Stored Programs

Transactions and stored program events are related as follows:

Inhoudsopgave Haut

Transactions and Savepoints

Savepoint statements are recorded as separate statement events. Transaction events include separate counters for SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT statements issued during the transaction.

Inhoudsopgave Haut

Transactions and Errors

Errors and warnings that occur within a transaction are recorded in statement events, but not in the corresponding transaction event. This includes transaction-specific errors and warnings, such as a rollback on a nontransactional table or GTID consistency errors.