MySQL Deadlocks in InnoDB

Deadlocks in InnoDB
死鎖是指不同的事務無法進行處理的情況，因為每個事務都持有對方需要的鎖。因為這兩個事務都在等待資源可用， 他們都沒有釋放過它所持有的鎖。

當事務鎖定多個表中的行（透過update或select ... for update等語句），但順序相反時會發生死鎖。當這樣的語句鎖定索引條目和間隙範圍時，每個事務由於時間問題獲得一些鎖定，而不獲得其他鎖定也會發生死鎖。

為了減少死鎖的可能性，請使用事務處理，而不是鎖定表語句；保持插入或更新資料的事務處理足夠小，以免長時間保持開放，當不同的事務更新多個表或大範圍的行時，請在每個事務中使用相同的操作順序（例如select ... for update,在select ... for update和update ... where語句所使用的列上建立索引。死鎖的可能性不受隔離級別的影響，因為隔離級別改變了讀取操作的行為，列鎖是由於寫操作引起的。

當啟用死鎖檢測（預設）併發生死鎖時，InnoDB會檢測觸發條件並回滾其中一個事務（受害者）。如果使用innodb_deadlock_detect配置選項禁用死鎖檢測，InnoDB依賴於innodb_lock_wait_timeout設定來在發生死鎖時回滾事務。因此，即使您的應用程式邏輯是正確的， 您仍然必須處理必須重試事務的情況。若要檢視InnoDB使用者事務中的最後一個死鎖，請使用show engine innodb status命令。如果頻繁的出現死鎖這就突出表時事務結構或應用程式錯誤處理存在問題，可以啟用innodb_print_all_deadlocks設定將所有死鎖的資訊列印到mysqld錯誤日誌。

一個InnoDB死鎖示例
下面的例子將演示當一個鎖請求將造成死鎖時可能出現的錯誤。這個例子將呼叫兩個客戶端A和B。

首先客戶端A建立一個表並插入一行記錄，然後開始一個事務。在這個事務中透過請求共享模式鎖的查詢語句來對行獲得共享鎖：

mysql> create table t(i int) engine=innodb;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.02 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where i=1 lock in share mode;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

客戶端B開始一個事務並試圖從表中刪除這行記錄：

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> delete from t where i=1;

刪除操作請求一個X鎖。因它它與客戶端A所持有的S鎖不相容，因此請求將會在鎖請求佇列中進行大排隊並且客戶端B會被阻塞

最後，客戶端A也試圖刪除這行記錄：

mysql> delete from t where i=1;

客戶端B出現錯誤資訊：

mysql> delete from t where i=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

這裡會發生死鎖，因為客戶端A需要一個X鎖來刪除該行。但是，不能授予該鎖定請求，因為客戶端B已經有了一個關於X鎖定的請求，並且正在等待客戶端A 來釋放它的S鎖。由於B事先請求使用X鎖，也不能將A持有的S鎖升級為X鎖。因此，InnoDB會為其中一個客戶端透過成錯誤資訊並釋放它所持有的鎖。客戶端將返回以下錯誤：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在這時，客戶端A可以被授予鎖請求並從表中刪除記錄

mysql> delete from t where i=1;
Query OK, 1 row affected (0.00 sec)

死鎖檢測與回滾
當死鎖檢測被啟用時（預設值），InnoDB會自動檢測事務的死鎖並回滾其中的一個事務或多個事務來打破死鎖。InnoDB嘗試選擇小事務來進行回滾，而事務的大小是由插入，更新或刪除的行記錄數來判斷的。

如果innodb_table_locks=1（預設）和autocommit=0，而且MySQL層知道行級鎖，那麼InnoDB就知道表鎖。否則，InnoDB無法檢測到由MySQL locktables語句設定的表鎖或由InnoDB以外的儲存引擎設定的鎖。透過設定innodb_lock_wait_timeout系統的值來解決這些情況。

當InnoDB執行完一個事務的回滾，由該事務所設定的鎖都會被釋放。但是，如果由於錯誤只導致一個SQL語句被回滾，則該語句設定的一些鎖可能會被保留。這是因為InnoDB以一種格式儲存行鎖，這樣之後就不能知道哪個語句設定了哪個鎖。

