如何找到MySQL長事務

本文主要給大家簡單講講如何找到MySQL長事務，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望如何找到MySQL長事務這篇文章可以給大家帶來一些實際幫助。

1.什么是長事務

首先我們先要知道什么是長事務，顧名思義就是運行時間比較長，長時間未提交的事務，也可以稱之為大事務。這類事務往往會造成大量的阻塞和鎖超時，容易造成主從延遲，要盡量避免使用長事務。

下面我將演示下如何開啟事務及模擬長事務：

#假設我們有一張stu_tb表，結構及數據如下
mysql> show create table stu_tb\G
*************************** 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
    ->   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;
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| PROCESS ID | USER | HOST      | EVENT_ID | trx_started         | EVENT NAME                   | SQL                                                 | time |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
|         20 | root | localhost |        1 | 2019-09-16 14:18:44 | statement/sql/select         | select @@version_comment limit 1                    |   60 |
|         20 | root | localhost |        2 | 2019-09-16 14:18:44 | statement/sql/begin          | start transaction                                   |   60 |
|         20 | root | localhost |        3 | 2019-09-16 14:18:44 | statement/sql/select         | SELECT DATABASE()                                   |   60 |
|         20 | root | localhost |        4 | 2019-09-16 14:18:44 | statement/com/Init DB        | NULL                                                |   60 |
|         20 | root | localhost |        5 | 2019-09-16 14:18:44 | statement/sql/show_databases | show databases                                      |   60 |
|         20 | root | localhost |        6 | 2019-09-16 14:18:44 | statement/sql/show_tables    | show tables                                         |   60 |
|         20 | root | localhost |        7 | 2019-09-16 14:18:44 | statement/com/Field List     | NULL                                                |   60 |
|         20 | root | localhost |        8 | 2019-09-16 14:18:44 | statement/com/Field List     | NULL                                                |   60 |
|         20 | root | localhost |        9 | 2019-09-16 14:18:44 | statement/sql/select         | select * from stu_tb                                |   60 |
|         20 | root | localhost |       10 | 2019-09-16 14:18:44 | statement/sql/select         | select * from stu_tb where stu_id = 1006 for update |   60 |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+

從上述結果中我們可以看到該事務從一開始到現在執行過的所有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_waits\G
*************************** 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_waits\G

 # 查詢事務鎖詳細信息
 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
如何找到MySQL長事務就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。