mysql查询未执行的sql_MySQL Transaction--查看未提交事务执行的SQL

未提交事务

长期未提交事务，指开启事务后，长时间未向MySQL发出SQL执行请求或事务处理(COMMIT/ROLLBACK)请求，在系统表`information_schema`.`INNODB_TRX` 中状态为RUNNING，而在`information_schema`.`PROCESSLIST`中状态为SlEEP。

## 查看未提交的事务(3秒内未操作的事务)

SELECT

p.ID AS conn_id,

P.USER AS login_user,

P.HOST AS login_host,

p.DB AS database_name,

P.TIME AS trx_sleep_seconds,

TIME_TO_SEC(TIMEDIFF(NOW(),T.trx_started)) AS trx_open_seconds,

T.trx_started,

T.trx_isolation_level,

T.trx_tables_locked,

T.trx_rows_locked,

t.trx_state,

p.COMMAND AS process_state

FROM `information_schema`.`INNODB_TRX` t

INNER JOIN `information_schema`.`PROCESSLIST` p

ON t.trx_mysql_thread_id=p.id

WHERE t.trx_state=‘RUNNING‘AND p.COMMAND=‘Sleep‘AND P.TIME>3ORDER BY T.trx_started ASC \G

导致事务长期未提交的因素很多，常见的有：

1、事务过程中执行其他非数据库操作，导致事务长期未被处理。2、事务处理异常或实现逻辑有误，导致事务未被正常处理。3、网段异常导致应用端请求未被正常发送给数据库，数据库等待应用后续操作。4、应用服务器性能问题(如CPU爆满)，导致应用无法及时切换到该进程进行处理。

对于代码实现逻辑有误的问题，如果DBA能提供该事务执行的SQL语句将有助于研发快递定位，哪如何定位呢？

方式1：使用通用日志

## 查看general log配置

show variables like‘%general_log%‘## 开启general log

SET GLOBAL general_log= 1;

在通用日志中能看到

2019-04-29T13:52:07.932646+08:00 20036 Query SELECT *FROM `sys`.`processlist`2019-04-29T13:52:36.049694+08:00 20036 Query SELECT * FROM `sys`.`processlist` WHERE CONN_ID=2036

2019-04-29T13:52:44.378687+08:00 20036 Query SELECT * FROM `sys`.`processlist` WHERE CONN_ID=20040

2019-04-29T13:52:51.418669+08:00 20036 Query SELECT * FROM `sys`.`processlist` WHERE CONN_ID=20040

上面的20036是会话连接号(connection id), 使用SHOW PROCESSLIST 或`information_schema`.`PROCESSLIST`查到的ID即该ID。

对于MySQL 5.7版本，可以使用sys.session视图来查看会话最后一次执行的SQL：

SELECT *FROM sys.session

WHERE CONN_ID= 20036 \G

输出结果为：

thd_id: 20065conn_id:20040user: admin@172.28.37.35db: demodb

command: Sleep

state: NULL

time:19current_statement: NULL

statement_latency: NULL

progress: NULL

lock_latency:157.00us

rows_examined:110rows_sent:110rows_affected:0tmp_tables:0tmp_disk_tables:0full_scan: YES

last_statement:select * from tb001 limit 110last_statement_latency:607.34us

current_memory:0bytes

last_wait: NULL

last_wait_latency: NULL

source: NULL

trx_latency: NULL

trx_state: NULL

trx_autocommit: NULL

pid:12758program_name: mysql

使用系统视图sys.processlist 也能看到类似上面的结果：

SELECT *FROM sys.processlist

WHERE CONN_ID= 20040

系统视图sys.processlist中数据主要来源于：

performance_schema.events_statements_current

performance_schema.events_statements_history

且上面两个表中记录的数据需要使用线程号(THREAD ID)，可以使用performance_schema.threads来与连接号(CONNECTION ID/PROCESSLIST ID)进行关联。

因此可以将查询未提交事务的SQL优化为：

## 查看未提交的事务(3秒内未操作的事务)

SELECT

p.ID AS conn_id,

P.USER AS login_user,

P.HOST AS login_host,

p.DB AS database_name,

P.TIME AS trx_sleep_seconds,

TIME_TO_SEC(TIMEDIFF(NOW(),T.trx_started)) AS trx_open_seconds,

T.trx_started,

T.trx_isolation_level,

T.trx_tables_locked,

T.trx_rows_locked,

t.trx_state,

p.COMMAND AS process_state,

(

SELECT GROUP_CONCAT(T1.`SQL_TEXT` SEPARATOR‘;‘)

FROM performance_schema.events_statements_history AS T1

INNER JOIN performance_schema.threads AS T2

ON T1.`THREAD_ID`=T2.`THREAD_ID`

WHERE T2.`PROCESSLIST_ID`=P.id

) AS trx_sql_text

FROM `information_schema`.`INNODB_TRX` t

INNER JOIN `information_schema`.`PROCESSLIST` p

ON t.trx_mysql_thread_id=p.id

WHERE t.trx_state=‘RUNNING‘AND p.COMMAND=‘Sleep‘AND P.TIME>3ORDER BY T.trx_started ASC \G

输出结果中trx_sql_text即该事务执行过的SQL语句。