排查鎖等待步驟

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

如果事務剛好還處于等待狀態(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和端口

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

• 找到阻塞線程執(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并不直接暴露為查詢條件。一個更通用的方式是結合事務的時間戳或者其他上下文信息進行搜索。

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

mysqlbinlog --start-datetime="2024-07-31 14:00:00" mysql-bin.000001 | grep "事務相關的關鍵詞或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;

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

最新評論