最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
MySQL长事务是什么 MySQL长事务实例解析
时间:2022-06-29 09:04:45 编辑:袖梨 来源:一聚教程网
本篇文章小编给大家分享一下MySQL长事务实例解析,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
注意:本篇文章并不聚焦于谈论事务隔离级别以及相关特性。而是介绍长事务相关危害以及监控处理方法。本文是基于MySQL5.7.23版本,不可重复读(RR)隔离级别所做实验。
1.什么是长事务
首先我们先要知道什么是长事务,顾名思义就是运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。
下面将演示下如何开启事务及模拟长事务:
#假设我们有一张stu_tb表,结构及数据如下 mysql> show create table stu_tbG *************************** 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 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 | +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
从上述结果中我们可以看到该事务从一开始到现在执行过的所有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_waitsG *************************** 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_waitsG # 查询事务锁详细信息 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
相关文章
- 王者荣耀侦探能力大测试攻略 王者荣耀侦探能力大测试怎么过 11-22
- 无期迷途主线前瞻兑换码是什么 11-22
- 原神欧洛伦怎么培养 11-22
- 炉石传说网易云音乐联动怎么玩 11-22
- 永劫无间手游确幸转盘怎么样 11-22
- 无期迷途主线前瞻兑换码是什么 无期迷途主线前瞻直播兑换码介绍 11-22