深入了解mysql長事務(wù)
更新時間：2020年08月10日 10:58:33 作者：mysql技術(shù)
這篇文章主要介紹了mysql長事務(wù)的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)mysql，感興趣的朋友可以了解下
前言：
本篇文章主要介紹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)隔離級別所做實(shí)驗(yàn)。(語句為\G可以使查詢結(jié)構(gòu)顯示更易讀，但只可以在mysql命令行使用。)
1.什么是長事務(wù)
首先我們先要知道什么是長事務(wù)，顧名思義就是運(yùn)行時間比較長，長時間未提交的事務(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ù)，當(dāng)其他會話要操作這條數(shù)據(jù)時，就會一直等待。
2.如何找到長事務(wù)
遇到事務(wù)等待問題時，我們首先要做的是找到正在執(zhí)行的事務(wù)。information_schema.INNODB_TRX 表中包含了當(dāng)前innodb內(nèi)部正在運(yùn)行的事務(wù)信息，這個表中給出了事務(wù)的開始時間，我們可以稍加運(yùn)算即可得到事務(wù)的運(yùn)行時間。
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是計(jì)算產(chǎn)生的，也是事務(wù)的持續(xù)時間。但事務(wù)的trx_query是NUL，這并不是說事務(wù)什么也沒執(zhí)行，一個事務(wù)可能包含多個SQL，如果SQL執(zhí)行完畢就不再顯示了。當(dāng)前事務(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 -> 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 | +------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
從上述結(jié)果中我們可以看到該事務(wù)從一開始到現(xiàn)在執(zhí)行過的所有SQL，當(dāng)我們把該事務(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以及鎖的類型，更強(qiáng)大的是殺掉會話的語句也給出來了。但是并沒有找到阻塞會話執(zhí)行的SQL，如果我們想找出更詳細(xì)的信息，可以使用下面語句：
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í)行的語句，有助于我們排查并確認(rèn)是否可以殺掉阻塞的會話。
3.監(jiān)控長事務(wù)
現(xiàn)實(shí)工作中我們需要監(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秒鐘就認(rèn)定是長事務(wù)，可以根據(jù)實(shí)際需要自定義。將該腳本加入定時任務(wù)中即可執(zhí)行。
總結(jié)：
本文主要介紹了長事務(wù)相關(guān)內(nèi)容，怎樣找到長事務(wù)，怎么處理長事務(wù)，如何監(jiān)控長事務(wù)?？赡苡行┬』锇閷κ聞?wù)理解還不多，希望這篇文章對你有所幫助。由于本篇文章列出的查詢事務(wù)相關(guān)語句較多，現(xiàn)總結(jié)如下：
# 查詢所有正在運(yùn)行的事務(wù)及運(yùn)行時間 select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G # 查詢事務(wù)詳細(xì)信息及執(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ù)鎖詳細(xì)信息 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長事務(wù)的詳細(xì)內(nèi)容，更多關(guān)于mysql長事務(wù)的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于MySQL中UNION與UNION ALL的基本使用方法，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
MySQL的Redo Log數(shù)據(jù)恢復(fù)核心機(jī)制面試精講
這篇文章主要為大家介紹了MySQL的Redo Log數(shù)據(jù)恢復(fù)核心機(jī)制面試精講,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-10-10
如果要在程序運(yùn)行過程中操作數(shù)據(jù)庫中的數(shù)據(jù),那得先學(xué)會使用SQL語句,下面這篇文章主要給大家介紹了關(guān)于SQL查詢語句更新和刪除數(shù)據(jù)的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-01-01
MySQL查詢重復(fù)記錄和刪除重復(fù)記錄的操作方法
在MySQL數(shù)據(jù)庫中,有時候會出現(xiàn)重復(fù)記錄的情況,這可能會導(dǎo)致數(shù)據(jù)不準(zhǔn)確或者不符合業(yè)務(wù)需求,為了解決這個問題,我們可以使用查詢語句來找出重復(fù)記錄,并使用刪除語句來刪除這些重復(fù)記錄,本文給大家介紹了兩種操作方法,需要的朋友可以參考下2024-12-12
這篇文章主要介紹了一文解答什么是MySQL的回表，回表就是?MySQL要先查詢到主鍵索引，然后再用主鍵索引定位到數(shù)據(jù)，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，需要的朋友可以參考一下2022-08-08
mysql遷移至8.0時的注意事項(xiàng)(小結(jié))
這篇文章主要介紹了mysql遷移至8.0時的注意事項(xiàng)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-05-05
本文主要介紹了MySQL數(shù)據(jù)庫表空間回收的解決，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-02-02
死鎖是數(shù)據(jù)庫并發(fā)控制中的一種現(xiàn)象,它涉及多個事務(wù)在執(zhí)行過程中相互等待對方占有的資源,導(dǎo)致無法繼續(xù)執(zhí)行,本文就來介紹一下Mysql5.7并發(fā)插入死鎖問題解決,感興趣的可以了解一下2024-09-09
這篇文章主要介紹MySQL讀取my.cnf的順序問題，以mysql.server、mysqld_safe及mysqld和my_print_defaults讀取my.cnf順序展開文章介紹主題，感興趣的話大家一起來學(xué)習(xí)吧2021-09-09
本文系統(tǒng)講解MySQL表操作與查詢,涵蓋創(chuàng)建、修改、復(fù)制表語法,基本查詢結(jié)構(gòu)及WHERE、GROUP BY等子句,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2025-06-06