很久之前曾經(jīng)總結(jié)過(guò)一篇博客“MySQL如何找出未提交事務(wù)信息”，現(xiàn)在看來(lái)，這篇文章中不少知識(shí)點(diǎn)或觀點(diǎn)都略顯膚淺，或者說(shuō)不夠深入，甚至部分結(jié)論是錯(cuò)誤的。下面重新探討一下這個(gè)話題。那么我們還是以之前的例子來(lái)介紹。

--準(zhǔn)備測(cè)試環(huán)境數(shù)據(jù)（實(shí)驗(yàn)環(huán)境為MySQL 8.0.18社區(qū)版）

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)

在一個(gè)會(huì)話窗口（連接ID=38）執(zhí)行下面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>

在另外一個(gè)會(huì)話窗口（連接ID=39）執(zhí)行下面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:37trx_isolation_level: REPEATABLE READ time: 23 user: root host: localhost db: MyDB command: Sleep1 row in set (0.00 sec)

雖然上面這個(gè)SQL找不出事務(wù)執(zhí)行過(guò)的SQL，其實(shí)呢，MySQL中未提交事務(wù)的最后執(zhí)行的一個(gè)SQL是可以通過(guò)下面腳本準(zhǔn)確查找出來(lái)的。如下所示：

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;

如下截圖所示：

在會(huì)話窗口（連接ID=38）繼續(xù)執(zhí)行下面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>

在會(huì)話窗口（連接ID=39）上繼續(xù)執(zhí)行下面SQL，你會(huì)發(fā)現(xiàn)捕獲的是事務(wù)最后執(zhí)行的SQL語(yǔ)句“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: 0trx_autocommit_non_locking: 0 event_name: statement/sql/select timer_wait: 0.0002 sql_text: select * from t1 row in set (0.00 sec) ERROR: No query specified

也是說(shuō)上面SQL只能獲取未提交事務(wù)最后執(zhí)行的一個(gè)SQL語(yǔ)句，生產(chǎn)環(huán)境中，一個(gè)事務(wù)中往往不止一個(gè)SQL語(yǔ)句，而是多個(gè)SQL語(yǔ)句的集合。如果想將一個(gè)未提交事務(wù)里面所有執(zhí)行過(guò)的SQL找出來(lái)怎么辦呢？其實(shí)在MySQL中還是有辦法的。下面SQL語(yǔ)句就可以找出或者

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_testFROM INFORMATION_SCHEMA.INNODB_TRX trxINNER JOIN INFORMATION_SCHEMA.processlist pc1 ON trx.trx_mysql_thread_id=pc1.idINNER JOIN performance_schema.threads th on th.processlist_id = trx.trx_mysql_thread_idINNER JOIN performance_schema.events_statements_current sc ON sc.THREAD_ID = th.THREAD_IDINNER 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_idWHERE trx_mysql_thread_id != connection_id() AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 0 ;

但是這兩個(gè)SQL有個(gè)問(wèn)題：它會(huì)找出當(dāng)前連接歷史上所有執(zhí)行過(guò)的SQL（當(dāng)然前提是這些SQL還保存在performance_schema.events_statements_history表中），也就是說(shuō)這個(gè)SQL，不僅查詢出未提交事務(wù)所有執(zhí)行過(guò)的腳本，而且會(huì)查詢出很多歷史SQL腳本，例如，這個(gè)會(huì)話（連接）之前事務(wù)的SQL語(yǔ)句，而且還有一個(gè)比較頭疼的問(wèn)題：這里不好區(qū)分哪些SQL對(duì)應(yīng)哪些事務(wù)。需要借助其他信息來(lái)甄別。比較費(fèi)時(shí)費(fèi)力。如下截圖所示。

因?yàn)橹挥衖nformation_schema.innodb_trx系統(tǒng)表中包含事務(wù)的開(kāi)始時(shí)間（trx_started），其它系統(tǒng)表沒(méi)有跟事務(wù)相關(guān)的時(shí)間，只能借助performance_schema.events_statements_history中的TIMER_START字段來(lái)獲取事件的SQL開(kāi)始執(zhí)行的時(shí)間，而這個(gè)時(shí)間必然是小于或等于對(duì)應(yīng)事務(wù)的開(kāi)始時(shí)間（trx_started）的。所以從這個(gè)突破口來(lái)找出未提交事務(wù)的所有SQL，下面是關(guān)于TIMER_START等字段的詳細(xì)介紹。

關(guān)于TIMER_START，TIMER_END，TIMER_WAIT的介紹如下：

TIMER_START，TIMER_END，TIMER_WAIT：事件的時(shí)間信息。這些值的單位是皮秒（萬(wàn)億分之一秒）。

TIMER_START和TIMER_END值表示事件的開(kāi)始時(shí)間和結(jié)束時(shí)間。

TIMER_WAIT是事件執(zhí)行消耗的時(shí)間（持續(xù)時(shí)間）

如果事件未執(zhí)行完成，則TIMER_END為當(dāng)前時(shí)間，TIMER_WAIT為當(dāng)前為止所經(jīng)過(guò)的時(shí)間（TIMER_END - TIMER_START）。

如果監(jiān)視儀器配置表setup_instruments中對(duì)應(yīng)的監(jiān)視器TIMED字段被設(shè)置為 NO，則不會(huì)收集該監(jiān)視器的時(shí)間信息，那么對(duì)于該事件采集的信息記錄中，TIMER_START，TIMER_END和TIMER_WAIT字段值均為NULL

測(cè)試、折騰了好久，終于搞出了一個(gè)幾乎完美的SQL：

