什么是长事务
运行时间比较长，长时间未提交的事务，也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时，容易造成主从延迟，要尽量避免使用长事务。
下面我将演示下如何开启事务及模拟长事务：
#假设我们有一张stu_tb表，结构及数据如下
stu_id 唯一索引
mysql> select * from stu_tb;
+--------------+--------+----------+---------------------+---------------------+
| id | stu_id | stu_name | create_time | update_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | from1 | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 2 | 1002 | dfsfd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 3 | 1003 | fdgfg | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 4 | 1004 | sdfsdf | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
+--------------+--------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)
#显式开启事务，可用begin或start transactionmysql
> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu_tb where stu_id = 1002 for update;
#如果我们不及时提交上个事务，那么这个事务就变成了长事务，当其他会话要操作这条数据时，就会一直等待。
如何找到长事务
遇到事务等待问题时，我们首先要做的是找到正在执行的事务。 information_schema.INNODB_TRX 表中包含了当前innodb内部正在运行的事务信息，这个表中给出了事务的开始时间，我们可以稍加运算即可得到事务的运行时间。
SELECT
t.*, to_seconds(now()) - to_seconds(t.trx_started) idle_time
FROM
information_schema.innodb_trx t;
*************************** 1. row ***************************
trx_id: 6168
trx_state: RUNNING
trx_started: 2019-09-16 11:08:27
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 11
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0trx_autocommit_non_locking: 0
idle_time: 170
在结果中idle_time是计算产生的，也是事务的持续时间。但事务的trx_query是NUL，这并不是说事务什么也没执行，一个事务可能包含多个SQL，如果SQL执行完毕就不再显示了。当前事务正在执行，innodb也不知道这个事务后续还有没有sql,啥时候会commit。 因此trx_query不能提供有意义的信息。
如果我们想看到这个事务执行过的SQL，看是否可以杀掉长事务，怎么办呢？我们可以联合其他系统表查询得到，具体查询SQL如下：
SELECT
now(),
(
UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)
) diff_sec,
b.id,
b. USER,
b. HOST,
b.db,
d.SQL_TEXT
FROM
.innodb_trx a
INNER JOIN PERFORMANCE_SCHEMA .`PROCESSLIST` b ON a.TRX_MYSQL_THREAD_ID = b.id
AND b.command = 'Sleep'
INNER JOIN PERFORMANCE_SCHEMA .threads c ON b.id = c.PROCESSLIST_ID
INNER JOIN PERFORMANCE_SCHEMA .events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
| now() | diff_sec | id | user | host | db | SQL_TEXT |
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
| 2019-09-16 14:06:26 | 54 | 17 | root | localhost | testdb | select * from stu_tb where stu_id = 1002 for update |
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
上述结果中diff_sec和上面idle_time表示意思相同，都是代表此事务持续的秒数。SQL_TEXT表示该事务刚执行的SQL。但是呢，上述语句只能查到事务最后执行的SQL，我们知道，一个事务里可能包含多个SQL，那我们想查询这个未提交的事务执行过哪些SQL，是否可以满足呢，答案是结合events_statements_history系统表也可以满足需求。下面语句将会查询出该事务执行过的所有SQL：
SELECT
ps.id 'PROCESS ID',
ps. USER,
ps. HOST,
esh.EVENT_ID,
trx.trx_started,
esh.event_name 'EVENT NAME',
esh.sql_text 'SQL',
ps.time
FROM
PERFORMANCE_SCHEMA .events_statements_history esh
JOIN PERFORMANCE_SCHEMA .threads th ON esh.thread_id = th.thread_id
JOIN information_schema. PROCESSLIST ps ON ps.id = th.processlist_id
LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
WHERE
trx.trx_id IS NOT NULL
AND ps. USER != 'SYSTEM_USER'
ORDER BY
esh.EVENT_ID;
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| PROCESS ID | USER | HOST | EVENT_ID | trx_started | EVENT NAME | SQL | time |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| 20 | root | localhost | 1 | 2019-09-16 14:18:44 | statement/sql/select | select @@version_comment limit 1 | 60 |
| 20 | root | localhost | 2 | 2019-09-16 14:18:44 | statement/sql/begin | start transaction | 60 |
| 20 | root | localhost | 3 | 2019-09-16 14:18:44 | statement/sql/select | SELECT DATABASE() | 60 |
| 20 | root | localhost | 4 | 2019-09-16 14:18:44 | statement/com/Init DB | NULL | 60 |
| 20 | root | localhost | 5 | 2019-09-16 14:18:44 | statement/sql/show_databases | show databases | 60 |
| 20 | root | localhost | 6 | 2019-09-16 14:18:44 | statement/sql/show_tables | show tables | 60 |
| 20 | root | localhost | 7 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | 60 |
| 20 | root | localhost | 8 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | 60 |
| 20 | root | localhost | 9 | 2019-09-16 14:18:44 | statement/sql/select | select * from stu_tb | 60 |
| 20 | root | localhost | 10 | 2019-09-16 14:18:44 | statement/sql/select | select * from stu_tb where stu_id = 1002 for update | 60 |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
从上述结果中我们可以看到该事务从一开始到现在执行过的所有SQL，当我们把该事务相关信息都查询清楚后，我们就可以判定该事务是否可以杀掉，以免影响其他事务造成等待现象。
在这里稍微拓展下，长事务极易造成阻塞或者死锁现象，通常情况下我们可以首先查询 sys.innodb_lock_waits 视图确定有没有事务阻塞现象：
#假设一个事务执行
select * from stu_tb where stu_id = 1002 for update
#另外一个事务执行
update stu_tb set stu_name = 'wang' where stu_id = 1002
mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2019-09-16 14:34:32
wait_age: 00:00:03
wait_age_secs: 3
locked_table: `testdb`.`stu_tb`
locked_index: uk_stu_id
locked_type: RECORD
waiting_trx_id: 6178
waiting_trx_started: 2019-09-16 14:34:32
waiting_trx_age: 00:00:03
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 19
waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1002
waiting_lock_id: 6178:47:4:7
waiting_lock_mode: X
blocking_trx_id: 6177
blocking_pid: 20
blocking_query: NULL
blocking_lock_id: 6177:47:4:7
blocking_lock_mode: X
blocking_trx_started: 2019-09-16 14:18:44
blocking_trx_age: 00:15:51
blocking_trx_rows_locked: 2
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 20sql_kill_blocking_connection: KILL 20
上述结果显示出被阻塞的SQL以及锁的类型，更强大的是杀掉会话的语句也给出来了。但是并没有找到阻塞会话执行的SQL，如果我们想找出更详细的信息，可以使用下面语句：
SELECT
tmp.*, c.SQL_Text blocking_sql_text,
p. HOST blocking_host
FROM
(
SELECT
r.trx_state wating_trx_state,
r.trx_id waiting_trx_id,
r.trx_mysql_thread_Id waiting_thread,
r.trx_query waiting_query,
b.trx_state blocking_trx_state,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
) tmp,
information_schema. PROCESSLIST p,
PERFORMANCE_SCHEMA .events_statements_current c,
PERFORMANCE_SCHEMA .threads t
WHERE
tmp.blocking_thread = p.id
AND t.thread_id = c.THREAD_ID
AND t.PROCESSLIST_ID = p.id
*************************** 1. row ***************************
wating_trx_state: LOCK WAIT
waiting_trx_id: 6180
waiting_thread: 19
waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006blocking_trx_state: RUNNING
blocking_trx_id: 6177
blocking_thread: 20
blocking_query: NULL
blocking_sql_text: select * from stu_tb where stu_id = 1006 for update
blocking_host: localhost
上面结果显得更加清晰，我们可以清楚的看到阻塞端及被阻塞端事务执行的语句，有助于我们排查并确认是否可以杀掉阻塞的会话。
监控长事务
现实工作中我们需要监控下长事务，定义一个阈值，比如说30s 执行时间超过30s的事务即为长事务，要求记录并告警出来，提醒管理人员去处理。下面给出监控脚本，各位可以参考下，根据需求改动使用：
#!/bin/bash
# Describe: monitor long transaction
/usr/local/mysql/bin/mysql -N -uroot -pxxxxxx -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT
from information_schema.innodb_trx a inner join information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | while read A B C D E F G H
do
if [ "$C" -gt 30 ]
then
echo $(date +"%Y-%m-%d %H:%M:%S")
echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H"
fi
done >> /tmp/longtransaction.txt
简单说明一下，这里的-gt 30是30秒钟的意思，只要超过了30秒钟就认定是长事务，可以根据实际需要自定义。将该脚本加入定时任务中即可执行。
总结
1、查询所有正在运行的事务及运行时间
SELECT
t.*, to_seconds(now()) - to_seconds(t.trx_started) idle_time
FROM
information_schema.innodb_trx t;
2、查询事务详细信息及执行的SQL
SELECT
now(),
(
UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)
) diff_sec,
b.id,
b. USER,
b. HOST,
b.db,
d.SQL_TEXT
FROM
.innodb_trx a
INNER JOIN PERFORMANCE_SCHEMA .`PROCESSLIST` b ON a.TRX_MYSQL_THREAD_ID = b.id
AND b.command = 'Sleep'
INNER JOIN PERFORMANCE_SCHEMA .threads c ON b.id = c.PROCESSLIST_ID
INNER JOIN PERFORMANCE_SCHEMA .events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
3、查询事务执行过的所有历史SQL记录
SELECT
ps.id 'PROCESS ID',
ps. USER,
ps. HOST,
esh.EVENT_ID,
trx.trx_started,
esh.event_name 'EVENT NAME',
esh.sql_text 'SQL',
ps.time
FROM
PERFORMANCE_SCHEMA .events_statements_history esh
JOIN PERFORMANCE_SCHEMA .threads th ON esh.thread_id = th.thread_id
JOIN information_schema. PROCESSLIST ps ON ps.id = th.processlist_id
LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
WHERE
trx.trx_id IS NOT NULL
AND ps. USER != 'SYSTEM_USER'
ORDER BY
esh.EVENT_ID;
4、简单查询事务锁
select * from sys.innodb_lock_waits
5、查询事务锁详细信息
SELECT
tmp.*, c.SQL_Text blocking_sql_text,
p. HOST blocking_host
FROM
(
SELECT
r.trx_state wating_trx_state,
r.trx_id waiting_trx_id,
r.trx_mysql_thread_Id waiting_thread,
r.trx_query waiting_query,
b.trx_state blocking_trx_state,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
) tmp,
information_schema. PROCESSLIST p,
PERFORMANCE_SCHEMA .events_statements_current c,
PERFORMANCE_SCHEMA .threads t
WHERE
tmp.blocking_thread = p.id
AND t.thread_id = c.THREAD_ID
AND t.PROCESSLIST_ID = p.id
标签：事务,stu,SQL,blocking,trx,详解,mysql,中长,id From： https://blog.51cto.com/u_6353447/5873555