MySQL InnoDB如何應付死鎖

死鎖是事務處理型數據庫系統的一個經典問題，但是它們并不是很危險的， 除非它們如此地頻繁以至于你根本處理不了幾個事務。 當因死鎖而產生了回滾時，你通常可以在你的應用程序中重新發出一個事務即可。

InnoDB 使用自動地行級鎖定。你可能恰好在插入或刪除單一一條記錄時產生死鎖。 這是因為這些操作并不是真正“原子(atomic)”級的：他們會自動地在鎖定 inserted/deleted 行的索引記錄(可能有幾個)。


可以通過下面所示的技巧來應付死鎖或減少死鎖的次數： 

在 MySQL >=3.23.52 和 >= 4.0.3 的版本中使用 SHOW INNODB STATUS 來確定引起最后一個死鎖的原因。這可以幫助你調整你的應用程序來避免死鎖。 
總是準備在因死鎖而發生錯誤時重新發出一個事務。死鎖并不危險。僅僅只需重試一遍。 
經常提交你的事務。小的事務有較少的碰撞可能。 
如果使用鎖定讀取 SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE，盡量使用較低的隔離級 READ COMMITTED。 
以一個固定秩序(a fixed order)訪問你的表和記錄。這樣事務將形成一個較精細的隊列，而避免死鎖。 
為你的表添加合適的索引。那么你的查詢只需要掃描較少的索引，因而設置較少的鎖定。使用 EXPLAIN SELECT 來確定 MySQL 為你的查詢挑選的適當的索引。 
盡量少用鎖定：如果可以通過一個 SELECT 在一個較老的數據快照中獲得所需數據，就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在這時使用 READ COMMITTED 隔離級是較好的主意，因為在同一個事務中的每個 consistent read 只讀取它最先確定的數據快照。 
如果仍然沒有什么補救效果，使用表級鎖定連載你的事務(serialize transactions)：LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES。表級鎖定可以使你的事務形成精細的隊列。注意 LOCK TABLES 隱含地啟動一個事務，就如同命令 BEGIN，UNLOCK TABLES 如同 COMMIT 一樣隱含地結束一個事務。 
連載事務(serialize transactions)的另一個解決辦法就是建立一個僅有一行記錄的輔助“信號量(semaphore)” 表。每一個事務在訪問其它表之前均更新這個記錄。通過這種方式所有的事務將持續執行。注意同時 InnoDB 實時死鎖檢測算法也在工作著，因為這個持續鎖定(serializing lock)是一個行鎖定。在 MySQL 中對于表級鎖定我們必須采取超時方式。 


死鎖檢測與回滾
InnoDB 會自動檢測一個事務的死鎖并回滾一個或多個事務來防止死鎖。從 4.0.5 版開始，InnoDB 將設法提取小的事務來進行回滾。一個事務的大小由它所插入(insert)、更新(update)和刪除(delete)的數據行數決定。 Previous to 4.0.5, InnoDB always rolled back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions.

InnoDB 不能檢測出由 MySQL 的 LOCK TABLES 語句引起的死鎖，或其它的表類型中的鎖定所引起的死鎖。你不得不通過在 my.cnf 中設置 innodb_lock_wait_timeout 參數來解決這些情形。

當 InnoDB 執行一個事務完整的回滾，這個事務所有所加的鎖將被釋放。然而，如果只一句的 SQL 語句因結果返回錯誤而進行回滾的，由這條 SQL 語句所設置的鎖定可能會被保持。這是因為 InnoDB r的行鎖存儲格式無法知道鎖定是由哪個 SQL 語句所設置。