SELECT @dt_ts:=UNIX_TIMESTAMP(NOW());SELECT @dt_timer:=MAX(sh.TIMER_START)FROM performance_schema.threads AS tINNER JOIN performance_schema.events_statements_history AS shON 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_rowsFROM performance_schema.threads AS tINNER 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_timerAND FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_startedORDER BY it.trx_id ASC, sh.TIMER_START ASC;

它能找出未提交事務(wù)的SQL，簡(jiǎn)單測(cè)試完全沒(méi)有問(wèn)題，同時(shí)構(gòu)造幾個(gè)未提交事務(wù)測(cè)試也OK。但是上面SQL由三個(gè)SQL組成，總感覺(jué)有點(diǎn)別扭，研究了一下，可以改造成下面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_rowsFROM performance_schema.threads AS tINNER 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_startedORDER BY it.trx_id ASC, sh.TIMER_START ASC;

注意：performance_schema.global_status是MySQL 5.7引入的，如果數(shù)據(jù)庫(kù)是MySQL 5.6的話，可以用INFORMATION_SCHEMA.GLOBAL_STATUS替換SQL中的performance_schema.global_status

那么是否這個(gè)SQL就一定完美了呢？ 網(wǎng)友MSSQL123反饋在一個(gè)測(cè)試環(huán)境中，發(fā)現(xiàn)上面這種SQL居然查不出任何數(shù)據(jù)，因?yàn)镕ROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_started會(huì)將數(shù)據(jù)過(guò)濾掉，檢查發(fā)現(xiàn)對(duì)應(yīng)的trx_started值都大于start_time

-------------------------------------------------------------------------補(bǔ)充資料--------------------------------------------------------------------------------------------------------

那么同一個(gè)測(cè)試環(huán)境，隔天測(cè)試的時(shí)候，突然發(fā)現(xiàn)上面第一個(gè)SQL正常，第二個(gè)SQL就由于不同的寫(xiě)法，start_time有細(xì)微的差別，導(dǎo)致查詢結(jié)果完全不同（第二個(gè)SQL語(yǔ)句精確到毫秒，對(duì)比的時(shí)候由于偏差過(guò)濾掉一批數(shù)據(jù)）

------------------------------------------------------------------------------------------------------------------------------------------------

搜索相關(guān)文檔，了解到TIMER_START字段值可能有波動(dòng)或偏差，那么這個(gè)波動(dòng)或偏差可能影響查詢結(jié)果，下面內(nèi)容來(lái)自http://porthos.ist.utl.pt/docs/mySQL/performance-schema.html

Modifications to the setup_timers table affect monitoring immediately. Events already in progress may use the original timer for the begin time and the new timer for the end time. To avoid unpredictable results after you make timer changes, use TRUNCATE TABLE to reset Performance Schema statistics.

The timer baseline (“time zero”) occurs at Performance Schema initialization during server startup. TIMER_START and TIMER_END values in events represent picoseconds since the baseline. TIMER_WAIT values are durations in picoseconds.

Picosecond values in events are approximate. Their accuracy is subject to the usual forms of error associated with conversion from one unit to another. If the CYCLE timer is used and the processor rate varies, there might be drift. For these reasons, it is not reasonable to look at the TIMER_START value for an event as an accurate measure of time elapsed since server startup. On the other hand, it is reasonable to use TIMER_START or TIMER_WAIT values in ORDER BY clauses to order events by start time or duration.

The choice of picoseconds in events rather than a value such as microseconds has a performance basis. One implementation goal was to show results in a uniform time unit, regardless of the timer. In an ideal world this time unit would look like a wall-clock unit and be reasonably precise; in other words, microseconds. But to convert cycles or nanoseconds to microseconds, it would be necessary to perform a division for every instrumentation. Division is expensive on many platforms. Multiplication is not expensive, so that is what is used. Therefore, the time unit is an integer multiple of the highest possible TIMER_FREQUENCY value, using a multiplier large enough to ensure that there is no major precision loss. The result is that the time unit is “picoseconds.” This precision is spurious, but the decision enables overhead to be minimized.

Before MySQL 5.7.8, while a wait, stage, statement, or transaction event is executing, the respective current-event tables display the event with TIMER_START populated, but with TIMER_END and TIMER_WAIT set to NULL

其中一段內(nèi)容翻譯如下：

事件中的皮秒值是近似值。它們的準(zhǔn)確性受與從一個(gè)單位轉(zhuǎn)換到另一單位相關(guān)的常見(jiàn)誤差形式的影響。如果使用了CYCLE定時(shí)器，并且處理器速率有所變化，則可能會(huì)有偏差。由于這些原因，將事件的TIMER_START值視為自服務(wù)器啟動(dòng)以來(lái)經(jīng)過(guò)的時(shí)間的準(zhǔn)確度量是不合理的。另一方面，在ORDER BY子句中使用TIMER_START或TIMER_WAIT值來(lái)按開(kāi)始時(shí)間或持續(xù)時(shí)間對(duì)事件進(jìn)行排序是合理的。

我們往往想一擊必殺的解決問(wèn)題，但是由于復(fù)雜的環(huán)境和一些不可控因素，現(xiàn)實(shí)往往就是“沒(méi)有銀彈”這么殘酷。如果遇到TIMER_START的波動(dòng)或偏差影響查詢結(jié)果時(shí)，那么我們必須通過(guò)文章前面的SQL找出大量SQL，然后通過(guò)其它字段或信息人工甄別哪些才是未提交事務(wù)的SQL。

參考資料：

https://stackoverflow.com/questions/25607249/mysql-performance-schema-how-to-get-event-time-from-events-statements-current-ta

http://porthos.ist.utl.pt/docs/mySQL/performance-schema.html

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-timing.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-timing.html

到此這篇關(guān)于MySQL找出未提交事務(wù)SQL的文章就介紹到這了,更多相關(guān)MySQL找出未提交事務(wù)SQL內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！