一次徹底講清如何處理mysql 的死鎖問題

lgx211發表於2024-10-17
MySql

MySQL 死鎖 是指兩個或多個事務互相等待對方持有的鎖，從而導致所有事務都無法繼續執行的現象。在 InnoDB 儲存引擎中，死鎖是透過鎖機制產生的，特別是在併發較高、業務邏輯複雜的情況下，更容易發生死鎖。

一、MySQL 死鎖的成因

MySQL 的死鎖一般發生在 行級鎖 上。常見的死鎖成因包括：

  1. 事務 A 和事務 B 持有互相需要的鎖：事務 A 鎖住了記錄 1，事務 B 鎖住了記錄 2，事務 A 嘗試獲取記錄 2 的鎖，而事務 B 試圖獲取記錄 1 的鎖，造成了死鎖。
  2. 不同順序的鎖定：兩個事務對同一組資源請求加鎖，但是加鎖順序不同，導致互相等待。例如，事務 A 按照順序鎖定記錄 1 和記錄 2，而事務 B 以相反的順序鎖定記錄 2 和記錄 1。
  3. 使用了 gap lock (間隙鎖)：在 InnoDB 的 Next-Key Locking 機制下，間隙鎖定也可能導致死鎖，尤其是在範圍查詢時，多個事務試圖鎖定同一間隙。
  4. 長事務和鎖等待時間過長：事務執行時間長，未及時釋放鎖，造成其他事務等待鎖超時或死鎖。

二、死鎖檢測與處理

MySQL 使用 死鎖檢測 來處理死鎖問題。MySQL 會自動檢測事務是否處於死鎖狀態，並中止其中一個事務，釋放鎖以允許另一個事務繼續執行。InnoDB 儲存引擎透過引入死鎖檢測機制來解決這個問題，當檢測到死鎖時，會選擇一個事務進行回滾，以打破僵局。被回滾的事務會丟擲 Deadlock found when trying to get lock 錯誤。

三、如何避免和處理 MySQL 的死鎖？

1. 合理設計索引

使用合適的索引可以減少加鎖的範圍，降低死鎖的發生機率。沒有索引時，MySQL 會對錶中的所有記錄加鎖，增加了鎖衝突的機會。因此，合理地設計和使用索引，確保查詢能夠快速找到資料，避免不必要的鎖爭用，能夠顯著減少死鎖風險。

2. 保持加鎖順序一致

事務操作表中的多條記錄時，保持一致的加鎖順序可以有效減少死鎖問題。例如，如果兩個事務都需要加鎖相同的資源，確保它們按照相同的順序請求鎖，避免死鎖。

3. 減少事務的鎖定時間

儘量縮短事務的執行時間，減少鎖的持有時間。將事務劃分為更小的邏輯單元，避免長時間佔用資源。同時，將非必要的複雜操作儘量移到事務外執行。

4. 減少併發度

在併發較高的情況下，增加鎖衝突和死鎖的機率較高。可以透過控制併發度來減少鎖爭用，比如使用樂觀鎖機制，避免頻繁加鎖。

5. 使用表鎖替代行鎖

對於一些寫操作集中的場景，可以考慮使用表鎖替代行鎖，以避免行級鎖導致的死鎖。不過表鎖會導致併發效能下降，所以需要根據業務場景選擇合適的鎖。

6. 鎖定更小的範圍

儘量透過使用主鍵索引和合適的條件，減少事務鎖定的行範圍。特別是在 UPDATE 或 DELETE 操作中，使用精準的查詢條件來限制鎖的作用範圍。

7. 分批提交事務

對於批次操作，考慮將大事務拆解成多個小事務，減少一次性加鎖的行數和操作範圍，減少鎖的持有時間。

8. 選擇合適的事務隔離級別

適當降低事務隔離級別可以減少鎖衝突的機率。例如，可以將事務隔離級別從 Serializable 調整為 Read Committed 或 Repeatable Read，來減少行鎖定的情況。

9. 加鎖操作使用SELECT ... FOR UPDATE

當你需要在查詢資料後立即進行更新時，可以使用 SELECT ... FOR UPDATE 來顯式地鎖定行，避免在更新時再去加鎖造成的死鎖。

四、常見死鎖示例

以下是一個常見的死鎖示例，兩個事務嘗試對相同的記錄加鎖但順序不同：

-- 事務 A
START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE id = 1; -- 鎖住記錄 1
-- 此時，事務 B 在等待鎖定記錄 1

-- 事務 B
START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE id = 2; -- 鎖住記錄 2
-- 此時，事務 A 在等待鎖定記錄 2

-- 事務 A 嘗試更新記錄 2，但事務 B 持有鎖，事務 A 等待
UPDATE orders SET status = 'shipped' WHERE id = 2;

-- 事務 B 嘗試更新記錄 1，但事務 A 持有鎖，事務 B 等待

-- 死鎖發生，MySQL 自動檢測並回滾其中一個事務

五、如何檢測和分析死鎖？

透過以下方式可以檢測和分析 MySQL 中的死鎖：

1. 啟用 innodb_print_all_deadlocks 引數

透過設定 innodb_print_all_deadlocks=ON，可以在 MySQL 日誌中輸出所有的死鎖資訊，便於分析和除錯。

2. 使用 SHOW ENGINE INNODB STATUS 命令

在 MySQL 發生死鎖後，可以使用 SHOW ENGINE INNODB STATUS 命令檢視死鎖資訊。該命令會輸出最近發生的死鎖情況，幫助開發者找到死鎖的根源。

SHOW ENGINE INNODB STATUS\G

輸出中包含的資訊包括：

  • 哪個事務被回滾
  • 發生死鎖時，事務分別持有哪些鎖，等待哪些鎖
  • 事務操作的 SQL 語句

3. MySQL 慢查詢日誌

開啟 MySQL 慢查詢日誌，也可以間接幫助發現由於鎖等待導致的效能問題，雖然不能直接顯示死鎖，但可以作為鎖衝突問題排查的輔助工具。

六、死鎖後的應對策略

當發生死鎖時，MySQL 會自動回滾其中一個事務，開發人員需要捕獲並處理這種異常。

在程式碼中，你可以使用如下方式處理死鎖：

try {
    // 執行事務
    ...
} catch (SQLException e) {
    if (e.getErrorCode() == 1213) { // 1213 代表死鎖錯誤程式碼
        // 死鎖檢測，進行重試
        retryTransaction();
    } else {
        // 其他異常處理
        throw e;
    }
}

透過捕獲死鎖異常並進行適當的重試，系統可以在發生死鎖後繼續執行，從而提升系統的健壯性。

七、總結

MySQL 死鎖是資料庫在併發場景下常見的問題，特別是對於大規模、複雜的業務系統，死鎖問題更為頻繁。透過合理的索引設計、保持加鎖順序一致、縮短事務時間、最佳化鎖策略等手段，可以有效減少死鎖的發生。同時，當死鎖發生時，MySQL 具備死鎖檢測和自動回滾機制，開發人員可以透過合理的異常處理和重試機制，來提高系統的穩定性和可靠性。

秋是慢入的，但冷卻是突然的，晴不知夏去，一雨方覺秋深！上海有點冷了。

相關文章