如果一個事務中的SELECT呼叫一個儲存函式，並且函式中的語句執行失敗，這個語句被回滾。之後，如果執行回滾，那麼整個事務將會被回滾。

如果InnoDB監控輸出的LATEST DETECTED DEADLOCK部分包含一條"TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACKFOLLOWING TRANSACTION"資訊，這指示等待列表中的事務資料已經超過了200的限制。當等待列表超過200個事務時就會當作死鎖並且試圖檢查等待列表的事務將被回滾。如果鎖定執行緒必須檢視超過1000000個鎖是由等待列表中事務所持有也會出現同樣的錯誤。

禁用死鎖檢測
在高併發系統中，當多執行緒等待同一個鎖時，死鎖檢測可能會導致速度減慢。這時禁用死鎖檢測並依賴innodb_lock_wait_timeout在出現死鎖時進行事務的回滾可能更有效。可以透過innodb_deadlock_detect配置選項來禁用死鎖檢測。

如何減少和處理死鎖
下面介紹如何組織資料庫操作來減少死鎖以及處理死鎖。

死鎖是事務型資料庫中一個非常經典的問題，但它們並不危險，除非頻繁出現死鎖且不能執行特定的事務。正常來說如果事務因為死鎖被回滾那麼你必須編寫你的應用程式來準備重新執行事務。

InnoDB使用自動行級鎖。那麼可能在只有插入或刪除單行記錄時也會出現死鎖。這是因為這些操作不是真正原子的，它們自動在被插入或刪除的行記錄所對應的索引記錄在設鎖。

您可以透過以下技術來處理死鎖，並減少其發生的可能性：
.的任何時候，可以執行show engine innodb status命令來判斷最近出現死鎖的情況。這可以幫助你最佳化程式來避免死鎖。

.如果經常出現死鎖警告引起關注，請透過啟用innodb_print_all_deadlocks配置選項來收集更廣泛的除錯資訊。關於每個死鎖的資訊，而不僅僅是被記錄在MySQL錯誤日誌中最新的一個,除錯後禁用此選項。

.如果事務由於死鎖而失敗，請隨時準備重新執行它。死鎖並不危險。再試一次。

.保持事務持續時間小和短，使它們不容易發生碰撞。

.在進行一組相關更改後立即提交事務，使它們不容易發生碰撞。特別是，不要讓一個互動式mysql會話長期開啟一個未已提交的事務。

.如果使用鎖定讀取(select ... for update 或select ... lock in share mode)，那麼嘗試使用低隔離級別比如read committed。

.當修改事務中的多個表或同一表中的不同行集時，每次都按照一致的順序執行這些操作。然後事務形成定義良好的佇列且不會死鎖。例如，將資料庫操作組織成應用程式中的函式，或呼叫儲存例程，而不是在不同的地方編碼多個插入、更新和刪除語句。

.向表中新增選擇良好的索引。然後，您的查詢需要掃描更少的索引記錄，從而設定更少的鎖。使用explain select來判斷MySQL伺服器認為哪些索引最適合您的查詢。

.使用更少的鎖定。如果您可以允許select語句從舊快照返回資料，請不要向語句中新增for update或lock in share mode子句。使用read committed讀提交的隔離級別是不錯的選擇， 因為同一事務中的每個一致性讀取都是從它自己的新快照中讀取的。

.如果沒有其他幫助，請使用表級鎖序列化你的事務。對事務表使用lock tables，如InnoDB表,使用set autocommit=0開始事務（而不是start transaction），接著使用lock tables，並且在顯式提交事務之前不要unlock tables。例如，如果您需要寫入表t1並從表t2中讀取，可以執行如下操作：

set autocommit=0;
lock tables t1 write,t2 read,...;
... do something with tables t1 and t2 here ..
commit;
unlock tables;

表級鎖阻止對錶的併發更新，以犧牲繁忙系統的響應能力為代價避免死鎖。

.另一種序列化事務的方法是建立一個僅包含一行的輔助“訊號量”表。在訪問其他表之前，讓每個事務都要更新該行。透過這種方式，所有事務以一種序列的方式發生。請注意，InnoDB即時死鎖檢測演算法在這種情況下也可以工作，因為序列化鎖是一個行級鎖。使用MySQL表級鎖時，必須使用超時方法來解決死鎖。