MySQL定位長事務(Identify Long Transactions)的實現(xiàn)

更新時間：2024年09月03日 09:05:57 作者：V1ncent Chen

在MySQL的運行中,經(jīng)常會遇到一些長事務,本文主要介紹了MySQL定位長事務,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

在MySQL的運行中，經(jīng)常會遇到一些長事務。長事務意味著長時間持有系統(tǒng)資源，這在OLAP系統(tǒng)中很常見，但在OLTP系統(tǒng)中，長事務意味著爭用、并發(fā)降低，等待。長事務伴隨的典型現(xiàn)象就是經(jīng)常聽到開發(fā)人員說"xxx表被鎖住了…"

一、長事務的成因

長事務表面上來看都是運行時間過長。但其背地里的成因卻可能不同，我認為長事務的成因可以分為以下3類：

表、索引設計不合理，存在慢SQL
事務設計不合理，耦合度過高
事務未正常結束，例如忘記事務提交或事務執(zhí)行出錯后沒有后續(xù)處理。

第一類：表、索引設計不合理，這種就是常見的慢SQL導致事務執(zhí)行時間過長，有些慢SQL在數(shù)據(jù)量低的時候可能無法發(fā)現(xiàn)，當生產(chǎn)數(shù)據(jù)逐漸增多，慢SQL的問題會越來越嚴重，最終導致長事務。這類長事務的解決方式是優(yōu)化SQL（可以通過慢查詢日志抓取慢SQL）。

第二類：事務設計不合理是將大量的邏輯處理塞到一個事務中，導致事務過于臃腫。這種問題需要從業(yè)務層面分析，看是否可以將過大事務拆分成多個獨立事務，降低耦合，對于OLTP系統(tǒng)，大部分都應該是短小的事務。

第三類：事務未正常結束，這種可能是忘記提交，或者事務處理中出錯，但用戶沒有后續(xù)處理。當事務某條語句出錯時，其仍然處于活躍狀態(tài)，已成功執(zhí)行語句的鎖會繼續(xù)持有。某些人可能會直接殺死客戶端連接，但對數(shù)據(jù)庫來說，并沒有收到顯式結束事務的命令，它保持事務是活躍狀態(tài)，一直等待用戶的命令，直到互動超時（interactive_timeout 默認28800秒，即會話8小時沒活動，關閉會話）。

以上三類長事務中，第一二類屬于性能優(yōu)化問題，事務通?？梢哉＝Y束。危害最大的是第三類，這種被遺忘的事務會長時間占用系統(tǒng)資源（默認8小時），是不可接受的。在事務執(zhí)行出現(xiàn)問題時，需要顯式的rollback或commit來結束該事務，如果客戶端已經(jīng)殺死連接，無法控制事務，那么只能從服務端殺死該會話。

二、查找長事務

MySQL已提供了相關性能視圖幫助我們查詢活躍事務信息，通過performance_schema.events_transactions_current可以查詢所有當前事務的event，配合其他視圖即可定位長事務及其會話信息，主要用到的視圖如下：

performance_schema.events_transactions_current 查詢事務的線程ID，狀態(tài)，持續(xù)時間等信息
performance_schema.threads 查詢線程類型，用戶，IP地址等信息（MySQL中一個線程對應一個用戶會話）
sys.processlist 查詢線程當前的狀態(tài)，執(zhí)行的SQL等信息

各個視圖的關鍵字段，即要查詢的關鍵信息解釋如下：

performance_schema.events_transactions_current

thread_id, event_id 事務線程ID，事件ID，這是一個聯(lián)合主鍵，唯一定位一行記錄
state 事務的狀態(tài)，有ACTIVE, COMMITTED 或ROLLED BACK三種狀態(tài)，找長事務需要關注的是ACTIVE狀態(tài)
timer_start, timer_end, timer_wait 事務起始，結束（未結束則是當前）及持續(xù)時長，單位是皮秒(10的負12次方)，我們要關注的是timer_wait
isolation_level 事務的隔離級別

注：如果是MySQL8.0.16之后的版本，可以直接用format_pico_time()函數(shù)將timer_wait轉換成易讀的格式。

performance_schema.threads

thread_id 線程ID
type 線程類型，分為BACKGROUND(后臺線程)和FOREGROUND(用戶線程)，我們要關注的是用戶線程
processlist_id 用戶會話ID，只有用戶線程才有
processlist_user 會話用戶名，只有用戶線程才有
processlist_host 會話主機地址(IP)，只有用戶線程才有
processlist_db 會話當前操作的數(shù)據(jù)庫

sys.processlist

thd_id 線程ID
conn_id 會話ID
user 用戶信息，user@host格式
db 用戶操作數(shù)據(jù)庫
command 當前會話狀態(tài)
time 線程處于當前狀態(tài)的時長
current_statement 當前執(zhí)行SQL

了解了上面3個視圖提供的信息含義，我們可以很容易的找出當前哪些事務執(zhí)行時間過長，及這些事務當前在做什么：

select
t.thread_id 線程ID,
t.processlist_id 會話ID,
t.processlist_user 用戶,
t.processlist_host 用戶地址,
t.processlist_db 數(shù)據(jù)庫,
p.command 會話狀態(tài),
e.state 事務狀態(tài),
format_pico_time(e.timer_wait) 事務持續(xù)時長,
p.current_statement 執(zhí)行SQL
from performance_schema.events_transactions_current e
join performance_schema.threads t on t.thread_id=e.thread_id
left join sys.processlist p on p.thd_id=t.thread_id
where t.type='FOREGROUND'
and e.state='ACTIVE'
order by e.timer_wait desc;

這里提前開了2個會話，通過begin手動開啟事務，一個會話執(zhí)行select sleep(10000)，另一個執(zhí)行了一條普通的insert into語句。
第一個會話模擬了大事務/慢SQL的狀態(tài)，會話的狀態(tài)是Query，且執(zhí)行SQL有內容，表示事務在運行中
第二個會話模擬了事務未正常結束的狀態(tài)，會話的狀態(tài)是Sleep，執(zhí)行SQL為NULL，表示事務處于空閑狀態(tài)，這類事務需要重點關注
第三條記錄是這個查詢本身

定位到長事務后，分析長事務屬于哪一類，決定是否需要優(yōu)化事務或人工介入。例如上面第二個事務，如果判斷會話異常，可以通過殺死會話ID來結束該會話（事務）;