文章目錄

• 生猛干貨
• 版本信息
• MySQL 行鎖分析
• MySQL死鎖演示
• 排查過程
• 查看近期死鎖日志信息
• 查詢鎖等待命令及kill 鎖
• 優化建議
• 搞定MySQL

生猛干貨

帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試

---

版本信息

mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.28 | +-----------+ 1 row in setmysql>

---

MySQL 行鎖分析

mysql> show status like'innodb_row_lock%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 222821 | | Innodb_row_lock_time_avg | 27852 | | Innodb_row_lock_time_max | 51017 | | Innodb_row_lock_waits | 8 | +-------------------------------+--------+ 5 rows in setmysql>

變量說明：

• Innodb_row_lock_current_waits 當前正在等待鎖定的數量 單位毫秒
• Innodb_row_lock_time 從系統啟動到現在鎖定總時間長度 單位毫秒
• Innodb_row_lock_time_avg 每次等待所花平均時間 單位毫秒
• Innodb_row_lock_time_max 從系統啟動到現在等待最長的一次所花時間 單位毫秒
• Innodb_row_lock_waits 系統啟動后到現在總共等待的次數

重點關注 ： Innodb_row_lock_time_avg 、Innodb_row_lock_waits 、Innodb_row_lock_time

---

MySQL死鎖演示

事務隔離級別， 默認 可重復讀

mysql> show variables like '%tx_isolation%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in setmysql>

【操作步驟】

session1session2

begin 模擬開啟事務
select * from art_info where id=1 for update;
	begin 模擬開啟事務
	select * from account where id=2 for update;
select * from art_info where id=2 for update; ---->一直等待
	select * from art_info where id=1 for update; —> Deadlock found when trying to get lock; try restarting transaction

大多數情況mysql可以自動檢測死鎖并回滾產生死鎖的那個事務，但是有些情況mysql沒法自動檢測死鎖

---

排查過程

【模擬鎖等待 】

session1session2

begin 模擬開啟事務
select * from art_info where id=1 for update;
	begin 模擬開啟事務
	select * from account where id=2 for update;
select * from account where id=2 for update;

mysql> select * from art_info where id =2 for update ; 1205 - Lock wait timeout exceeded; try restarting transaction

---

-- 查看事務 select * from information_schema.INNODB_TRX; -- 查看鎖 select * from information_schema.INNODB_LOCKS; -- 查看鎖等待 select * from information_schema.INNODB_LOCK_WAITS;-- 鎖釋放 information_schema.INNODB_TRX 查詢 trx_mysql_thread_id 然后去 kill 對應的value kill trx_mysql_thread_id

來吧 ，用上面的SQL查吧

---

查看近期死鎖日志信息

show engine innodb status \G;

---

查詢鎖等待命令及kill 鎖

-- 查看事務 select * from information_schema.INNODB_TRX; -- 查看鎖 select * from information_schema.INNODB_LOCKS; -- 查看鎖等待 select * from information_schema.INNODB_LOCK_WAITS;-- 鎖釋放 information_schema.INNODB_TRX 查詢 trx_mysql_thread_id 然后去 kill 對應的value kill trx_mysql_thread_id

鎖等待有自己的超時時間，超過后一般都會自動釋放

mysql> select * from art_info where id =2 for update ; 1205 - Lock wait timeout exceeded; try restarting transaction

---

優化建議

• 盡可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖
• 合理設計索引，盡量縮小鎖的范圍
• 盡可能減少檢索條件范圍，避免間隙鎖
• 盡量控制事務大小，減少鎖定資源量和時間長度，涉及事務加鎖的sql盡量放在事務最后執行
• 盡可能低級別事務隔離

---

搞定MySQL

總結

以上是生活随笔為你收集整理的MySQL - 锁等待及死锁初探的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。