Mysql历史事务_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