查看MySQL最近的事務執行信息

發布時間：2020-03-03 12:35:07 來源：網絡閱讀：316 作者：wjw555 欄目：系統運維

課題：查看MySQL最近的事務執行信息

*雖然我們可以通過查詢慢查詢日志查詢到一條語句的執行總時長，但是如果數據庫中存在一些大事務在執行過程中回滾了，，或者在執行過程中異常終止了，這個時候慢查詢日志中是不會記錄的，這時需要借助
performance_schema的 eventstransactions—的表來查看與事務相關的記錄，在這些表中詳細記錄了是否有事務被回滾，活躍（長時間未提交的事務也屬于活躍事務）活已提交等信息。**

下面模擬幾種事務情況，并查看事務事件記錄表：

事務事件，默認是沒啟用的，首先需要進行配置啟用


root@localhost [performance_schema]>select * from setup_instruments where name like 'transaction';
+-------------+---------+-------+
| NAME        | ENABLED | TIMED |
+-------------+---------+-------+
| transaction | NO      | NO    |
+-------------+---------+-------+
1 row in set (0.00 sec)

root@localhost [performance_schema]>select * from setup_instruments where name like '%transaction%';
+--------------------------------------------------------------------------------+---------+-------+
| NAME                                                                           | ENABLED | TIMED |
+--------------------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/LOCK_transaction_cache                                    | NO      | NO    |
| stage/sql/Waiting for preceding transaction to commit                          | NO      | NO    |
| stage/sql/Waiting for dependent transaction to commit                          | NO      | NO    |
| transaction                                                                    | NO      | NO    |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name    | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name  | YES     | NO    |
| memory/performance_schema/events_transactions_history                          | YES     | NO    |
| memory/performance_schema/events_transactions_summary_by_user_by_event_name    | YES     | NO    |
| memory/performance_schema/events_transactions_history_long                     | YES     | NO    |
| memory/sql/THD::transactions::mem_root                                         | YES     | NO    |
+--------------------------------------------------------------------------------+---------+-------+
11 rows in set (0.00 sec)

查看最近的事務執行信息：

開啟事務生產者的參數：

root@localhost [performance_schema]>update setup_instruments set enabled='yes',timed='yes' where name like 'transaction';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@localhost [performance_schema]>update setup_instruments  set enabled='yes' where name like '%transaction%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 11  Changed: 3  Warnings: 0

登錄mysql會話1，執行清理，避免其他事務的干擾：
提示：線上最好不要直接truncate清除

root@localhost [performance_schema]>truncate events_transactions_current;truncate events_transactions_history; truncate events_transactions_history_long;

root@localhost [performance_schema]>select * from events_transactions_current;
Empty set (0.00 sec)

root@localhost [performance_schema]>select * from events_transactions_history;
Empty set (0.00 sec)

root@localhost [performance_schema]>select * from events_transactions_history_long;
Empty set (0.00 sec)

開啟新的會話2，用于執行事務，并模擬事務的回滾：

root@localhost [test001]>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test001]>update test1_event set username='fox' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@localhost [test001]>rollback;
Query OK, 0 rows affected (0.00 sec)

在會話1查看當前活躍的事務，查看結果卻是空值：

root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
Empty set (0.00 sec)
root@localhost [(none)]>

root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_history_long\G
Empty set (0.00 sec)
root@localhost [(none)]>

root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_history
Empty set (0.00 sec)
root@localhost [(none)]>

查看結果卻是空值，原因是MySQL消費者參數setup_consumers這一塊沒開啟

root@localhost [performance_schema]>select * from setup_consumers where name like '%transaction%';
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
+----------------------------------+---------+
3 rows in set (0.01 sec)

開啟消費者參數：

root@localhost [performance_schema]>update setup_consumers  set enabled='yes' where name like '%transaction%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

root@localhost [performance_schema]>select * from setup_consumers where name like '%transaction%';
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_transactions_current      | YES     |
| events_transactions_history      | YES     |
| events_transactions_history_long | YES     |
+----------------------------------+---------+
3 rows in set (0.00 sec)

這些消費參數在分析完事務后記得關閉，關閉命令如下：

update setup_consumers  set enabled='no' where name like '%transaction%';

再次在會話1查看當前活躍的事務：

