devbox
Cpp五条
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mysql-长事务详解

作者：Cpp五条 | 2024-05-30 13:15:36

赞

踩

长事务

目录

前言：

1.什么是长事务

2.如何找到长事务

3.监控长事务

总结：

前言：

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

注意：本篇文章并不聚焦于谈论事务隔离级别以及相关特性。而是介绍长事务相关危害以及监控处理方法。本文是基于MySQL5.7.23版本，不可重复读(RR)隔离级别所做实验。

1.什么是长事务

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

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

  1. #假设我们有一张stu_tb表，结构及数据如下
  2. mysql> show create table stu_tb\G
  3. *************************** 1. row ***************************
  4.        Table: stu_tb
  5. Create Table: CREATE TABLE `stu_tb` (
  6.   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  7.   `stu_id` int(11) NOT NULL COMMENT '学号',
  8.   `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  9.   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  10.   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  11.   PRIMARY KEY (`increment_id`),
  12.   UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE
  13. ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='测试学生表'
  14. 1 row in set (0.01 sec)
  15.  
  16. mysql> select * from stu_tb;
  17. +--------------+--------+----------+---------------------+---------------------+
  18. | increment_id | stu_id | stu_name | create_time         | update_time         |
  19. +--------------+--------+----------+---------------------+---------------------+
  20. |            1 |   1001 | from1    | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  21. |            2 |   1002 | dfsfd    | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  22. |            3 |   1003 | fdgfg    | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  23. |            4 |   1004 | sdfsdf   | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  24. |            5 |   1005 | dsfsdg   | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  25. |            6 |   1006 | fgd      | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  26. |            7 |   1007 | fgds     | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  27. |            8 |   1008 | dgfsa    | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  28. +--------------+--------+----------+---------------------+---------------------+
  29. 8 rows in set (0.00 sec)
  30.  
  31. #显式开启事务，可用begin或start transaction
  32. mysql> start transaction;
  33. Query OK, 0 rows affected (0.00 sec)
  34.  
  35. mysql> select * from stu_tb where stu_id = 1006 for update;
  36. +--------------+--------+----------+---------------------+---------------------+
  37. | increment_id | stu_id | stu_name | create_time         | update_time         |
  38. +--------------+--------+----------+---------------------+---------------------+
  39. |            6 |   1006 | fgd      | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
  40. +--------------+--------+----------+---------------------+---------------------+
  41. 1 row in set (0.01 sec)
  42.  
  43. #如果我们不及时提交上个事务，那么这个事务就变成了长事务，当其他会话要操作这条数据时，就会一直等待。

2.如何找到长事务

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

  1. mysql> select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G
  2. *************************** 1. row ***************************
  3.                     trx_id: 6168
  4.                  trx_state: RUNNING
  5.                trx_started: 2019-09-16 11:08:27
  6.      trx_requested_lock_id: NULL
  7.           trx_wait_started: NULL
  8.                 trx_weight: 3
  9.        trx_mysql_thread_id: 11
  10.                  trx_query: NULL
  11.        trx_operation_state: NULL
  12.          trx_tables_in_use: 0
  13.          trx_tables_locked: 1
  14.           trx_lock_structs: 3
  15.      trx_lock_memory_bytes: 1136
  16.            trx_rows_locked: 2
  17.          trx_rows_modified: 0
  18.    trx_concurrency_tickets: 0
  19.        trx_isolation_level: REPEATABLE READ
  20.          trx_unique_checks: 1
  21.     trx_foreign_key_checks: 1
  22. trx_last_foreign_key_error: NULL
  23.  trx_adaptive_hash_latched: 0
  24.  trx_adaptive_hash_timeout: 0
  25.           trx_is_read_only: 0
  26. trx_autocommit_non_locking: 0
  27.                  idle_time: 170

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

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

  1. 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
  2.     -> information_schema.PROCESSLIST b
  3.     -> on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
  4.     -> inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
  5.     -> inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
  6. +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
  7. | now()               | diff_sec | id | user | host      | db     | SQL_TEXT                                            |
  8. +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
  9. | 2019-09-16 14:06:26 |       54 | 17 | root | localhost | testdb | select * from stu_tb where stu_id = 1006 for update |
  10. +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+

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

  1. mysql> SELECT
  2.     ->   ps.id 'PROCESS ID',
  3.     ->   ps.USER,
  4.     ->   ps.HOST,
  5.     ->   esh.EVENT_ID,
  6.     ->   trx.trx_started,
  7.     ->   esh.event_name 'EVENT NAME',
  8.     ->   esh.sql_text 'SQL',
  9.     ->   ps.time
  10.     -> FROM
  11.     ->   PERFORMANCE_SCHEMA.events_statements_history esh
  12.     ->   JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
  13.     ->   JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
  14.     ->   LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
  15.     -> WHERE
  16.     ->   trx.trx_id IS NOT NULL
  17.     ->   AND ps.USER != 'SYSTEM_USER'
  18.     -> ORDER BY
  19.     ->   esh.EVENT_ID;
  20. +------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
  21. | PROCESS ID | USER | HOST      | EVENT_ID | trx_started         | EVENT NAME                   | SQL                                                 | time |
  22. +------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
  23. |         20 | root | localhost |        1 | 2019-09-16 14:18:44 | statement/sql/select         | select @@version_comment limit 1                    |   60 |
  24. |         20 | root | localhost |        2 | 2019-09-16 14:18:44 | statement/sql/begin          | start transaction                                   |   60 |
  25. |         20 | root | localhost |        3 | 2019-09-16 14:18:44 | statement/sql/select         | SELECT DATABASE()                                   |   60 |
  26. |         20 | root | localhost |        4 | 2019-09-16 14:18:44 | statement/com/Init DB        | NULL                                                |   60 |
  27. |         20 | root | localhost |        5 | 2019-09-16 14:18:44 | statement/sql/show_databases | show databases                                      |   60 |
  28. |         20 | root | localhost |        6 | 2019-09-16 14:18:44 | statement/sql/show_tables    | show tables                                         |   60 |
  29. |         20 | root | localhost |        7 | 2019-09-16 14:18:44 | statement/com/Field List     | NULL                                                |   60 |
  30. |         20 | root | localhost |        8 | 2019-09-16 14:18:44 | statement/com/Field List     | NULL                                                |   60 |
  31. |         20 | root | localhost |        9 | 2019-09-16 14:18:44 | statement/sql/select         | select * from stu_tb                                |   60 |
  32. |         20 | root | localhost |       10 | 2019-09-16 14:18:44 | statement/sql/select         | select * from stu_tb where stu_id = 1006 for update |   60 |
  33. +------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+

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

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

  1. #假设一个事务执行 select * from stu_tb where stu_id = 1006 for update
  2. #另外一个事务执行 update stu_tb set stu_name = 'wang' where stu_id = 1006
  3.  
  4. mysql> select * from sys.innodb_lock_waits\G
  5. *************************** 1. row ***************************
  6.                 wait_started: 2019-09-16 14:34:32
  7.                     wait_age: 00:00:03
  8.                wait_age_secs: 3
  9.                 locked_table: `testdb`.`stu_tb`
  10.                 locked_index: uk_stu_id
  11.                  locked_type: RECORD
  12.               waiting_trx_id: 6178
  13.          waiting_trx_started: 2019-09-16 14:34:32
  14.              waiting_trx_age: 00:00:03
  15.      waiting_trx_rows_locked: 1
  16.    waiting_trx_rows_modified: 0
  17.                  waiting_pid: 19
  18.                waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006
  19.              waiting_lock_id: 6178:47:4:7
  20.            waiting_lock_mode: X
  21.              blocking_trx_id: 6177
  22.                 blocking_pid: 20
  23.               blocking_query: NULL
  24.             blocking_lock_id: 6177:47:4:7
  25.           blocking_lock_mode: X
  26.         blocking_trx_started: 2019-09-16 14:18:44
  27.             blocking_trx_age: 00:15:51
  28.     blocking_trx_rows_locked: 2
  29.   blocking_trx_rows_modified: 0
  30.      sql_kill_blocking_query: KILL QUERY 20
  31. sql_kill_blocking_connection: KILL 20

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

  1. mysql> SELECT
  2.     ->   tmp.*,
  3.     ->   c.SQL_Text blocking_sql_text,
  4.     ->   p.HOST blocking_host
  5.     -> FROM
  6.     ->   (
  7.     ->   SELECT
  8.     ->     r.trx_state wating_trx_state,
  9.     ->     r.trx_id waiting_trx_id,
  10.     ->     r.trx_mysql_thread_Id waiting_thread,
  11.     ->     r.trx_query waiting_query,
  12.     ->     b.trx_state blocking_trx_state,
  13.     ->     b.trx_id blocking_trx_id,
  14.     ->     b.trx_mysql_thread_id blocking_thread,
  15.     ->     b.trx_query blocking_query
  16.     ->   FROM
  17.     ->     information_schema.innodb_lock_waits w
  18.     ->     INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  19.     ->     INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
  20.     ->   ) tmp,
  21.     ->   information_schema.PROCESSLIST p,
  22.     ->   PERFORMANCE_SCHEMA.events_statements_current c,
  23.     ->   PERFORMANCE_SCHEMA.threads t
  24.     -> WHERE
  25.     ->   tmp.blocking_thread = p.id
  26.     ->   AND t.thread_id = c.THREAD_ID
  27.     ->   AND t.PROCESSLIST_ID = p.id \G
  28. *************************** 1. row ***************************
  29.   wating_trx_state: LOCK WAIT
  30.     waiting_trx_id: 6180
  31.     waiting_thread: 19
  32.      waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006
  33. blocking_trx_state: RUNNING
  34.    blocking_trx_id: 6177
  35.    blocking_thread: 20
  36.     blocking_query: NULL
  37.  blocking_sql_text: select * from stu_tb where stu_id = 1006 for update
  38.      blocking_host: localhost

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

3.监控长事务

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

  1. #!/bin/bash
  2. # -------------------------------------------------------------------------------
  3. # FileName:    long_trx.sh
  4. # Describe:    monitor long transaction
  5. # Revision:    1.0
  6. # Date:        2019/09/16
  7. # Author:      wang
  8.  
  9. /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
  10. information_schema.PROCESSLIST b
  11. on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
  12. inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
  13. 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
  14. do
  15.   if [ "$C" -gt 30 ]
  16.       then
  17.       echo $(date +"%Y-%m-%d %H:%M:%S")
  18.       echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H"
  19.   fi
  20. done >> /tmp/longtransaction.txt

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

总结：

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

  1. # 查询所有正在运行的事务及运行时间
  2. select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G
  3.  
  4. # 查询事务详细信息及执行的SQL
  5. 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
  6. on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
  7. inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
  8. inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
  9.  
  10. # 查询事务执行过的所有历史SQL记录
  11. SELECT
  12.   ps.id 'PROCESS ID',
  13.   ps.USER,
  14.   ps.HOST,
  15.   esh.EVENT_ID,
  16.   trx.trx_started,
  17.   esh.event_name 'EVENT NAME',
  18.   esh.sql_text 'SQL',
  19.   ps.time 
  20. FROM
  21.   PERFORMANCE_SCHEMA.events_statements_history esh
  22.   JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
  23.   JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
  24.   LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id 
  25. WHERE
  26.   trx.trx_id IS NOT NULL 
  27.   AND ps.USER != 'SYSTEM_USER' 
  28. ORDER BY
  29.   esh.EVENT_ID;
  30.   
  31.  # 简单查询事务锁
  32.  select * from sys.innodb_lock_waits\G
  33.  
  34.  # 查询事务锁详细信息
  35.  SELECT
  36.   tmp.*,
  37.   c.SQL_Text blocking_sql_text,
  38.   p.HOST blocking_host 
  39. FROM
  40.   (
  41.   SELECT
  42.     r.trx_state wating_trx_state,
  43.     r.trx_id waiting_trx_id,
  44.     r.trx_mysql_thread_Id waiting_thread,
  45.     r.trx_query waiting_query,
  46.     b.trx_state blocking_trx_state,
  47.     b.trx_id blocking_trx_id,
  48.     b.trx_mysql_thread_id blocking_thread,
  49.     b.trx_query blocking_query 
  50.   FROM
  51.     information_schema.innodb_lock_waits w
  52.     INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  53.     INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
  54.   ) tmp,
  55.   information_schema.PROCESSLIST p,
  56.   PERFORMANCE_SCHEMA.events_statements_current c,
  57.   PERFORMANCE_SCHEMA.threads t 
  58. WHERE
  59.   tmp.blocking_thread = p.id 
  60.   AND t.thread_id = c.THREAD_ID 
  61.   AND t.PROCESSLIST_ID = p.id \G

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/647108
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号