前言：

昨晚我正在床上睡得著著的，突然來了一條短信。

什么？線上的訂單無法取消！

我趕緊登錄線上系統(tǒng)，查看業(yè)務(wù)日志。

發(fā)現(xiàn)有MySQL鎖超時的錯誤日志。

不用想，肯定有另一個事務(wù)正在修改這條訂單，持有這條訂單的鎖。

導(dǎo)致當(dāng)前事務(wù)獲取不到鎖，一直等待，直到超過鎖超時時間，然后報錯。

既然問題已經(jīng)清楚了，接下來就輪到怎么排查一下到底是哪個事務(wù)正在持有這條訂單的鎖。

好在MySQL提供了豐富的工具，幫助我們排查鎖競爭問題。

現(xiàn)場復(fù)現(xiàn)一個這個問題：

創(chuàng)建一張用戶表，造點數(shù)據(jù)：

CREATE TABLE `user` (
 ?`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 ?`name` varchar(50) NOT NULL DEFAULT '' COMMENT '姓名',
 ?PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

事務(wù)1，更新id=1的用戶姓名，不提交事務(wù)：

begin;
update user set name='一燈' where id=1;

事務(wù)2，刪除id=1的數(shù)據(jù)，這時候會產(chǎn)生鎖等待：

begin;
delete from user where id=1;

接下來，我們就通過MySQL提供的鎖競爭統(tǒng)計表，排查一下鎖等待問題：

先查一下鎖等待情況：

select * from information_schema.innodb_lock_waits;

可以看到有一個鎖等待的事務(wù)。

然后再查一下正在競爭的鎖有哪些？

select * from information_schema.innodb_locks;

可以看到，MySQL統(tǒng)計的非常詳細：

lock_trx_id 表示事務(wù)ID

lock_mode 表示排它鎖還是共享鎖

lock_type 表示鎖定的記錄，還是范圍

lock_table 鎖的表名

lock_index 鎖定的是主鍵索引

再查一下正在執(zhí)行的事務(wù)有哪些？

select * from information_schema.innodb_trx; 

可以清楚的看到正在執(zhí)行的事務(wù)有兩個，一個狀態(tài)是鎖等待（LOCK WAIT），正在執(zhí)行的SQL也打印出來了：

delete from user where id=1;

正是事務(wù)2的刪除語句。

不用問，第二條，顯示正在運行狀態(tài)（RUNNING）的事務(wù)就是正在持有鎖的事務(wù)1，MySQL線程id（trx_mysql_thread_id）是193。

我們用MySQL線程id查一下事務(wù)線程id：

select * from performance_schema.threads where processlist_id=193;

找到對應(yīng)的事務(wù)線程id是218，然后再找一下這個線程正在執(zhí)行的SQL語句：

select THREAD_ID,CURRENT_SCHEMA,SQL_TEXT 
from performance_schema.events_statements_current 
where thread_id=218;

可以清楚的看到這個線程正在執(zhí)行的SQL語句就是事務(wù)1的update語句。

持有鎖的SQL語句找到了，接下來再去找對應(yīng)的業(yè)務(wù)代碼也就輕而易舉了。

以上是基于MySQL5.7版本，在MySQL8.0版本中有些命令已經(jīng)刪除了，替換成了其他命令，下篇文章再講一下MySQL8.0怎么定位MySQL鎖超時問題。

到此這篇關(guān)于一文教你學(xué)會定位線上MySQL鎖超時問題的文章就介紹到這了,更多相關(guān)MySQL鎖超時內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論