深入了解mysql长事务

数据库 2024/11/9 佚名

3 2 1

前言：

本篇文章主要介绍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长事务的资料请关注其它相关文章！

mysql,事务,mysql,长事务

标签：

mysql,事务,mysql,长事务

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“深入了解mysql长事务”

深入了解mysql长事务

暂无“深入了解mysql长事务”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

深入了解mysql长事务

mysql,事务,mysql,长事务

mysql免安装版步骤解压后找不到密码处理方法

mysql8.0.21下载安装详细教程

评论“深入了解mysql长事务”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

深入了解mysql长事务

mysql,事务,mysql,长事务

mysql免安装版步骤解压后找不到密码处理方法

mysql8.0.21下载安装详细教程

评论“深入了解mysql长事务”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存