未提交事务
长期未提交事务，指开启事务后，长时间未向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>
ORDER BY T.trx_started ASC \G
导致事务长期未提交的因素很多，常见的有：
、事务过程中执行其他非数据库操作，导致事务长期未被处理。
、事务处理异常或实现逻辑有误，导致事务未被正常处理。
、网段异常导致应用端请求未被正常发送给数据库，数据库等待应用后续操作。
、应用服务器性能问题(如CPU爆满)，导致应用无法及时切换到该进程进行处理。
对于代码实现逻辑有误的问题，如果DBA能提供该事务执行的SQL语句将有助于研发快递定位，哪如何定位呢？
方式1：使用通用日志
## 查看general log配置
show variables like '%general_log%' ## 开启general log
SET GLOBAL general_log = ;
在通用日志中能看到
--29T13::07.932646+: Query SELECT * FROM `sys`.`processlist`
--29T13::36.049694+: Query SELECT * FROM `sys`.`processlist` WHERE CONN_ID=
--29T13::44.378687+: Query SELECT * FROM `sys`.`processlist` WHERE CONN_ID=
--29T13::51.418669+: Query SELECT * FROM `sys`.`processlist` WHERE CONN_ID=
上面的20036是会话连接号(connection id), 使用SHOW PROCESSLIST 或`information_schema`.`PROCESSLIST`查到的ID即该ID。
对于MySQL 5.7版本，可以使用sys.session视图来查看会话最后一次执行的SQL：
SELECT *
FROM sys.session
WHERE CONN_ID = \G
输出结果为：
thd_id:
conn_id:
user: admin@172.28.37.35
db: demodb
command: Sleep
state: NULL
time:
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 157.00 us
rows_examined:
rows_sent:
rows_affected:
tmp_tables:
tmp_disk_tables:
full_scan: YES
last_statement: select * from tb001 limit
last_statement_latency: 607.34 us
current_memory: bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid:
program_name: mysql
使用系统视图sys.processlist 也能看到类似上面的结果：
SELECT *
FROM sys.processlist
WHERE CONN_ID =
系统视图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(REPLACE(REPLACE(REPLACE(T1.`SQL_TEXT`,'\n',' '),'\r',' '),'\t',' ') 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>
ORDER BY T.trx_started ASC \G
输出结果中trx_sql_text即该事务执行过的SQL语句。
查看每个未提交事务执行过多的所有SQL
SELECT @dt_ts:=UNIX_TIMESTAMP(NOW());
SELECT
@dt_timer:=MAX(SH.TIMER_START)
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID=CONNECTION_ID(); SELECT
SH.CURRENT_SCHEMA AS database_name,
REPLACE(REPLACE(REPLACE(SH.`SQL_TEXT`,'\n',' '),'\r',' '),'\t',' ') AS executed_sql,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000 AS SIGNED)) AS start_time,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer+SH.TIMER_END)/1000000000000 AS SIGNED)) AS end_time,
(SH.TIMER_END-SH.TIMER_START)/1000000000000 AS used_seconds,
SH.TIMER_WAIT/1000000000000 AS wait_seconds,
SH.LOCK_TIME/1000000000000 AS lock_seconds,
SH.ROWS_AFFECTED AS affected_rows,
SH.ROWS_SENT AS send_rows
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID IN (
SELECT
p.ID AS conn_id
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>3
)
AND SH.TIMER_START<@dt_timer
ORDER BY SH.TIMER_START ASC;
PS：如果使用MySQL 5.7早期版本热备升级到MySQL 5.7后期版本，可能会导致sys库上很多视图和函数无法正常使用，需要使用mysql_upgrade进行升级。
/export/servers/mysql/bin/mysql_upgrade \
--host='127.0.0.1' \
--port=3305 \
--user='admin' \
--password="admin_psw"