溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

MySQL中的長事務(wù)示例詳解

發(fā)布時間：2020-09-02 14:01:23 來源：腳本之家 閱讀：162 作者：kunjian 欄目：MySQL數(shù)據(jù)庫

前言：

『入門MySQL』系列文章已經(jīng)完結(jié)，今后我的文章還是會以MySQL為主，主要記錄下近期工作及學(xué)習(xí)遇到的場景或者自己的感悟想法，可能后續(xù)的文章不是那么連貫，但還是希望大家多多支持。言歸正傳，本篇文章主要介紹MySQL長事務(wù)相關(guān)內(nèi)容，比如說我們開啟的一個事務(wù)，一直沒提交或回滾會怎樣呢，出現(xiàn)事務(wù)等待情況應(yīng)該如何處理，本篇文章將給你答案。

注意：本篇文章并不聚焦于談?wù)撌聞?wù)隔離級別以及相關(guān)特性。而是介紹長事務(wù)相關(guān)危害以及監(jiān)控處理方法。本文是基于MySQL5.7.23版本，不可重復(fù)讀(RR)隔離級別所做實驗。

1.什么是長事務(wù)

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

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

#假設(shè)我們有一張stu_tb表，結(jié)構(gòu)及數(shù)據(jù)如下
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 '學(xué)號',
 `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名',
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
 `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='測試學(xué)生表'
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)

#顯式開啟事務(wù)，可用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)

#如果我們不及時提交上個事務(wù)，那么這個事務(wù)就變成了長事務(wù)，當其他會話要操作這條數(shù)據(jù)時，就會一直等待。

2.如何找到長事務(wù)

遇到事務(wù)等待問題時，我們首先要做的是找到正在執(zhí)行的事務(wù)。 information_schema.INNODB_TRX 表中包含了當前innodb內(nèi)部正在運行的事務(wù)信息，這個表中給出了事務(wù)的開始時間，我們可以稍加運算即可得到事務(wù)的運行時間。

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

在結(jié)果中idle_time是計算產(chǎn)生的，也是事務(wù)的持續(xù)時間。但事務(wù)的trx_query是NUL，這并不是說事務(wù)什么也沒執(zhí)行，一個事務(wù)可能包含多個SQL，如果SQL執(zhí)行完畢就不再顯示了。當前事務(wù)正在執(zhí)行，innodb也不知道這個事務(wù)后續(xù)還有沒有sql,啥時候會commit。 因此trx_query不能提供有意義的信息。

如果我們想看到這個事務(wù)執(zhí)行過的SQL，看是否可以殺掉長事務(wù)，怎么辦呢？我們可以聯(lián)合其他系統(tǒng)表查詢得到，具體查詢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 |
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+

上述結(jié)果中diff_sec和上面idle_time表示意思相同，都是代表此事務(wù)持續(xù)的秒數(shù)。SQL_TEXT表示該事務(wù)剛執(zhí)行的SQL。但是呢，上述語句只能查到事務(wù)最后執(zhí)行的SQL，我們知道，一個事務(wù)里可能包含多個SQL，那我們想查詢這個未提交的事務(wù)執(zhí)行過哪些SQL，是否可以滿足呢，答案是結(jié)合events_statements_history系統(tǒng)表也可以滿足需求。下面語句將會查詢出該事務(wù)執(zhí)行過的所有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 |
+---------------------+----------+----+------+-----------+--------+-----------------------------------------------------+

從上述結(jié)果中我們可以看到該事務(wù)從一開始到現(xiàn)在執(zhí)行過的所有SQL，當我們把該事務(wù)相關(guān)信息都查詢清楚后，我們就可以判定該事務(wù)是否可以殺掉，以免影響其他事務(wù)造成等待現(xiàn)象。

在這里稍微拓展下，長事務(wù)極易造成阻塞或者死鎖現(xiàn)象，通常情況下我們可以首先查詢 sys.innodb_lock_waits 視圖確定有沒有事務(wù)阻塞現(xiàn)象：

#假設(shè)一個事務(wù)執(zhí)行 select * from stu_tb where stu_id = 1006 for update
#另外一個事務(wù)執(zhí)行 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

上述結(jié)果顯示出被阻塞的SQL以及鎖的類型，更強大的是殺掉會話的語句也給出來了。但是并沒有找到阻塞會話執(zhí)行的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

上面結(jié)果顯得更加清晰，我們可以清楚的看到阻塞端及被阻塞端事務(wù)執(zhí)行的語句，有助于我們排查并確認是否可以殺掉阻塞的會話。

3.監(jiān)控長事務(wù)

現(xiàn)實工作中我們需要監(jiān)控下長事務(wù)，定義一個閾值，比如說30s 執(zhí)行時間超過30s的事務(wù)即為長事務(wù)，要求記錄并告警出來，提醒管理人員去處理。下面給出監(jiān)控腳本，各位可以參考下，根據(jù)需求改動使用：

#!/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秒鐘就認定是長事務(wù)，可以根據(jù)實際需要自定義。將該腳本加入定時任務(wù)中即可執(zhí)行。

總結(jié)：

本文主要介紹了長事務(wù)相關(guān)內(nèi)容，怎樣找到長事務(wù)，怎么處理長事務(wù)，如何監(jiān)控長事務(wù)。可能有些小伙伴對事務(wù)理解還不多，希望這篇文章對你有所幫助。由于本篇文章列出的查詢事務(wù)相關(guān)語句較多，現(xiàn)總結(jié)如下：

# 查詢所有正在運行的事務(wù)及運行時間
select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G

# 查詢事務(wù)詳細信息及執(zhí)行的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;

# 查詢事務(wù)執(zhí)行過的所有歷史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;
 
 # 簡單查詢事務(wù)鎖
 select * from sys.innodb_lock_waits\G
 
 # 查詢事務(wù)鎖詳細信息
 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

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對億速云的支持。

向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手