深入了解mysql长事务
前言：
本篇文章主要介绍MySQL长事务相关内容，比如说我们开启的一个事务，一直没提交或回滚会怎样呢，出现事务等待情况应该如何处理，本篇文章将给你答案。
注意：本篇文章并不聚焦于谈论事务隔离级别以及相关特性。而是介绍长事务相关危害以及监控处理方法。本文是基于MySQL5.7.23版本，不可重复读(RR)隔离级别所做实验。(语句为\G可以使查询结构显示更易读，但只可以在mysql命令行使用。)
1.什么是长事务
首先我们先要知道什么是长事务，顾名思义就是运行时间比较长，长时间未提交的事务，也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时，容易造成主从延迟，要尽量避免使用长事务。
下面我将演示下如何开启事务及模拟长事务：
#假设我们有一张stu_tb表，结构及数据如下 mysql>showcreatetablestu_tb\G ***************************1.row*************************** Table:stu_tb CreateTable:CREATETABLE`stu_tb`( `increment_id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'自增主键', `stu_id`int(11)NOTNULLCOMMENT'学号', `stu_name`varchar(20)DEFAULTNULLCOMMENT'学生姓名', `create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', `update_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间', PRIMARYKEY(`increment_id`), UNIQUEKEY`uk_stu_id`(`stu_id`)USINGBTREE )ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8COMMENT='测试学生表' 1rowinset(0.01sec) mysql>select*fromstu_tb; +--------------+--------+----------+---------------------+---------------------+ |increment_id|stu_id|stu_name|create_time|update_time| +--------------+--------+----------+---------------------+---------------------+ |1|1001|from1|2019-09-1514:27:34|2019-09-1514:27:34| |2|1002|dfsfd|2019-09-1514:27:34|2019-09-1514:27:34| |3|1003|fdgfg|2019-09-1514:27:34|2019-09-1514:27:34| |4|1004|sdfsdf|2019-09-1514:27:34|2019-09-1514:27:34| |5|1005|dsfsdg|2019-09-1514:27:34|2019-09-1514:27:34| |6|1006|fgd|2019-09-1514:27:34|2019-09-1514:27:34| |7|1007|fgds|2019-09-1514:27:34|2019-09-1514:27:34| |8|1008|dgfsa|2019-09-1514:27:34|2019-09-1514:27:34| +--------------+--------+----------+---------------------+---------------------+ 8rowsinset(0.00sec) #显式开启事务，可用begin或starttransaction mysql>starttransaction; QueryOK,0rowsaffected(0.00sec) mysql>select*fromstu_tbwherestu_id=1006forupdate; +--------------+--------+----------+---------------------+---------------------+ |increment_id|stu_id|stu_name|create_time|update_time| +--------------+--------+----------+---------------------+---------------------+ |6|1006|fgd|2019-09-1514:27:34|2019-09-1514:27:34| +--------------+--------+----------+---------------------+---------------------+ 1rowinset(0.01sec) #如果我们不及时提交上个事务，那么这个事务就变成了长事务，当其他会话要操作这条数据时，就会一直等待。
2.如何找到长事务
遇到事务等待问题时，我们首先要做的是找到正在执行的事务。information_schema.INNODB_TRX表中包含了当前innodb内部正在运行的事务信息，这个表中给出了事务的开始时间，我们可以稍加运算即可得到事务的运行时间。
mysql>selectt.*,to_seconds(now())-to_seconds(t.trx_started)idle_timefromINFORMATION_SCHEMA.INNODB_TRXt\G ***************************1.row*************************** trx_id:6168 trx_state:RUNNING trx_started:2019-09-1611: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:REPEATABLEREAD 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>selectnow(),(UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(a.trx_started))diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXTfrominformation_schema.innodb_trxainnerjoin ->information_schema.PROCESSLISTb ->ona.TRX_MYSQL_THREAD_ID=b.idandb.command='Sleep' ->innerjoinperformance_schema.threadscONb.id=c.PROCESSLIST_ID ->innerjoinperformance_schema.events_statements_currentdONd.THREAD_ID=c.THREAD_ID; +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+ |now()|diff_sec|id|user|host|db|SQL_TEXT| +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+ |2019-09-1614:06:26|54|17|root|localhost|testdb|select*fromstu_tbwherestu_id=1006forupdate| +---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+
上述结果中diff_sec和上面idle_time表示意思相同，都是代表此事务持续的秒数。SQL_TEXT表示该事务刚执行的SQL。但是呢，上述语句只能查到事务最后执行的SQL，我们知道，一个事务里可能包含多个SQL，那我们想查询这个未提交的事务执行过哪些SQL，是否可以满足呢，答案是结合events_statements_history系统表也可以满足需求。下面语句将会查询出该事务执行过的所有SQL：
mysql>SELECT ->ps.id'PROCESSID', ->ps.USER, ->ps.HOST, ->esh.EVENT_ID, ->trx.trx_started, ->esh.event_name'EVENTNAME', ->esh.sql_text'SQL', ->ps.time ->FROM ->PERFORMANCE_SCHEMA.events_statements_historyesh ->JOINPERFORMANCE_SCHEMA.threadsthONesh.thread_id=th.thread_id ->JOINinformation_schema.PROCESSLISTpsONps.id=th.processlist_id ->LEFTJOINinformation_schema.innodb_trxtrxONtrx.trx_mysql_thread_id=ps.id ->WHERE ->trx.trx_idISNOTNULL ->ANDps.USER!='SYSTEM_USER' ->ORDERBY ->esh.EVENT_ID; +------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+ |PROCESSID|USER|HOST|EVENT_ID|trx_started|EVENTNAME|SQL|time| +------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+ |20|root|localhost|1|2019-09-1614:18:44|statement/sql/select|select@@version_commentlimit1|60| |20|root|localhost|2|2019-09-1614:18:44|statement/sql/begin|starttransaction|60| |20|root|localhost|3|2019-09-1614:18:44|statement/sql/select|SELECTDATABASE()|60| |20|root|localhost|4|2019-09-1614:18:44|statement/com/InitDB|NULL|60| |20|root|localhost|5|2019-09-1614:18:44|statement/sql/show_databases|showdatabases|60| |20|root|localhost|6|2019-09-1614:18:44|statement/sql/show_tables|showtables|60| |20|root|localhost|7|2019-09-1614:18:44|statement/com/FieldList|NULL|60| |20|root|localhost|8|2019-09-1614:18:44|statement/com/FieldList|NULL|60| |20|root|localhost|9|2019-09-1614:18:44|statement/sql/select|select*fromstu_tb|60| |20|root|localhost|10|2019-09-1614:18:44|statement/sql/select|select*fromstu_tbwherestu_id=1006forupdate|60| +------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
从上述结果中我们可以看到该事务从一开始到现在执行过的所有SQL，当我们把该事务相关信息都查询清楚后，我们就可以判定该事务是否可以杀掉，以免影响其他事务造成等待现象。
在这里稍微拓展下，长事务极易造成阻塞或者死锁现象，通常情况下我们可以首先查询sys.innodb_lock_waits视图确定有没有事务阻塞现象：
#假设一个事务执行select*fromstu_tbwherestu_id=1006forupdate #另外一个事务执行updatestu_tbsetstu_name='wang'wherestu_id=1006 mysql>select*fromsys.innodb_lock_waits\G ***************************1.row*************************** wait_started:2019-09-1614: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-1614:34:32 waiting_trx_age:00:00:03 waiting_trx_rows_locked:1 waiting_trx_rows_modified:0 waiting_pid:19 waiting_query:updatestu_tbsetstu_name='wang'wherestu_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-1614:18:44 blocking_trx_age:00:15:51 blocking_trx_rows_locked:2 blocking_trx_rows_modified:0 sql_kill_blocking_query:KILLQUERY20 sql_kill_blocking_connection:KILL20
上述结果显示出被阻塞的SQL以及锁的类型，更强大的是杀掉会话的语句也给出来了。但是并没有找到阻塞会话执行的SQL，如果我们想找出更详细的信息，可以使用下面语句：
mysql>SELECT ->tmp.*, ->c.SQL_Textblocking_sql_text, ->p.HOSTblocking_host ->FROM ->( ->SELECT ->r.trx_statewating_trx_state, ->r.trx_idwaiting_trx_id, ->r.trx_mysql_thread_Idwaiting_thread, ->r.trx_querywaiting_query, ->b.trx_stateblocking_trx_state, ->b.trx_idblocking_trx_id, ->b.trx_mysql_thread_idblocking_thread, ->b.trx_queryblocking_query ->FROM ->information_schema.innodb_lock_waitsw ->INNERJOINinformation_schema.innodb_trxbONb.trx_id=w.blocking_trx_id ->INNERJOINinformation_schema.innodb_trxrONr.trx_id=w.requesting_trx_id ->)tmp, ->information_schema.PROCESSLISTp, ->PERFORMANCE_SCHEMA.events_statements_currentc, ->PERFORMANCE_SCHEMA.threadst ->WHERE ->tmp.blocking_thread=p.id ->ANDt.thread_id=c.THREAD_ID ->ANDt.PROCESSLIST_ID=p.id\G ***************************1.row*************************** wating_trx_state:LOCKWAIT waiting_trx_id:6180 waiting_thread:19 waiting_query:updatestu_tbsetstu_name='wang'wherestu_id=1006 blocking_trx_state:RUNNING blocking_trx_id:6177 blocking_thread:20 blocking_query:NULL blocking_sql_text:select*fromstu_tbwherestu_id=1006forupdate blocking_host:localhost
上面结果显得更加清晰，我们可以清楚的看到阻塞端及被阻塞端事务执行的语句，有助于我们排查并确认是否可以杀掉阻塞的会话。
3.监控长事务
现实工作中我们需要监控下长事务，定义一个阈值，比如说30s执行时间超过30s的事务即为长事务，要求记录并告警出来，提醒管理人员去处理。下面给出监控脚本，各位可以参考下，根据需求改动使用：
#!/bin/bash #------------------------------------------------------------------------------- #FileName:long_trx.sh #Describe:monitorlongtransaction #Revision:1.0 #Date:2019/09/16 #Author:wang /usr/local/mysql/bin/mysql-N-uroot-pxxxxxx-e"selectnow(),(UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(a.trx_started))diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXTfrominformation_schema.innodb_trxainnerjoin information_schema.PROCESSLISTb ona.TRX_MYSQL_THREAD_ID=b.idandb.command='Sleep' innerjoinperformance_schema.threadscONb.id=c.PROCESSLIST_ID innerjoinperformance_schema.events_statements_currentdONd.THREAD_ID=c.THREAD_ID;"|whilereadABCDEFGH do if["$C"-gt30] then echo$(date+"%Y-%m-%d%H:%M:%S") echo"processid[$D]$E@$Findb[$G]holdtransactiontime$CSQL:$H" fi done>>/tmp/longtransaction.txt
简单说明一下，这里的-gt30是30秒钟的意思，只要超过了30秒钟就认定是长事务，可以根据实际需要自定义。将该脚本加入定时任务中即可执行。
总结：
本文主要介绍了长事务相关内容，怎样找到长事务，怎么处理长事务，如何监控长事务。可能有些小伙伴对事务理解还不多，希望这篇文章对你有所帮助。由于本篇文章列出的查询事务相关语句较多，现总结如下：
#查询所有正在运行的事务及运行时间 selectt.*,to_seconds(now())-to_seconds(t.trx_started)idle_timefromINFORMATION_SCHEMA.INNODB_TRXt\G #查询事务详细信息及执行的SQL selectnow(),(UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(a.trx_started))diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXTfrominformation_schema.innodb_trxainnerjoininformation_schema.PROCESSLISTb ona.TRX_MYSQL_THREAD_ID=b.idandb.command='Sleep' innerjoinperformance_schema.threadscONb.id=c.PROCESSLIST_ID innerjoinperformance_schema.events_statements_currentdONd.THREAD_ID=c.THREAD_ID; #查询事务执行过的所有历史SQL记录 SELECT ps.id'PROCESSID', ps.USER, ps.HOST, esh.EVENT_ID, trx.trx_started, esh.event_name'EVENTNAME', esh.sql_text'SQL', ps.time FROM PERFORMANCE_SCHEMA.events_statements_historyesh JOINPERFORMANCE_SCHEMA.threadsthONesh.thread_id=th.thread_id JOINinformation_schema.PROCESSLISTpsONps.id=th.processlist_id LEFTJOINinformation_schema.innodb_trxtrxONtrx.trx_mysql_thread_id=ps.id WHERE trx.trx_idISNOTNULL ANDps.USER!='SYSTEM_USER' ORDERBY esh.EVENT_ID; #简单查询事务锁 select*fromsys.innodb_lock_waits\G #查询事务锁详细信息 SELECT tmp.*, c.SQL_Textblocking_sql_text, p.HOSTblocking_host FROM ( SELECT r.trx_statewating_trx_state, r.trx_idwaiting_trx_id, r.trx_mysql_thread_Idwaiting_thread, r.trx_querywaiting_query, b.trx_stateblocking_trx_state, b.trx_idblocking_trx_id, b.trx_mysql_thread_idblocking_thread, b.trx_queryblocking_query FROM information_schema.innodb_lock_waitsw INNERJOINinformation_schema.innodb_trxbONb.trx_id=w.blocking_trx_id INNERJOINinformation_schema.innodb_trxrONr.trx_id=w.requesting_trx_id )tmp, information_schema.PROCESSLISTp, PERFORMANCE_SCHEMA.events_statements_currentc, PERFORMANCE_SCHEMA.threadst WHERE tmp.blocking_thread=p.id ANDt.thread_id=c.THREAD_ID ANDt.PROCESSLIST_ID=p.id\G
以上就是深入了解mysql长事务的详细内容，更多关于mysql长事务的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。