深入了解mysql长事务

前言：

本篇文章主要介绍mysql长事务相关内容，比如说我们开启的一个事务，一直没提交或回滚会怎样呢，出现事务等待情况应该如何处理，本篇文章将给你答案。

注意：本篇文章并不聚焦于谈论事务隔离级别以及相关特性。而是介绍长事务相关危害以及监控处理方法。本文是基于mysql5.7.23版本，不可重复读(rr)隔离级别所做实验。(语句为\g可以使查询结构显示更易读，但只可以在mysql命令行使用。)

1.什么是长事务

首先我们先要知道什么是长事务，顾名思义就是运行时间比较长，长时间未提交的事务，也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时，容易造成主从延迟，要尽量避免使用长事务。

下面我将演示下如何开启事务及模拟长事务：

#假设我们有一张stu_tb表，结构及数据如下
mysql> show create table stu_tb\g
*************************** 1. row ***************************
    table: stu_tb
create table: create table `stu_tb` (
 `increment_id` int(11) not null auto_increment comment '自增主键',
 `stu_id` int(11) not null comment '学号',
 `stu_name` varchar(20) default null comment '学生姓名',
 `create_time` timestamp not null default current_timestamp comment '创建时间',
 `update_time` timestamp not null default current_timestamp on update current_timestamp comment '修改时间',
 primary key (`increment_id`),
 unique key `uk_stu_id` (`stu_id`) using btree
) engine=innodb auto_increment=9 default charset=utf8 comment='测试学生表'
1 row in set (0.01 sec)

mysql> select * from stu_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_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 |
|      5 |  1005 | dsfsdg  | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
|      6 |  1006 | fgd   | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
|      7 |  1007 | fgds   | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
|      8 |  1008 | dgfsa  | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
+--------------+--------+----------+---------------------+---------------------+
8 rows in set (0.00 sec)

#显式开启事务，可用begin或start transaction
mysql> start transaction;
query ok, 0 rows affected (0.00 sec)

mysql> select * from stu_tb where stu_id = 1006 for update;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time     | update_time     |
+--------------+--------+----------+---------------------+---------------------+
|      6 |  1006 | fgd   | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
+--------------+--------+----------+---------------------+---------------------+
1 row in set (0.01 sec)

 #如果我们不及时提交上个事务，那么这个事务就变成了长事务，当其他会话要操作这条数据时，就会一直等待。

2.如何找到长事务

遇到事务等待问题时，我们首先要做的是找到正在执行的事务。information_schema.innodb_trx 表中包含了当前innodb内部正在运行的事务信息，这个表中给出了事务的开始时间，我们可以稍加运算即可得到事务的运行时间。

mysql> select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from information_schema.innodb_trx t \g
*************************** 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: 1
trx_last_foreign_key_error: null
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
     trx_is_read_only: 0
trx_autocommit_non_locking: 0
         idle_time: 170

在结果中idle_time是计算产生的，也是事务的持续时间。但事务的trx_query是nul，这并不是说事务什么也没执行，一个事务可能包含多个sql，如果sql执行完毕就不再显示了。当前事务正在执行，innodb也不知道这个事务后续还有没有sql,啥时候会commit。因此trx_query不能提供有意义的信息。

如果我们想看到这个事务执行过的sql，看是否可以杀掉长事务，怎么办呢？我们可以联合其他系统表查询得到，具体查询sql如下：

mysql> 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;
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
| 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 = 1006 for update |
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+

上述结果中diff_sec和上面idle_time表示意思相同，都是代表此事务持续的秒数。sql_text表示该事务刚执行的sql。但是呢，上述语句只能查到事务最后执行的sql，我们知道，一个事务里可能包含多个sql，那我们想查询这个未提交的事务执行过哪些sql，是否可以满足呢，答案是结合events_statements_history系统表也可以满足需求。下面语句将会查询出该事务执行过的所有sql：

mysql> 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 = 1006 for update |  60 |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+

从上述结果中我们可以看到该事务从一开始到现在执行过的所有sql，当我们把该事务相关信息都查询清楚后，我们就可以判定该事务是否可以杀掉，以免影响其他事务造成等待现象。

在这里稍微拓展下，长事务极易造成阻塞或者死锁现象，通常情况下我们可以首先查询 sys.innodb_lock_waits 视图确定有没有事务阻塞现象：

#假设一个事务执行 select * from stu_tb where stu_id = 1006 for update
#另外一个事务执行 update stu_tb set stu_name = 'wang' where stu_id = 1006

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 = 1006
       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 20
sql_kill_blocking_connection: kill 20

上述结果显示出被阻塞的sql以及锁的类型，更强大的是杀掉会话的语句也给出来了。但是并没有找到阻塞会话执行的sql，如果我们想找出更详细的信息，可以使用下面语句：

mysql> 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 \g
*************************** 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 = 1006
blocking_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

上面结果显得更加清晰，我们可以清楚的看到阻塞端及被阻塞端事务执行的语句，有助于我们排查并确认是否可以杀掉阻塞的会话。

3.监控长事务

现实工作中我们需要监控下长事务，定义一个阈值，比如说30s 执行时间超过30s的事务即为长事务，要求记录并告警出来，提醒管理人员去处理。下面给出监控脚本，各位可以参考下，根据需求改动使用：

#!/bin/bash
# -------------------------------------------------------------------------------
# filename:  long_trx.sh
# describe:  monitor long transaction
# revision:  1.0
# date:    2019/09/16
# author:   wang

/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秒钟就认定是长事务，可以根据实际需要自定义。将该脚本加入定时任务中即可执行。

总结：

本文主要介绍了长事务相关内容，怎样找到长事务，怎么处理长事务，如何监控长事务。可能有些小伙伴对事务理解还不多，希望这篇文章对你有所帮助。由于本篇文章列出的查询事务相关语句较多，现总结如下：

# 查询所有正在运行的事务及运行时间
select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from information_schema.innodb_trx t \g

# 查询事务详细信息及执行的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 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;

# 查询事务执行过的所有历史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;
 
 # 简单查询事务锁
 select * from sys.innodb_lock_waits\g
 
 # 查询事务锁详细信息
 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 \g

以上就是深入了解mysql长事务的详细内容，更多关于mysql长事务的资料请关注其它相关文章！