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 語句所設定。