Mysql 死鎖

在高并發情況下，不同僚務持有資源而等待另一個資源鎖時，而其他事務恰好相反，出現了循環等待，即出現了死鎖。

一般的，最常見且最易了解的死鎖形式是：

事務T1持有A的鎖->等待B的鎖

事務T2持有B的鎖->等待A的鎖

這樣的形式即發生了死鎖，後續會介紹死鎖避免及死鎖解決，現在來看看對同一個行操作，mysql是如何出現死鎖了。

首先，T1進行

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id FROM pet where id=10 for share;
+----+
| id |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
           

T2進行

START TRANSACTION;
DELETE from pet where id=10;
##會提示
DELETE from pet where id=10;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
           

因為T1不是自動送出事務，查詢的時候，hold持有共享鎖，而此時T2開啟事務并試圖進行delete拿x鎖，但處于一直等待中，當最後等待鎖逾時而自動放棄（innodb-lock-wait-timeout 預設是50s）。

做一下調整，如果T2還在等待T1釋放鎖時，T1進行同樣的删除操作

T1：

mysql> DELETE from pet where id=10;
Query OK, 1 row affected (0.00 sec)
           

此時 T2：

DELETE from pet where id=10;
[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction
           

過程：如果T1開啟事務，先拿取了記錄R的S讀鎖，此時T2來删除R需要用X鎖，因T1占有S鎖不得不等待，在此等待過程中，T1又進行了删除操作又嘗試對R記錄追加X鎖，最後導緻T2發生死鎖退出。

死鎖原因：

此處發生死鎖，因為用戶端T1需要X鎖來删除該行。 但是，無法授予該鎖請求，因為用戶端T2已經有一個X鎖請求并且正在等待用戶端T1釋放其S鎖。 由于T2先前對X鎖的請求，T1保持的S鎖也不能更新到X鎖。 是以，InnoDB會為其中一個用戶端生成錯誤并釋放鎖。

啟用死鎖檢測（預設設定）後，InnoDB會自動檢測事務死鎖并復原事務或事務以打破死鎖。 InnoDB嘗試選擇要復原的小事務，其中事務的大小由插入，更新或删除的行數決定。

innodb-deadlock-detect[={OFF|ON}] 死鎖檢測，預設值為ON

如果innodb_table_locks = 1（預設值）和autocommit = 0，InnoDB知道表鎖，并且它上面的MySQL層知道行級鎖。否則，InnoDB無法檢測到由MySQL LOCK TABLES語句設定的表鎖或由InnoDB以外的存儲引擎設定的鎖的死鎖。通過設定innodb_lock_wait_timeout系統變量的值來解決這些情況。

當InnoDB執行事務的完全復原時，将釋放由事務設定的所有鎖。但是，如果由于錯誤而僅復原單個SQL語句，則可能會保留由該語句設定的某些鎖定。發生這種情況是因為InnoDB以一種格式存儲行鎖，以至于後來無法知道哪個鎖由哪個語句設定。

如果SELECT在事務中調用存儲的函數，并且函數中的語句失敗，則該語句将復原。此外，如果在此之後執行ROLLBACK，則整個事務復原。

禁用死鎖檢測

在高并發系統上，當許多線程等待同一個鎖時，死鎖檢測會導緻速度減慢。有時，在發生死鎖時，禁用死鎖檢測并依賴innodb_lock_wait_timeout設定進行事務復原可能更有效。可以使用innodb_deadlock_detect配置選項禁用死鎖檢測。

使用以下技術處理死鎖并降低其發生的可能性：

●在任何時候，發出SHOW ENGINE INNODB STATUS指令以确定最近死鎖的原因。這可以幫助您調整應用程式以避免死鎖。

●如果頻繁出現死鎖警告，請通過啟用innodb_print_all_deadlocks配置選項來收集更多的調試資訊。有關每個死鎖的資訊，而不僅僅是最新的死鎖，都記錄在MySQL錯誤日志中。完成調試後禁用此選項。

●如果由于死鎖而失敗，請始終準備重新釋出事務。死鎖并不危險。再試一次。

●保持交易持續時間短且不易發生，以減少交易。

●在進行一組相關更改後立即送出事務，以使它們不易發生沖突。特别是，不要使用未送出的事務使互動式mysql會話長時間保持打開狀态。

●如果使用鎖定讀取（SELECT ... FOR UPDATE或SELECT ... FOR SHARE），請嘗試使用較低的隔離級别，例如READ COMMITTED。

●在事務中修改多個表或同一個表中的不同行集時，每次都以一緻的順序執行這些操作。然後事務形成定義良好的隊列，不會死鎖。例如，将資料庫操作組織到應用程式中的函數中，或調用存儲的過程，而不是在不同的位置編寫多個類似的INSERT，UPDATE和DELETE語句序列。

●在表中添加精心選擇的索引。然後，您的查詢需要掃描更少的索引記錄，進而設定更少的鎖。使用EXPLAIN SELECT确定MySQL伺服器認為哪些索引最适合您的查詢。

●使用較少的鎖。如果您能夠允許SELECT從舊快照傳回資料，請不要向其添加FOR UPDATE或FOR SHARE子句。在這裡使用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表級鎖定時，必須使用逾時方法來解決死鎖。

補：

你可以在my.ini配置檔案中，使用innodb_read_io_threads和innodb_write_io_threads配置參數來配置為資料頁讀寫I/O提供服務的背景線程的數量。這些參數表示用于讀寫請求的背景線程的數量。