本篇文章小编给大家分享一下MySQL找出未提交事务的SQL代码实例解析,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
--准备测试环境数据(实验环境为MySQL 8.0.18社区版)
mysql> create table kkk(id int , name varchar(12)); Query OK, 0 rows affected (0.34 sec) mysql> insert into kkk values(1, 'kerry'); Query OK, 1 row affected (0.01 sec) mysql> insert into kkk values(2, 'jerry'); Query OK, 1 row affected (0.00 sec) mysql> insert into kkk values(3, 'ken'); Query OK, 1 row affected (0.00 sec) mysql> mysql> create table t(a varchar(10)); Query OK, 0 rows affected (0.47 sec) mysql> insert into t values('test'); Query OK, 1 row affected (0.00 sec)
在一个会话窗口(连接ID=38)执行下面SQL
mysql> select connection_id() from dual; +-----------------+ | connection_id() | +-----------------+ | 38 | +-----------------+ 1 row in set (0.00 sec) mysql> set session autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> delete from kkk where id =1; Query OK, 1 row affected (0.00 sec) mysql>
在另外一个会话窗口(连接ID=39)执行下面SQL
mysql> SELECT t.trx_mysql_thread_id -> ,t.trx_id -> ,t.trx_state -> ,t.trx_tables_in_use -> ,t.trx_tables_locked -> ,t.trx_query -> ,t.trx_rows_locked -> ,t.trx_rows_modified -> ,t.trx_lock_structs -> ,t.trx_started -> ,t.trx_isolation_level -> ,p.time -> ,p.user -> ,p.host -> ,p.db -> ,p.command -> FROM information_schema.innodb_trx t -> INNER JOIN information_schema.processlist p -> ON t.trx_mysql_thread_id = p.id -> WHERE t.trx_state = 'RUNNING' -> AND p.time > 4 -> AND p.command = 'Sleep'G *************************** 1. row *************************** trx_mysql_thread_id: 38 trx_id: 7981581 trx_state: RUNNING trx_tables_in_use: 0 trx_tables_locked: 1 trx_query: NULL trx_rows_locked: 4 trx_rows_modified: 1 trx_lock_structs: 2 trx_started: 2020-12-03 15:39:37 trx_isolation_level: REPEATABLE READ time: 23 user: root host: localhost db: MyDB command: Sleep 1 row in set (0.00 sec)
虽然上面这个SQL找不出事务执行过的SQL,其实呢,MySQL中未提交事务的最后执行的一个SQL是可以通过下面脚本准确查找出来的。如下所示:
SELECT t.trx_mysql_thread_id AS connection_id ,t.trx_id AS trx_id ,t.trx_state AS trx_state ,t.trx_started AS trx_started ,TIMESTAMPDIFF(SECOND,t.trx_started, now()) AS "trx_run_time(s)" ,t.trx_requested_lock_id AS trx_requested_lock_id ,t.trx_operation_state AS trx_operation_state ,t.trx_tables_in_use AS trx_tables_in_use ,t.trx_tables_locked AS trx_tables_locked ,t.trx_rows_locked AS trx_rows_locked ,t.trx_isolation_level AS trx_isolation_level ,t.trx_is_read_only AS trx_is_read_only ,t.trx_autocommit_non_locking AS trx_autocommit_non_locking ,e.event_name AS event_name ,e.timer_wait / 1000000000000 AS timer_wait ,e.sql_text FROM information_schema.innodb_trx t, performance_schema.events_statements_current e, performance_schema.threads c WHERE t.trx_mysql_thread_id = c.processlist_id AND e.thread_id = c.thread_idG;
如下截图所示:
在会话窗口(连接ID=38)继续执行下面SQL:"select * from t;"。 如下所示
mysql> set session autocommit=0; Query OK, 0 rows affected (0.01 sec) mysql> delete from kkk where id =1; Query OK, 1 row affected (0.00 sec) mysql> select * from t; +------+ | a | +------+ | test | +------+ 1 row in set (0.00 sec) mysql>
在会话窗口(连接ID=39)上继续执行下面SQL,你会发现捕获的是事务最后执行的SQL语句“select * from t”
mysql> SELECT t.trx_mysql_thread_id AS connection_id -> ,t.trx_id AS trx_id -> ,t.trx_state AS trx_state -> ,t.trx_started AS trx_started -> ,TIMESTAMPDIFF(SECOND,t.trx_started, now()) AS "trx_run_time(s)" -> ,t.trx_requested_lock_id AS trx_requested_lock_id -> ,t.trx_operation_state AS trx_operation_state -> ,t.trx_tables_in_use AS trx_tables_in_use -> ,t.trx_tables_locked AS trx_tables_locked -> ,t.trx_rows_locked AS trx_rows_locked -> ,t.trx_isolation_level AS trx_isolation_level -> ,t.trx_is_read_only AS trx_is_read_only -> ,t.trx_autocommit_non_locking AS trx_autocommit_non_locking -> ,e.event_name AS event_name -> ,e.timer_wait / 1000000000000 AS timer_wait -> ,e.sql_text -> FROM information_schema.innodb_trx t, -> performance_schema.events_statements_current e, -> performance_schema.threads c -> WHERE t.trx_mysql_thread_id = c.processlist_id -> AND e.thread_id = c.thread_idG; *************************** 1. row *************************** connection_id: 38 trx_id: 7981581 trx_state: RUNNING trx_started: 2020-12-03 15:39:37 trx_run_time(s): 237 trx_requested_lock_id: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_rows_locked: 4 trx_isolation_level: REPEATABLE READ trx_is_read_only: 0 trx_autocommit_non_locking: 0 event_name: statement/sql/select timer_wait: 0.0002 sql_text: select * from t 1 row in set (0.00 sec) ERROR: No query specified
也是说上面SQL只能获取未提交事务最后执行的一个SQL语句,生产环境中,一个事务中往往不止一个SQL语句,而是多个SQL语句的集合。如果想将一个未提交事务里面所有执行过的SQL找出来怎么办呢?其实在MySQL中还是有办法的。下面SQL语句就可以找出或者
SELECT trx.trx_mysql_thread_id AS processlist_id ,sc.thread_id ,trx.trx_started ,TO_SECONDS(now())-TO_SECONDS(trx_started) AS trx_last_time ,pc1.user ,pc1.host ,pc1.db ,sc.SQL_TEXT AS current_sql_text ,sh.history_sql_test FROM INFORMATION_SCHEMA.INNODB_TRX trx INNER JOIN INFORMATION_SCHEMA.processlist pc1 ON trx.trx_mysql_thread_id=pc1.id INNER JOIN performance_schema.threads th on th.processlist_id = trx.trx_mysql_thread_id INNER JOIN performance_schema.events_statements_current sc ON sc.THREAD_ID = th.THREAD_ID INNER JOIN ( SELECT thread_id AS thread_id, GROUP_CONCAT(SQL_TEXT SEPARATOR ';') AS history_sql_test FROM performance_schema.events_statements_history GROUP BY thread_id ) sh ON sh.thread_id = th.thread_id WHERE trx_mysql_thread_id != connection_id() AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 0 ;
但是这两个SQL有个问题:它会找出当前连接历史上所有执行过的SQL(当然前提是这些SQL还保存在performance_schema.events_statements_history表中),也就是说这个SQL,不仅查询出未提交事务所有执行过的脚本,而且会查询出很多历史SQL脚本,例如,这个会话(连接)之前事务的SQL语句,而且还有一个比较头疼的问题:这里不好区分哪些SQL对应哪些事务。需要借助其他信息来甄别。比较费时费力。如下截图所示。
因为只有information_schema.innodb_trx系统表中包含事务的开始时间(trx_started),其它系统表没有跟事务相关的时间,只能借助performance_schema.events_statements_history中的TIMER_START字段来获取事件的SQL开始执行的时间,而这个时间必然是小于或等于对应事务的开始时间(trx_started)的。所以从这个突破口来找出未提交事务的所有SQL,下面是关于TIMER_START等字段的详细介绍。
关于TIMER_START,TIMER_END,TIMER_WAIT的介绍如下:
TIMER_START,TIMER_END,TIMER_WAIT:事件的时间信息。这些值的单位是皮秒(万亿分之一秒)。
TIMER_START和TIMER_END值表示事件的开始时间和结束时间。
TIMER_WAIT是事件执行消耗的时间(持续时间)
如果事件未执行完成,则TIMER_END为当前时间,TIMER_WAIT为当前为止所经过的时间(TIMER_END - TIMER_START)。
如果监视仪器配置表setup_instruments中对应的监视器TIMED字段被设置为 NO,则不会收集该监视器的时间信息,那么对于该事件采集的信息记录中,TIMER_START,TIMER_END和TIMER_WAIT字段值均为NULL
测试、折腾了好久,终于搞出了一个几乎完美的SQL:
SELECT @dt_ts:=UNIX_TIMESTAMP(NOW()); SELECT @dt_timer:=MAX(sh.TIMER_START) FROM performance_schema.threads AS t INNER JOIN performance_schema.events_statements_history AS sh ON t.`THREAD_ID`=sh.`THREAD_ID` WHERE t.PROCESSLIST_ID=CONNECTION_ID(); SELECT sh.current_schema AS database_name ,t.thread_id ,it.trx_mysql_thread_id AS connection_id ,it.trx_id ,sh.event_id ,it.trx_state ,REPLACE(REPLACE(REPLACE(sh.`SQL_TEXT`,'n',' '),'r',' '),'t',' ') AS executed_sql ,it.trx_started ,FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) AS start_time ,FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_END) /1000000000000 AS SIGNED)) AS end_time ,(sh.TIMER_END-sh.TIMER_START)/1000000000000 AS used_seconds ,sh.TIMER_WAIT/1000000000000 AS wait_seconds ,sh.LOCK_TIME/1000000000000 AS lock_seconds ,sh.ROWS_AFFECTED AS affected_rows ,sh.ROWS_SENT AS send_rows FROM performance_schema.threads AS t INNER JOIN information_schema.innodb_trx it ON it.trx_mysql_thread_id = t.processlist_id INNER JOIN performance_schema.events_statements_history AS sh ON t.`THREAD_ID`=sh.`THREAD_ID` WHERE t.PROCESSLIST_ID IN ( SELECT p.ID AS conn_id FROM `information_schema`.`INNODB_TRX` t INNER JOIN `information_schema`.`PROCESSLIST` p ON t.trx_mysql_thread_id=p.id WHERE t.trx_state='RUNNING' AND p.COMMAND='Sleep' AND p.TIME>2 ) AND sh.TIMER_START<@dt_timer AND FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_started ORDER BY it.trx_id ASC, sh.TIMER_START ASC;
它能找出未提交事务的SQL,简单测试完全没有问题,同时构造几个未提交事务测试也OK。但是上面SQL由三个SQL组成,总感觉有点别扭,研究了一下,可以改造成下面SQL。
SELECT sh.current_schema AS database_name ,t.thread_id ,it.trx_mysql_thread_id AS connection_id ,it.trx_id ,sh.event_id ,it.trx_state ,REPLACE(REPLACE(REPLACE(sh.`SQL_TEXT`,'n',' '),'r',' '),'t',' ') AS executed_sql ,it.trx_started ,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_START*10e-13 second) AS start_time ,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_END*10e-13 second) AS end_time ,(sh.TIMER_END-sh.TIMER_START)/1000000000000 AS used_seconds ,sh.TIMER_WAIT/1000000000000 AS wait_seconds ,sh.LOCK_TIME/1000000000000 AS lock_seconds ,sh.ROWS_AFFECTED AS affected_rows ,sh.ROWS_SENT AS send_rows FROM performance_schema.threads AS t INNER JOIN information_schema.innodb_trx it ON it.trx_mysql_thread_id = t.processlist_id INNER JOIN performance_schema.events_statements_history AS sh ON t.`THREAD_ID`=sh.`THREAD_ID` WHERE t.PROCESSLIST_ID IN ( SELECT p.ID AS conn_id FROM `information_schema`.`INNODB_TRX` t INNER JOIN `information_schema`.`PROCESSLIST` p ON t.trx_mysql_thread_id=p.id WHERE t.trx_state='RUNNING' AND p.COMMAND='Sleep' AND p.TIME>2 ) AND sh.TIMER_START<(SELECT VARIABLE_VALUE*1000000000000 FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') AND DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_START*10e-13 second) >=it.trx_started ORDER BY it.trx_id ASC, sh.TIMER_START ASC;
注意:performance_schema.global_status是MySQL 5.7引入的,如果数据库是MySQL 5.6的话,可以用INFORMATION_SCHEMA.GLOBAL_STATUS替换SQL中的performance_schema.global_status
那么是否这个SQL就一定完美了呢? 网友MSSQL123反馈在一个测试环境中,发现上面这种SQL居然查不出任何数据,因为FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_started会将数据过滤掉,检查发现对应的trx_started值都大于start_time
-------------------------------------------------------------------------补充资料--------------------------------------------------------------------------------------------------------
那么同一个测试环境,隔天测试的时候,突然发现上面第一个SQL正常,第二个SQL就由于不同的写法,start_time有细微的差别,导致查询结果完全不同(第二个SQL语句精确到毫秒,对比的时候由于偏差过滤掉一批数据)