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