root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
         THREAD_ID: 3957325
        EVENT_NAME: transaction
             STATE: ACTIVE
            TRX_ID: NULL
              GTID: AUTOMATIC
            SOURCE: 
        TIMER_WAIT: 85087447373000
       ACCESS_MODE: READ WRITE
   ISOLATION_LEVEL: REPEATABLE READ
        AUTOCOMMIT: NO
  NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

在會話2 上rollback回滾事務：

root@localhost [test001]>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test001]>update test1_event set username='fox' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@localhost [test001]>rollback;
Query OK, 0 rows affected (0.00 sec)

再次在會話1查看當前活躍的事務：


root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
         THREAD_ID: 3957325
        EVENT_NAME: transaction
             STATE: ROLLED BACK
            TRX_ID: NULL
              GTID: AUTOMATIC
            SOURCE: 
        TIMER_WAIT: 170837979344000
       ACCESS_MODE: READ WRITE
   ISOLATION_LEVEL: REPEATABLE READ
        AUTOCOMMIT: NO
  NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

查看事務事件歷史記錄表：events_transactions_history：


root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_history\G
*************************** 1. row ***************************
         THREAD_ID: 3957325
        EVENT_NAME: transaction
             STATE: ROLLED BACK
            TRX_ID: NULL
              GTID: AUTOMATIC
            SOURCE: 
        TIMER_WAIT: 170837979344000
       ACCESS_MODE: READ WRITE
   ISOLATION_LEVEL: REPEATABLE READ
        AUTOCOMMIT: NO
  NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
2 rows in set (0.00 sec)

root@localhost [test001]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from performance_schema.events_transactions_history_long where STATE='ROLLED BACK'\G
*************************** 1. row ***************************
         THREAD_ID: 3957325
        EVENT_NAME: transaction
             STATE: ROLLED BACK
            TRX_ID: NULL
              GTID: AUTOMATIC
            SOURCE: 
        TIMER_WAIT: 170837979344000
       ACCESS_MODE: READ WRITE
   ISOLATION_LEVEL: REPEATABLE READ
        AUTOCOMMIT: NO
  NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

++++++++++++++++++++++++++++=+++++++++++++++++++++++++
在會話2上模擬正常提交事務：
開始事務


root@localhost [test001]>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test001]>update test1_event set username='fox' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

會話1上查看事務信息：


root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
         THREAD_ID: 3957325
        EVENT_NAME: transaction
             STATE: ACTIVE
            TRX_ID: NULL
              GTID: AUTOMATIC
            SOURCE: 
        TIMER_WAIT: 18063808685000
       ACCESS_MODE: READ WRITE
   ISOLATION_LEVEL: REPEATABLE READ
        AUTOCOMMIT: NO
  NESTING_EVENT_ID: 75
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

在會話2上正常提交事務

root@localhost [test001]>commit;
Query OK, 0 rows affected (0.00 sec)

會話1上查看事務信息：


root@localhost [performance_schema]>
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
         THREAD_ID: 3957325
        EVENT_NAME: transaction
             STATE: COMMITTED                                           ##事務已經提交成功
            TRX_ID: NULL
              GTID: 64a062d1-2e92-11ea-847e-801844ed7bbc:2584551         ##事務正常提交后要記錄Gtid的
            SOURCE: 
        TIMER_WAIT: 28245631402000
       ACCESS_MODE: READ WRITE
   ISOLATION_LEVEL: REPEATABLE READ
        AUTOCOMMIT: NO
  NESTING_EVENT_ID: 75
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

提示：如果一個事務長時間未提交（長時間處于ACTIVE狀態）對于這情況，俺們可以查看表nformation_schema.innodb_trx 表直觀的看到事務是什么時候開始的，
以及產生這個事務的mysql的線程id號trx_mysql_thread_id: 3957292（也就是會話2的線程id號）

root@localhost [performance_schema]>select * from information_schema.innodb_trx \G
*************************** 1. row ***************************
                    trx_id: 2594744
                 trx_state: RUNNING
               trx_started: 2020-02-13 17:53:23
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 3957292

performance_schema相關表意思具體可參考下面資料：
配置詳解 | performance_schema全方位介紹（二）
http://www.sohu.com/a/253338003_610509
應用示例薈萃 | performance_schema全方位介紹
https://blog.csdn.net/woqutechteam/article/details/81114520

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

查看MySQL最近的事務執行信息

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

查看MySQL最近的事務執行信息

猜你喜歡

最新資訊

相關推薦

相關標簽