MySQL-长事务详解

2021年11月21日 阅读数:5
这篇文章主要向大家介绍MySQL-长事务详解,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

前言: 
mysql


『入门MySQL』系列文章已经完结,从此个人文章仍是会以MySQL为主,主要记录下近期工做及学习遇到的场景或者本身的感悟想法,可能后续的文章不是那么连贯,但仍是但愿你们多多支持。言归正传,本篇文章主要介绍MySQL长事务相关内容,好比说咱们开启的一个事务,一直没提交或回滚会怎样呢,出现事务等待状况应该如何处理,本篇文章将给你答案。sql


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


1.什么是长事务


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

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



#假设咱们有一张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)
#若是咱们不及时提交上个事务,那么这个事务就变成了长事务,当其余会话要操做这条数据时,就会一直等待。

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: 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以下:spa



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,当咱们把该事务相关信息都查询清楚后,咱们就能够断定该事务是否能够杀掉,以避免影响其余事务形成等待现象。
code


在这里稍微拓展下,长事务极易形成阻塞或者死锁现象,一般状况下咱们能够首先查询 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 20sql_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 = 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


简单说明一下,这里的-gt 30是30秒钟的意思,只要超过了30秒钟就认定是长事务,能够根据实际须要自定义。将该脚本加入定时任务中便可执行。


总结: 


本文主要介绍了长事务相关内容,怎样找到长事务,怎么处理长事务,如何监控长事务。可能有些小伙伴对事务理解还很少,但愿这篇文章对你有所帮助。因为本篇文章列出的查询事务相关语句较多,现总结以下:



# 查询全部正在运行的事务及运行时间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