深入了解mysql长事务

前言：

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

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

1.什么是长事务

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

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

#假设我们有一张stu_tb表，结构及数据如下mysql> show create table stu_tb\G*************************** 1. row ***************************    Table: stu_tbCreate 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 transactionmysql> 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) #如果我们不及时提交上个事务，那么这个事务就变成了长事务，当其他会话要操作这条数据时，就会一直等待。

遇到事务等待问题时，我们首先要做的是找到正在执行的事务。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: 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

如果我们想看到这个事务执行过的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 |+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+

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 |+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+

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

#假设一个事务执行 select * from stu_tb where stu_id = 1006 for update#另外一个事务执行 update stu_tb set stu_name = 'wang' where stu_id = 1006mysql> 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 20sql_kill_blocking_connection: KILL 20

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 = 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

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 joininformation_schema.PROCESSLIST bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | while read A B C D E F G Hdo 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" fidone >> /tmp/longtransaction.txt

总结：

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

# 查询所有正在运行的事务及运行时间select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G# 查询事务详细信息及执行的SQLselect 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 bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner 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_hostFROM ( 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 tWHERE tmp.blocking_thread = p.id  AND t.thread_id = c.THREAD_ID  AND t.PROCESSLIST_ID = p.id \G

更多相关文章

随机推荐