排查鎖等待步驟

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

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

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

SELECT * FROM sys.innodb_lock_waits;

這里可以看到當(dāng)前 鎖的類型，等待鎖的事務(wù)，等待時(shí)間

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

select * from information_schema.PROCESSLIST WHERE ID = 12;

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

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

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

第一種：

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

SHOW VARIABLES LIKE 'performance_schema';

但注意，如果是OFF，改成ON的話需要重啟實(shí)例，一般線上環(huán)境是不會(huì)這么操作，而且打開后，因?yàn)橐嘤涗浺恍┤罩拘畔?，?huì)影響整體性能，也不推薦打開。

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

查找阻塞線程的詳細(xì)信息：

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，去我們具體的服務(wù)定位問題。

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

1. 查詢binlog文件

首先，我們需要找到包含我們需要查詢的事務(wù)id的binlog文件?？梢允褂靡韵旅畈榭串?dāng)前正在生成的binlog文件

SHOW MASTER STATUS;

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

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

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

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

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

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

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

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

SELECT * FROM information_schema.INNODB_TRX;

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

最新評(píng)論