一文教你學會定位線上MySQL鎖超時問題

2022-08-16 14:06:20

前言：

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

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

我趕緊登入線上系統，檢視業務紀錄檔。

發現有MySQL鎖超時的錯誤紀錄檔。

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

導致當前事務獲取不到鎖，一直等待，直到超過鎖超時時間，然後報錯。

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

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

現場復現一個這個問題：

建立一張使用者表，造點資料：

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;

事務1，更新id=1的使用者姓名，不提交事務：

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

事務2，刪除id=1的資料，這時候會產生鎖等待：

begin;
delete from user where id=1;

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

先查一下鎖等待情況：

select * from information_schema.innodb_lock_waits;

可以看到有一個鎖等待的事務。

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

select * from information_schema.innodb_locks;

可以看到，MySQL統計的非常詳細：

lock_trx_id 表示事務ID

lock_mode 表示排它鎖還是共用鎖

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

lock_table 鎖的表名

lock_index 鎖定的是主鍵索引

再查一下正在執行的事務有哪些？

select * from information_schema.innodb_trx;

可以清楚的看到正在執行的事務有兩個，一個狀態是鎖等待（LOCK WAIT），正在執行的SQL也列印出來了：

delete from user where id=1;

正是事務2的刪除語句。

不用問，第二條，顯示正在執行狀態（RUNNING）的事務就是正在持有鎖的事務1，MySQL執行緒id（trx_mysql_thread_id）是193。

我們用MySQL執行緒id查一下事務執行緒id：

select * from performance_schema.threads where processlist_id=193;

找到對應的事務執行緒id是218，然後再找一下這個執行緒正在執行的SQL語句：

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

可以清楚的看到這個執行緒正在執行的SQL語句就是事務1的update語句。

持有鎖的SQL語句找到了，接下來再去找對應的業務程式碼也就輕而易舉了。

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

到此這篇關於一文教你學會定位線上MySQL鎖超時問題的文章就介紹到這了,更多相關MySQL鎖超時內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

一文教你學會定位線上MySQL鎖超時問題

熱門文章