MySQL Transaction Tracking in Performance Schema

This article explains how MySQL monitors and records the states of database transactions using its Performance Schema engine. It covers the specific event tables used for tracking, the setup required to enable transaction instrumentation, and how to interpret key transaction columns and states such as active, committed, and rolled back.

Enabling Transaction Instrumentation

By default, MySQL may not have transaction tracking enabled. To collect transaction event data, you must configure the Performance Schema setup tables. This requires enabling the transaction instrument and its corresponding consumers:

-- Enable the transaction instrument
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME = 'transaction';

-- Enable the transaction consumers
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_transactions%';

Once enabled, the Performance Schema begins monitoring the lifecycle of every transaction, including its start, progress, and termination.

The Transaction Event Tables

MySQL records transaction states across three primary tables in the performance_schema database, depending on the recency of the transaction:

• events_transactions_current: Displays the transaction currently in progress, or the most recently completed transaction, for each active thread.
• events_transactions_history: Stores the most recent completed transactions per thread (the default limit is 10 histories per thread).
• events_transactions_history_long: Stores a global history of completed transactions across all threads, up to a globally defined limit.

Key Columns for Tracking States

To understand the exact state and properties of a transaction, you can query these tables and analyze several critical columns:

• STATE: This is the primary indicator of the transaction’s current status. The value can be ACTIVE (the transaction is currently open and executing statements), COMMITTED (the transaction completed successfully), or ROLLED BACK (the transaction was aborted and changes were reverted).
• ACCESS_MODE: Identifies whether the transaction is READ ONLY or READ WRITE.
• ISOLATION_LEVEL: Displays the isolation level under which the transaction is running, such as REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, or SERIALIZABLE.
• AUTOCOMMIT: Indicates whether the transaction was executed under autocommit mode (YES or NO). This helps distinguish between explicit transactions (started with START TRANSACTION) and implicit single-statement transactions.

How the Transaction Lifecycle is Logged

When a transaction begins, MySQL allocates a unique EVENT_ID to it. As the transaction executes, the Performance Schema tracks its progress.

Any SQL statements executed within the scope of that transaction are linked back to it. This relationship is maintained using parent-child nesting. In the statement event tables (like events_statements_current), statements executed inside a transaction will populate the NESTING_EVENT_ID column with the transaction’s EVENT_ID, and the NESTING_EVENT_TYPE will be set to TRANSACTION.

When the transaction finishes via a COMMIT or ROLLBACK statement (or due to an implicit commit like a DDL statement), MySQL updates the transaction’s STATE column in the current events table, records the final timestamp to calculate the transaction’s total latency, and moves the record to the history tables.