排查鎖等待步驟

最近線上碰到了幾次mysql鎖等待的問題，一個事務線程長期占用鎖資源，導致其他事務無法獲取到鎖，為了快速解決問題，我們把線程kill掉了，但后面就定位不到具體的問題了，這里我總結(jié)整理一下我的方案。

如果事務剛好還處于等待狀態(tài)

• 使用以下查詢查看具體的鎖等待情況，包括哪個事務持有了鎖，哪個事務在等待哪個鎖：

SELECT * FROM sys.innodb_lock_waits;

這里可以看到當前 鎖的類型，等待鎖的事務，等待時間

同時我們可以看到等待的線程id，正在執(zhí)行的sql，但這個sql不全，我們可以拿這個線程id去
information_schema.PROCESSLIST 查到完整sql

select * from information_schema.PROCESSLIST WHERE ID = 12;

這里我們還可以定位到執(zhí)行sql的host和端口

但這些不是我們主要關(guān)注的，等待的事務超過時間會回滾，我們需要找到阻塞線程做了什么操作，還是回到第一步，找到阻塞線程id，但我們無法定位到具體sql，因為這里只會把正在執(zhí)行的sql查出來，所以如何找到阻塞線程執(zhí)行過的sql成了關(guān)鍵。

• 找到阻塞線程執(zhí)行過的sql

第一種：

前提是要打開 performance_schema，可以用下面sql看一下

SHOW VARIABLES LIKE 'performance_schema';

但注意，如果是OFF，改成ON的話需要重啟實例，一般線上環(huán)境是不會這么操作，而且打開后，因為要多記錄一些日志信息，會影響整體性能，也不推薦打開。

用阿里云的RDS，修改參數(shù)時也會提示

查找阻塞線程的詳細信息：

SELECT * FROM performance_schema.threads WHERE processlist_id = 11;

查看引起阻塞的SQL語句：

SELECT * FROM performance_schema.events_statements_history WHERE thread_id = 51;

這樣我們就定位到了該線程執(zhí)行過的sql，再通過information_schema.PROCESSLIST 找到host和port，去我們具體的服務定位問題。

第二種方案：上面也提到performance_schema這個參數(shù)我們一般不會打開，所以我們只能通過事務id去binlog找了

1. 查詢binlog文件

首先，我們需要找到包含我們需要查詢的事務id的binlog文件?？梢允褂靡韵旅畈榭串斍罢谏傻腷inlog文件

SHOW MASTER STATUS;

2.使用mysqlbinlog查找事務ID:接下來，可以使用mysqlbinlog命令配合–start-position和–stop-position參數(shù)，逐步定位到含有特定事務ID的binlog記錄。但是直接通過事務ID查找可能不直接支持，因為通常事務ID并不直接暴露為查詢條件。一個更通用的方式是結(jié)合事務的時間戳或者其他上下文信息進行搜索。

如果你確切知道某個事務大致發(fā)生的時間，可以利用時間篩選，例如：

mysqlbinlog --start-datetime="2024-07-31 14:00:00" mysql-bin.000001 | grep "事務相關(guān)的關(guān)鍵詞或ID"

3.將binlog解析后的SQL語句輸出到一個文本文件中

mysqlbinlog mysql-bin.000001 > output.sql
mysqlbinlog --base64-output=DECODE-ROWS --verbose mysql-bin.000001 > output.sql

這樣就可以從輸出文件中找到我們事務的信息了

鎖等待事務已經(jīng)回滾

這種時候只能看看現(xiàn)在RUNNING的線程，看看是否像我們這種情況有事務長時間占用，通過事務id找到具體sql

SELECT * FROM information_schema.INNODB_TRX;

到此這篇關(guān)于mysql排查鎖等待的解決方法的文章就介紹到這了,更多相關(guān)mysql排查鎖等待內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論