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= )
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，避免长事物。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。