MySQL 查看最近的事务执行信息
MySQL版本：5.7.31
根据《MySQL性能优化金字塔法则》做的实验，好书推荐！
我们可以通过慢查询日志查询到一条语句的执行总时长，但是如果数据库中存在着一些大事务在执行过程中回滚了，或者在执行过程中异常中止，这个时候慢查询日志就不顶用了，这时可以借助performance_schema和events_transactions_*表来查看与事务相关的记录信息。
events_transactions_*表中详细记录了是否有事务被回滚，事务是否活跃（长时间未提交的事务也属于活跃事务），事务是否提交等信息。
一、查看事务记录功能是否开启
记录事务信息的表一般为performance_schema中的3张表，分别为
events_transactions_current，默认记录每个线程最近的一个事务信息events_transactions_history，默认记录每个线程最近的十个事务信息events_transactions_history_long，默认记录每个线程最近的10000个事务信息
mysql> select * from performance_schema.setup_consumers where name like 'events_transactions%';
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_transactions_current | YES |
| events_transactions_history | NO | < -- 未开启
| events_transactions_history_long | NO | < -- 未开启
+----------------------------------+---------+
二、开启事务记录功能
备注：可以通过修改performance_schema.threads表中的配置，针对特定的线程进行记录信息，降低对性能的影响程度
mysql> update setup_consumers set enabled='YES' where name in ('events_transactions_history','events_transactions_history_long');
三、模拟
3.1 模拟活跃事务的查看
开启会话1窗口
mysql> begin;
mysql> use sbtest;
mysql> update sbtest1 set pad='yyy' where id =1;
mysql> select sys.ps_thread_id(connection_id());
+-----------------------------------+
| sys.ps_thread_id(connection_id()) |
+-----------------------------------+
| 173799 |
+-----------------------------------+
会话2，查看
mysql> select * from performance_schema.events_transactions_current where thread_id=173799\G;
*************************** 1. row ***************************
THREAD_ID: 173799
EVENT_ID: 3878
END_EVENT_ID: NULL
EVENT_NAME: transaction
STATE: ACTIVE <--- 活跃
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: NULL
XID_GTRID: NULL
XID_BQUAL: NULL
XA_STATE: NULL
SOURCE:
TIMER_START: 1052739637991597000
TIMER_END: 1052859374760511000
TIMER_WAIT: 119736768914000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: READ COMMITTED
AUTOCOMMIT: NO
NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
NUMBER_OF_RELEASE_SAVEPOINT: 0
OBJECT_INSTANCE_BEGIN: NULL
NESTING_EVENT_ID: 3871
NESTING_EVENT_TYPE: STATEMENT
3.2 模拟回滚事务的查看（提交同理）
会话1 我们将3.1的会话1的事务回滚掉
-- mysql> begin;
-- mysql> use sbtest;
-- mysql> update sbtest1 set pad='yyy' where id =1;
-- mysql> select sys.ps_thread_id(connection_id());
+-----------------------------------+
| sys.ps_thread_id(connection_id()) |
+-----------------------------------+
| 173799 |
+-----------------------------------+
mysql> rollback;
会话2 我们查看事务信息历史表，可以看到当前线程被回滚的ID
mysql> select * from performance_schema.events_transactions_history where thread_id=173799\G;
*************************** 1. row ***************************
THREAD_ID: 173799
EVENT_ID: 3878
END_EVENT_ID: 4139
EVENT_NAME: transaction
STATE: ROLLED BACK <--- 已经回滚了
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: NULL
XID_GTRID: NULL
XID_BQUAL: NULL
XA_STATE: NULL
SOURCE:
TIMER_START: 1052739637991597000
TIMER_END: 1052938188250308000
TIMER_WAIT: 198550258711000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: READ COMMITTED
AUTOCOMMIT: NO
NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
NUMBER_OF_RELEASE_SAVEPOINT: 0
OBJECT_INSTANCE_BEGIN: NULL
NESTING_EVENT_ID: 3871
NESTING_EVENT_TYPE: STATEMENT
3.3 补充
如果一个事务长时间未提交（长时间处于ACTIVE状态），这种情况虽然可以从events_transactions_current表中可以查询到未提交的事务信息，但是并不能很直观的看到事务是从什么时间点开始的，我们可以借助information_schema.innodb_trx表进行辅助判断Y
