适用范围
MySQL8.0版本
问题概述
本篇文章中，我们将展示如何在MySQL 8.0中查询长事务。因为长大事物或者执行时间很长的SQL事物，或者执行完SQL不提交的事务可能会导致性能问题，并可能导致数据库无响应。为了避免这些问题，监控和管理数据库中的长事务非常重要。
查找过程
执行下面SQL轻松找到那些对DBA来说可能是噩梦的长事务：
SELECT thr.processlist_id AS mysql_thread_id,
concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
command,
FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
current_statement as `latest_statement`
FROM performance_schema.events_transactions_current trx
INNER JOIN performance_schema.threads thr USING (thread_id)
LEFT JOIN sys.processlist p ON p.thd_id=thread_id
WHERE thr.processlist_id IS NOT NULL
AND PROCESSLIST_USER IS NOT NULL
AND trx.state = 'ACTIVE'
GROUP BY thread_id, timer_wait
ORDER BY TIMER_WAIT DESC LIMIT 10;
实际查询结果：
上面截图可以看到，目前有一个会话（活动会话ACTIVE）的事物，开始了将近30分钟了，什么也没做NULL.
通常情况下Sleep状态的这些活动事物会话是引起问题最多的会话，因为他们可能是被遗忘的交互式会话 interacive session，默认情况下他们会保持很长时间（8小时，interactive_timeout）。
相关追踪
如果开启了setup_consumers表中的记录，还可以列出在此事物中执行过的SQL语句（默认情况下限制为10个，performance_schema_events_statements_history_size）
UPDATE performance_schema.setup_consumers SET enabled = 'yes'
WHERE name LIKE 'events_statements_history_long'
OR name LIKE 'events_transactions_history_long';
开启后，我们可以使用以下语句查看所有新事务的历史记录：
SELECT DATE_SUB(now(), INTERVAL (
SELECT variable_value
FROM performance_schema.global_status
WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) `start_time`,
SQL_TEXT
FROM performance_schema.events_statements_history
WHERE nesting_event_id=(
SELECT EVENT_ID
FROM performance_schema.events_transactions_current t
LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id
WHERE conn_id= <mysql_thread_id> )
ORDER BY event_id;
模拟场景
窗口A：显示开启一个事物，执行几条SQL语句
MySQL localhost sysdb SQL > begin; //开启一个事物，然后执行下面几条SQL语句，不提交！
Query OK, 0 rows affected (0.0001 sec)
MySQL localhost sysdb ★ SQL >
MySQL localhost sysdb ★ SQL > select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (10.2303 sec)
MySQL localhost sysdb ★ SQL > show tables;
+-----------------+
| Tables_in_sysdb |
+-----------------+
| sbtest1 |
| sbtest10 |
| sbtest11 |
| sbtest12 |
| sbtest13 |
| sbtest14 |
| sbtest15 |
| sbtest16 |
| sbtest17 |
| sbtest18 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+-----------------+
18 rows in set (0.0016 sec)
MySQL localhost sysdb ★ SQL > select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
1 row in set (2.0001 sec)
MySQL localhost sysdb ★ SQL > select * from sbtest8 limit 10;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 869129 | 88685603666-13082731833-44828444812-82705875919-26341360863-04733958019-41368186712-24795947673-34798017639-58666435879 | 04351122388-15994679389-62105097614-36840217009-68710798385 |
| 2 | 999082 | 61202645658-47770503385-16546509174-55261723675-27022251151-33426512757-12199751348-34277946822-74620002610-10716463870 | 62709683723-72149291042-31713695328-91656075578-12623937392 |
| 3 | 888692 | 19921577838-98574458066-54323495798-83771239170-49216762870-52211303081-89456091162-45079030597-61371324782-54788672073 | 22966748308-19475300288-41584146859-68153041739-78482443508 |
| 4 | 1009951 | 56943369991-40665904202-76856327923-72678375842-44977898889-88965801379-80760437704-04768797185-37074589522-42996124820 | 05207125472-72977594313-34290545649-83224842815-05324455631 |
| 5 | 998811 | 56037133707-69002289600-02736715648-08667993966-68076262603-21383088770-32995576929-88835094977-40353874794-28278877933 | 48665120690-84711012402-48220658276-27360805992-27247224117 |
| 6 | 821030 | 86341799252-60268157155-21352938496-40609098126-76987284773-39925057087-63100886706-41801340577-19805905002-27655014068 | 29040935624-61569457789-81446450420-73629478247-38720076794 |
| 7 | 1003127 | 22745933148-94359884592-48502621273-24705635945-92151081750-91496266451-75130647212-36524221705-97355215983-51582923024 | 57875846355-75242050445-08973920069-31829371280-63169018472 |
| 8 | 954978 | 03343730102-94181567853-53163215623-89757342726-04656401022-21750643776-86639420813-56015238845-36681554122-34505128545 | 49949773343-64645054754-42833232876-17950151920-91524803110 |
| 9 | 1000519 | 64150381666-64883095293-16245995565-44880735074-89499063785-44425484718-32877284059-53455296950-22004123081-10928333035 | 06844213595-81963114917-27437857464-06781692283-17955818107 |
| 10 | 1009540 | 58864564061-02311812627-19755386600-01919012224-49284724700-79632899515-48756358204-67813773052-47684075623-59997325957 | 08623802706-47474903574-89108655376-39420654298-57306328315 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
10 rows in set (0.0022 sec)
MySQL localhost sysdb ★ SQL > select count(*) from sbtest6;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
1 row in set (1.9631 sec)
MySQL localhost sysdb ★ SQL > //执行完之后不提交！
窗口B：查询长事物，并通过长事物的mysql_thread_id列找到之前这个会话执行过的SQL语句
root@127.0.0.1:(none) 04:10:09 >
root@127.0.0.1:(none) 04:10:09 >SELECT thr.processlist_id AS mysql_thread_id,
-> concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
-> Command,
-> FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
-> current_statement as `latest_statement`
-> FROM performance_schema.events_transactions_current trx
-> INNER JOIN performance_schema.threads thr USING (thread_id)
-> LEFT JOIN sys.processlist p ON p.thd_id=thread_id
-> WHERE thr.processlist_id IS NOT NULL
-> AND PROCESSLIST_USER IS NOT NULL
-> AND trx.state = 'ACTIVE'
-> GROUP BY thread_id, timer_wait
-> ORDER BY TIMER_WAIT DESC LIMIT 10;
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
| mysql_thread_id | User | Command | trx_duration | latest_statement |
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
| 24 | root@localhost | Sleep | 9.85 min | NULL |
| 25 | root@localhost | Query | 27.39 ms | SELECT thr.processlist_id AS m ... ER BY TIMER_WAIT DESC LIMIT 10 |
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)
root@127.0.0.1:(none) 04:12:27 >
root@127.0.0.1:(none) 04:13:00 >
root@127.0.0.1:(none) 04:13:01 >SELECT DATE_SUB(now(), INTERVAL (
-> SELECT variable_value
-> FROM performance_schema.global_status
-> WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) `start_time`,
-> SQL_TEXT
-> FROM performance_schema.events_statements_history
-> WHERE nesting_event_id=(
-> SELECT EVENT_ID
-> FROM performance_schema.events_transactions_current t
-> LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id
-> WHERE conn_id=24)
-> ORDER BY event_id;
+----------------------------+--------------------------------+
| start_time | SQL_TEXT |
+----------------------------+--------------------------------+
| 2023-03-14 16:10:36.362086 | select @@sql_mode |
| 2023-03-14 16:10:44.954953 | select count(*) from sbtest1 |
| 2023-03-14 16:10:59.158119 | show tables |
| 2023-03-14 16:11:09.731169 | select count(*) from sbtest9 |
| 2023-03-14 16:11:39.732789 | select * from sbtest8 limit 10 |
| 2023-03-14 16:12:08.838031 | select count(*) from sbtest6 |
+----------------------------+--------------------------------+
6 rows in set (0.02 sec)
至此，我们可以查到该长事物之前执行过的一个SQL语句的列表，通过结果来判断我们该事物是否可以跟业务人员沟通进行kill（mysql> kill 24;）或者等待提交，通常此类长事物尤其是没有提交的事物是会影响MySQL数据库性能的，产生锁等待，业务人员看不到内部到底发生了什么，然而整个事物的停滞（被执行人忽略提交，忘记提交等原因，客户端设置auto_commit=0后执行SQL不commit/rollback）却影响了业务的响应时间。
享受MySQL，避免长事物。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
