View MySQL's recent transaction execution information

Topic: view the recent transaction execution information of MySQL

* although we can query the slow query log to find the total execution time of a statement, if some large transactions in the database are rolled back or terminated abnormally during execution, it will not be recorded in the slow query log at this time.

Performance_schema 's eventstransactions- table to view transaction-related records, in which detailed records of whether transactions have been rolled back, active (transactions that have not been committed for a long time are also active transactions) have been committed and other information. **

The following simulates several transaction situations and looks at the transaction event record table:

Transaction events are not enabled by default. You need to configure them to enable them first.

Root@localhost [performance_schema] > select * from setup_instruments where name like 'transaction' +-+ | NAME | ENABLED | TIMED | +-+ | transaction | NO | NO | +-+ 1 row in set (0 .00 sec) root@localhost [performance_schema] > select * from setup_instruments where name like'% transaction%' +-+ | NAME | | ENABLED | TIMED | +-+-- | -+ | wait/synch/mutex/sql/LOCK_transaction_cache | NO | NO | | stage/sql/Waiting for preceding transaction to commit | NO | NO | | stage/sql/Waiting for dependent transaction to commit | NO | NO | | transaction | | NO | NO | | memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES | NO | | memory/performance_schema/events_transactions_summary_by_host_by_event_name | YES | NO | | memory/performance_schema/events_transactions_summary_by_thread_by_event_name | YES | NO | | memory/performance_schema/events_transactions_ | History | YES | NO | | memory/performance_schema/events_transactions_summary_by_user_by_event_name | YES | NO | | memory/performance_schema/events_transactions_history_long | YES | NO | | memory/sql/THD::transactions::mem_root | YES | NO | +-+ 11 rows in set (0.00 sec)

View recent transaction execution information:

Parameters to enable the transaction producer:

Root@localhost [performance_schema] > update setup_instruments set enabled='yes',timed='yes' where name like 'transaction';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@localhost [performance_schema] > update setup_instruments set enabled='yes' where name like'% transaction%';Query OK, 3 rows affected (0.00 sec) Rows matched: 11 Changed: 3 Warnings: 0

Log in to mysql session 1 and perform cleanup to avoid interference from other transactions:

Tip: online, it is best not to clear the truncate directly.

Root@localhost [performance_schema] > truncate events_transactions_current;truncate events_transactions_history; truncate events_transactions_history_long;root@localhost [performance_schema] > select * from events_transactions_current;Empty set (0.00 sec) root@localhost [performance_schema] > select * from events_transactions_history;Empty set (0.00 sec) root@localhost [performance_schema] > select * from events_transactions_history_long;Empty set (0.00 sec)

Open a new session 2 to execute the transaction and simulate the rollback of the transaction:

Root@localhost [test001] > begin;Query OK, 0 rows affected (0.00 sec) root@localhost [test001] > update test1_event set username='fox' where id=1;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@localhost [test001] > rollback;Query OK, 0 rows affected (0.00 sec)

View the currently active transaction in session 1, but the result is null:

Root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\ GEmpty set (0.00 sec) root@localhost [(none)] > root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID NESTING_EVENT_TYPE from events_transactions_history_long\ GEmpty set (0.00 sec) root@localhost [(none)] > root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_historyEmpty set (0.00 sec) root@localhost [(none)] >

The result is null because the MySQL consumer parameter setup_consumers is not enabled.

Root@localhost [performance_schema] > select * from setup_consumers where name like'% transaction%' +-- +-+ | NAME | ENABLED | +-- +-+ | events_transactions_current | NO | | events_transactions_history | NO | | events_transactions_history_long | NO | +-- +-+ 3 rows in set (0.01sec) |

Enable consumer parameters:

Root@localhost [performance_schema] > update setup_consumers set enabled='yes' where name like'% transaction%';Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0root@localhost [performance_schema] > select * from setup_consumers where name like'% transaction%' +-- +-+ | NAME | ENABLED | +-- +-+ | events_transactions_current | YES | | events_transactions_history | YES | | events_transactions_history_long | YES | +-- +-+ 3 rows in set (0.00 sec) |

Remember to close these consumption parameters after analyzing the transaction. The shutdown command is as follows:

Update setup_consumers set enabled='no' where name like'% transaction%'

View the currently active transactions in session 1 again:

Root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID NESTING_EVENT_TYPE from events_transactions_current\ transaction STATE * 1. Row * * THREAD_ID: 3957325 EVENT_NAME: transaction STATE: ACTIVE TRX_ID: NULL GTID: AUTOMATIC SOURCE: TIMER_WAIT: 85087447373000 ACCESS_MODE: READ WRITE ISOLATION_LEVEL: REPEATABLE READ AUTOCOMMIT: NO NESTING_EVENT_ID: 27NESTING_EVENT_TYPE: STATEMENT1 row in set (0.00 sec)

