MySQL死鎖使用詳解及檢測和避免方法

前言

上一篇博客我們知道的Mysql事務的隔離機制和實現,以及鎖的詳細解析

鏈接: MySQL臟讀幻讀不可重復讀及事務的隔離級別和MVCC、LBCC實現

在我們使用鎖的時候,有一個問題是需要註意和避免的,我們知道,排它鎖有互斥的特性。一個事務或者說一個線程持有鎖的時候,會阻止其他的線程獲取鎖,這個時候會造成阻塞等待,如果循環等待,會有可能造成死鎖。

這個問題我們需要從幾個方面來分析,一個是鎖為什麼不釋放,第二個是被阻塞瞭怎麼辦,第三個死鎖是怎麼發生的,怎麼避免。

鎖的釋放與阻塞

回顧:鎖什麼時候釋放?
事務結束(commit,rollback)﹔
客戶端連接斷開。

如果一個事務一直未釋放鎖,其他事務會被阻塞多久?會不會永遠等待下去?
如果是,在並發訪問比較高的情況下,如果大量事務因無法立即獲得所需的鎖而掛起,會占用大量計算機資源,造成嚴重性能問題,甚至拖跨數據庫。

線上怕不怕這個錯?

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL有一個參數來控制獲取鎖的等待時間,默認是50秒。

show VARIABLES like "innodb_lock_wait_timeout";

對於死鎖,是無論等多久都不能獲取到鎖的,這種情況,也需要等待50秒鐘嗎?那不是白白浪費瞭50秒鐘的時間嗎?

死鎖的發生和檢測

演示一下,開兩個會話:

方便對時間線的提現,這裡用圖片,有興趣的可以跟著模仿一下

栗子一:

栗子二:

在第一個事務中,檢測到瞭死鎖,馬上退出瞭,第二個事務獲得瞭鎖,不需要等待50秒:

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

為什麼可以直接檢測到呢?是因為死鎖的發生需要滿足一定的條件,對於我們程序員來說,有明確的條件,意味著能判定,所以在發生死鎖時,InnoDB一般都能通過算法(wait-for graph)自動檢測到。

那麼死鎖需要滿足什麼條件?死鎖的產生條件,因為鎖本身是互斥的:

  • (1)同一時刻隻能有一個事務持有這把鎖;
  • (2)其他的事務需要在這個事務釋放鎖之後才能獲取鎖,而不可以強行剝奪;
  • (3)當多個事務形成等待環路的時候,即發生死鎖。

理發店有兩個總監。一個負責剪頭的Tony老師,一個負責洗頭的Kelvin老師。Tony老師不能同時給兩個人剪頭,這個就叫互斥

Tony在給別人在剪頭的時候,你不能讓他停下來幫你剪頭,這個叫不能強行剝奪
如果Tony的客戶對Kelvin說:你不幫我洗頭我怎麼剪頭? Kelvin 的客戶對Tony說:你不幫我剪頭我怎麼洗頭?這個就叫形成等待環路
實際上,發生死鎖的情況非常多,但是都滿足以上3個條件。
這個也是表鎖是不會發生死鎖的原因,因為表鎖的資源都是一次性獲取的

如果鎖一直沒有釋放,就有可能造成大量阻塞或者發生死鎖,造成系統吞吐量下降,這時候就要查看是哪些事務持有瞭鎖。

查看鎖信息(日志)

首先,SHow STATUS命令中,包括瞭一些行鎖的信息:

show status like 'innodb_row_lock_%';

lnnodb_row_lock_current_waits:當前正在等待鎖定的數量;
lnnodb_row_lock_time :從系統啟動到現在鎖定的總時間長度,單位ms;
Innodb_row_lock_time_avg :每次等待所花平均時間;
Innodb_row_lock_time_max:從系統啟動到現在等待最長的一次所花的時間;
lnnodb_row_lock_waits :從系統啟動到現在總共等待的次數。

SHOW命令是一個概要信息。InnoDB還提供瞭三張表來分析事務與鎖的情況:

select * from information_schema.INNODB_TRX; --當前運行的所有事務﹐還有具體的語句

select* from information_schema.INNODB_LOCKS; --當前出現的鎖

select * from information_schema.INNODB_LOCK_WAITS; --鎖等待的對應關系

更加詳細的鎖信息,開啟標準監控和鎖監控:

額外的監控肯定會消耗額外的性能

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

通過分析鎖日志,找出持有鎖的事務之後呢?
如果一個事務長時間持有鎖不釋放,可以kill事務對應的線程ID,也就是INNODB_TRX表中的trx_mysql_thread_id,例如執行kill 4,kill 7, kill 8。
當然,死鎖的問題不能每次都靠kill線程來解決,這是治標不治本的行為。我們應該盡量在應用端,也就是在編碼的過程中避免。
有哪些可以避免死鎖的方法呢?

死鎖的避免

  • 1、在程序中,操作多張表時,盡量以相同的順序來訪問(避免形成等待環路)
  • 2、批量操作單張表數據的時候,先對數據進行排序(避免形成等待環路);
  • 3、申請足夠級別的鎖,如果要操作數據,就申請排它鎖;
  • 4、盡量使用索引訪問數據,避免沒有where條件的操作,避免鎖表;
  • 5、如果可以,大事務化成小事務;
  • 6、使用等值查詢而不是范圍查詢查詢數據,命中記錄,避免間隙鎖對並發的影響。

到此這篇關於MySQL死鎖使用詳解及檢測和避免方法的文章就介紹到這瞭,更多相關MySQL死鎖 內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: