mysql死鎖的原因和處理方法是什麼

當多個事務同時持有和請求同一資源上的鎖而產生循環依賴的時候就產生了死鎖，解決方法為：1、用更低的隔離級別；2、以固定的順序存取你的表和行；3、添加精心選定的索引到表中；4、使用更少的鎖定。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

當多個事務同時持有和請求同一資源上的鎖而產生循環依賴的時候就產生了死鎖。死鎖發生在事務試圖以不同的順序鎖定資源。以StockPrice表上的兩個交易為例：

交易1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

交易#2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET;
COMMIT;

如果不走運的話，每個交易都可以執行完第一個語句，並在過程中鎖住資源。然後每個事務都試圖去執行第二行語句，當時卻發現它被鎖住了。兩個事務將永遠的等待對方完成，除非有其他原因打斷死鎖。

為了解決這個問題，資料庫實作了各種死鎖探查和逾時機制。像InnoDB這樣複雜的儲存引擎會提示循環依賴並且立即傳回錯誤。否則死鎖將會導致查詢非常緩慢。其他一些不好的做法是等待超時後放棄。目前InnoDB處理死鎖的方式是回滾持有最少排他行級鎖的事務。 （幾乎最簡單的回滾的參考指標）

鎖定的行為是順序是儲存引擎決定的。因此，一些儲存引擎可能會在特定的操作順序下發生死鎖，其他的可能沒有。死鎖有兩種：有些是因為實際資料衝突而無法避免，有些是因為儲存引擎的工作方式產生。

只有部分或完全回滾其中的一個交易才可能打破死鎖。死鎖是事務系統中客觀存在的事實，你的應該在設計上必須考慮處理死鎖。有些業務系統可以從頭重試事務。

如何處理死鎖
死鎖是事務型資料庫典型的問題，但是除非它們頻繁出現以至於你更本來不能運行某個事務，它們一般是不危險的。正常地，你必須編寫你的應用程式使得它們總是準備如果因為死鎖而回滾一個事務就重新發出一個事務。

InnoDB使用自動行級鎖定。即使在只插入或刪除單一行的交易的情況下，你可以遇到死鎖。這是因為這些操作不是真正的“極小的”，它們自動對插入或刪除的行的（可能是數個）索引記錄設定鎖定。

你可以用下列技術對付死鎖減少它們發生的可能性：

用Use SHOW INNODB STATUS來確定最後一個死鎖的原因。這樣可以幫助你調節應用程式來避免死鎖。

總是準備著重新發出事務，如果它因為死鎖而失敗了。死鎖不危險，再試一次。

經常提交你的事務。小事務更少傾向於衝突。

如果你正在使用鎖定讀，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），試著用更低的隔離級別，例如READ COMMITTED。

以固定的順序存取你的表格和行。則事務形成良好定義的查詢並且沒有死鎖。

將精心選定的索引新增到你的表。則你的查詢需要掃描更少的索引記錄並且因此設定更少的鎖定。使用EXPLAIN SELECT來決定對於你的查詢,MySQL認為哪個索引是最適當的。

使用更少的鎖定。如果你可以接受允許一個SELECT從一個舊的快照返回數據，不要給它添加FOR UPDATE或LOCK IN SHARE MODE子句。這裡使用READ COMMITTED隔離等級是比較好的，因為每個在同一事務裡的持續讀取從它自己新鮮的快照讀取。

如果沒有別的有幫助的了，用表級鎖定係列化你的事務。用LOCK TABLES對事務型表（如InnoDB)的正確方法是設定AUTOCOMMIT = 0 並且不呼叫UNLOCK TABLES直到你明確地提交了事務。例如，如果你需要寫表t1並從表t讀，你可以按如下做：

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

[do something with tables t1 and t2 here];

COMMIT;

UNLOCK TABLES;

表級鎖定使得你的事務很好地排隊，並且死鎖被避免了。

領一個系列化交易的方法是建立一個輔助的「semaphore」 表，它只包含一個單行。讓每個事務在訪問其它表之前更新那個行。以這種方式，所有事務以序列的方式發生。請注意，InnoDB即時死鎖偵測演算法也能在這種情況下起租用，因為系列化鎖定是行級鎖定。超時方法，用MySQL表級鎖定，必須用來解決死鎖。

在應用程式中使用LOCK TABLES指令，如果AUTOCOMMIT=1,MySQL不設定InnoDB表鎖定。

相關推薦：《mysql教學》

以上是mysql死鎖的原因和處理方法是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！