Rollback rolls back the transaction on session 2:

Root@localhost [test001] > begin;Query OK, 0 rows affected (0.00 sec) root@localhost [test001] > update test1_event set username='fox' where id=1;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@localhost [test001] > rollback;Query OK, 0 rows affected (0.00 sec)

View the currently active transactions in session 1 again:

Root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID NESTING_EVENT_TYPE from events_transactions_current\ transaction STATE * 1. Row * * THREAD_ID: 3957325 EVENT_NAME: transaction STATE: ROLLED BACK TRX_ID: NULL GTID: AUTOMATIC SOURCE: TIMER_WAIT: 170837979344000 ACCESS_MODE: READ WRITE ISOLATION_LEVEL: REPEATABLE READ AUTOCOMMIT: NO NESTING_EVENT_ID: 27NESTING_EVENT_TYPE: STATEMENT1 row in set (0.00 sec)

View the transaction event history table: events_transactions_history:

Root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID NESTING_EVENT_TYPE from events_transactions_history\ transaction STATE * 1. Row * * THREAD_ID: 3957325 EVENT_NAME: transaction STATE: ROLLED BACK TRX_ID: NULL GTID: AUTOMATIC SOURCE: TIMER_WAIT: 170837979344000 ACCESS_MODE: READ WRITE ISOLATION_LEVEL: REPEATABLE READ AUTOCOMMIT: NO NESTING_EVENT_ID: 27NESTING_EVENT_TYPE: STATEMENT2 rows in set (0.00 sec) root@localhost [test001] > select THREAD_ID EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID NESTING_EVENT_TYPE from performance_schema.events_transactions_history_long where STATE='ROLLED BACK'\ transaction STATE * 1. Row * * THREAD_ID: 3957325 EVENT_NAME: transaction STATE: ROLLED BACK TRX_ID: NULL GTID: AUTOMATIC SOURCE: TIMER_WAIT: 170837979344000 ACCESS_MODE: READ WRITE ISOLATION_LEVEL: REPEATABLE READ AUTOCOMMIT: NO NESTING_EVENT_ID: 27NESTING_EVENT_TYPE: STATEMENT1 row in set (0.00 sec)

+ + = +

Simulate a normal commit transaction on session 2:

Start a transaction

Root@localhost [test001] > begin;Query OK, 0 rows affected (0.00 sec) root@localhost [test001] > update test1_event set username='fox' where id=1;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

View transaction information on session 1:

Root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID NESTING_EVENT_TYPE from events_transactions_current\ transaction STATE * 1. Row * * THREAD_ID: 3957325 EVENT_NAME: transaction STATE: ACTIVE TRX_ID: NULL GTID: AUTOMATIC SOURCE: TIMER_WAIT: 18063808685000 ACCESS_MODE: READ WRITE ISOLATION_LEVEL: REPEATABLE READ AUTOCOMMIT: NO NESTING_EVENT_ID: 75NESTING_EVENT_TYPE: STATEMENT1 row in set (0.00 sec)

Commit the transaction normally on session 2

Root@localhost [test001] > commit

Query OK, 0 rows affected (0.00 sec)

View transaction information on session 1:

Root@localhost [performance_schema] > root@localhost [performance_schema] > select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID NESTING_EVENT_TYPE from events_transactions_current\ transaction STATE * 1. Row * * THREAD_ID: 3957325 EVENT_NAME: transaction STATE: COMMITTED # TRX_ID: NULL GTID: 64a062d1-2e92-11ea-847e-801844ed7bbc:2584551 # # SOURCE: TIMER_WAIT: 28245631402000 ACCESS_MODE: READ WRITE ISOLATION_LEVEL: REPEATABLE READ AUTOCOMMIT: NO NESTING_EVENT_ID: 75NESTING_EVENT_TYPE: STATEMENT1 row in set (0.00 sec) to record Gtid after normal transaction submission

Tip: if a transaction has not been committed for a long time (ACTIVE for a long time) in this case, we can look at the table nformation_schema.innodb_trx table to see when the transaction started.

And the thread id number trx_mysql_thread_id: 3957292 of the mysql that generated the transaction (that is, the thread id number of session 2)

Root@localhost [performance_schema] > select * from information_schema.innodb_trx\ gateway * 1. Row * * trx_id: 2594744 trx_state: RUNNING trx_started: 2020-02- 13 17:53:23 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 3957292

For more information on the meaning of performance_schema, please refer to the following materials:

Configuration details | Comprehensive introduction to performance_schema (2)

Http://www.sohu.com/a/253338003_610509

A collection of application examples | A comprehensive introduction to performance_schema

Https://blog.csdn.net/woqutechteam/article/details/81114520