mysql排查鎖等待的解決方法
排查鎖等待步驟
最近線上碰到了幾次mysql鎖等待的問題,一個(gè)事務(wù)線程長期占用鎖資源,導(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)橥ǔJ聞?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ù)長時(shí)間占用,通過事務(wù)id找到具體sql
SELECT * FROM information_schema.INNODB_TRX;
到此這篇關(guān)于mysql排查鎖等待的解決方法的文章就介紹到這了,更多相關(guān)mysql排查鎖等待內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySql安裝與配置方法(MySQL添加用戶、刪除用戶與授權(quán))
這篇文章主要介紹了MySql安裝與配置方法(MySQL添加用戶、刪除用戶與授權(quán))的相關(guān)資料,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2016-08-08MySQL拋出Incorrect string value異常分析
從上至下統(tǒng)一用上UTF-8就高枕無憂,今天還是遇到字符的異常,本文將介紹解決方法2012-11-11Mysql通過ibd文件恢復(fù)數(shù)據(jù)的詳細(xì)步驟
mysql在使用的過程中,難免遇到數(shù)據(jù)庫表誤操作,下面這篇文章主要給大家介紹了關(guān)于Mysql通過ibd文件恢復(fù)數(shù)據(jù)的詳細(xì)步驟,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06MySQL命令行中給表添加一個(gè)字段(字段名、是否為空、默認(rèn)值)
本文介紹MySQL數(shù)據(jù)庫使用SQL語句來在現(xiàn)有的表中,添加一個(gè)新的字段,包括設(shè)置字段的是否為空、默認(rèn)值等2016-04-04關(guān)于mysql 8.x 中insert ignore的性能問題
這篇文章主要介紹了關(guān)于mysql 8.x 中insert ignore的性能問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。2022-08-08