MySQL資料函式庫交易死鎖與鎖定衝突處理策略

– 9 分鐘閱讀

在高併發的資料函式庫環境中，鎖定衝突和死鎖是常見的效能瓶頸。理解 InnoDB 儲存引擎的鎖定機制對於處理這些問題至關重要。本文將探討如何透過索引、交易隔離級別和資源分割等策略來減少鎖定，並提供程式碼範例和效能分析，以協助開發者最佳化資料函式庫效能。鎖定機制雖然保障了資料一致性，但過多的鎖定會降低系統吞吐量。透過適當的索引設計，可以縮小鎖定的範圍，避免不必要的鎖定衝突。此外，選擇合適的交易隔離級別，例如 READ COMMITTED，可以減少鎖定的持有時間，降低死鎖風險。

資料函式庫交易死鎖與鎖定衝突處理

在資料函式倉管理系統中，交易死鎖（deadlock）是一種常見的問題，尤其是在高並發的環境下。MySQL 的 InnoDB 儲存引擎提供了多種機制來處理鎖定衝突和死鎖。

交易死鎖的發生

交易死鎖發生在兩個或多個交易相互等待對方釋放資源的情況下。例如，當兩個交易同時嘗試更新同一筆資料時，如果第一個交易已經取得該資料的分享鎖，而第二個交易嘗試取得該資料的排他鎖，則第二個交易將被阻塞。接著，如果第一個交易嘗試更新另一筆資料，而該資料已經被第二個交易鎖定，則會發生死鎖。

簡單的死鎖範例

考慮以下兩個交易：

  • 交易 1：對 ID 為 130 的城市資料取得分享鎖，然後嘗試更新該資料。
  • 交易 2：嘗試更新 ID 為 130 的城市資料。
-- 交易 1
START TRANSACTION;
SELECT * FROM world.city WHERE ID = 130 FOR SHARE;

-- 交易 2
START TRANSACTION;
UPDATE world.city SET Population = Population + 1 WHERE ID = 130;

-- 交易 1 繼續執行
UPDATE world.city SET Population = Population + 1 WHERE ID = 130;

在這個例子中，交易 1 首先對 ID 為 130 的城市資料取得分享鎖。接著，交易 2 嘗試更新該資料，但由於交易 1 持有分享鎖，因此交易 2 被阻塞。當交易 1 繼續執行並嘗試更新同一筆資料時，死鎖發生。

死鎖的處理

當發生死鎖時，InnoDB 將自動檢測並選擇一個交易作為犧牲者，回復該交易的變更以解決死鎖。在前面的例子中，交易 2 成為犧牲者，其變更被回復。

停用死鎖檢測

在高並發的環境下，死鎖檢測可能會對效能造成影響。可以透過設定 innodb_deadlock_detect 選項為 OFF 來停用死鎖檢測。然而，這樣做需要謹慎，因為它可能會導致查詢停滯更長時間。

設定鎖等待逾時

如果停用了死鎖檢測，建議設定 innodb_lock_wait_timeout 為一個較低的值，例如 1 秒，以便快速檢測鎖定衝突。此外，啟用 innodb_rollback_on_timeout 選項可以確保在逾時發生時釋放鎖。

InnoDB Mutex 和 Semaphore 等待

當 InnoDB 請求 mutex 或 rw-lock semaphore 但無法立即取得時，它將進入等待狀態。InnoDB 可以透過輪詢（polling）或暫停執行緒（suspending the thread）來等待。

輪詢控制選項

有三個組態選項可以用來控制輪詢行為：

  • innodb_spin_wait_delay：控制輪詢延遲。
  • innodb_spin_wait_pause_multiplier：用於計算 PAUSE 指令的數量。
  • innodb_sync_spin_loops：控制在暫停執行緒之前執行的輪詢迴圈次數。

這些選項可以根據特定的硬體架構和工作負載進行調整，以最佳化效能。

減少鎖定問題

記住，MySQL 和 InnoDB 中的鎖定機制是用於提供平行存取的，通常 InnoDB 的精細鎖定允許高度平行的工作量。然而，如果有過多的鎖定，就會導致平行度降低和查詢堆積，在最壞的情況下，可能會導致應用程式停滯並造成不良的使用者經驗。

因此，在編寫應用程式和設計其資料和存取的架構時，必須考慮鎖定。減少鎖定的策略包括新增索引、更改交易隔離級別、更改組態和搶佔式鎖定。本章涵蓋了這些策略。

交易大小和年齡

減少鎖定問題的一個重要策略是保持交易小型化，避免延遲使交易保持開啟狀態的時間超過必要。最常見的鎖定問題原因之一是修改大量列的交易或活躍時間超過必要。

交易的規模是指交易完成的工作量，特別是它所持有的鎖數量，但交易執行的時間也很重要。如同其他一些主題所討論的，您可以透過索引和交易隔離級別來部分減少影響。然而，記住整體結果也很重要。如果您需要修改許多列，請問自己是否可以將工作分成較小的批次，或者是否需要在同一個交易中完成所有工作。也可以將一些準備工作分出來，在主要交易之外進行。

交易的持續時間也很重要。一個常見的問題是使用 autocommit = 0 的連線。這會在每次執行查詢（包括 SELECT）而沒有活躍交易時啟動一個新的交易，直到執行明確的 COMMIT 或 ROLLBACK、執行 DDL 陳述式或關閉連線時才會完成交易。一些聯結器預設停用自動提交，因此您可能在未察覺的情況下使用此模式，從而錯誤地讓交易保持開啟狀態數小時。

內容解密：

  • 交易大小：指的是交易的工作量，尤其是鎖的數量。
  • 交易年齡：指的是交易的持續時間。
  • autocommit = 0：停用自動提交，可能導致交易意外保持開啟狀態。

索引

索引減少了存取特定列所需的工作量。因此，索引是減少鎖定的好工具，因為只有在執行查詢時存取的記錄才會被鎖定。

考慮一個簡單的例子，您在 world.city 表中查詢名稱為 Sydney 的城市：

START TRANSACTION;
SELECT *
FROM world.city
WHERE Name = 'Sydney'
FOR SHARE;

內容解密：

  • 索引的作用：減少存取特定列的工作量，從而減少鎖定。
  • 示例查詢：展示如何使用 FOR SHARE 鎖定查詢結果。
  • FOR SHARE：用於在查詢結果上設定分享鎖。

重點回顧

  • 交易大小和年齡是影響鎖定問題的重要因素。
  • 使用索引可以減少鎖定。
  • 更改交易隔離級別和組態也可以幫助減少鎖定問題。

透過實施這些策略，您可以減少鎖定問題，提高應用程式的效能和可靠性。

減少鎖定問題的技術探討

在資料函式倉管理系統中，鎖定機制是確保資料一致性和完整性的重要手段。然而，不當的鎖定策略可能導致效能問題、死鎖甚至資料損壞。本文將探討如何減少鎖定問題，涵蓋索引最佳化、記錄存取順序、交易隔離級別等關鍵議題。

索引對鎖定的影響

索引是最佳化查詢效能和減少鎖定範圍的有效工具。以 world.city 表為例，當查詢 Name = 'Sydney' 時，若無索引，資料函式庫需進行全表掃描並鎖定所有記錄。透過新增索引於 Name 欄位，可大幅減少鎖定的記錄數。

無索引情況下的鎖定狀況

在無索引的情況下，查詢 Name = 'Sydney' 會導致 4103 個記錄鎖定，其中 24 個鎖定為主鍵的 supremum 虛擬記錄。

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)

Connection 1> SELECT ID, Name, CountryCode, District
FROM world.city
WHERE Name = 'Sydney'
FOR SHARE;
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
| ID | Name | CountryCode | District |
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
| 130 | Sydney | AUS | New South Wales |
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
1 row in set (0.0034 sec)

查詢鎖定狀況：

Connection 2> SELECT index_name, lock_type, lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1143
GROUP BY index_name, lock_type, lock_mode;
+
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
-
---
+
---
-
---
---
+
| index_name | lock_type | lock_mode | COUNT(*) |
+
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
-
---
+
---
-
---
---
+
| NULL       | TABLE     | IS        | 1        |
| PRIMARY    | RECORD    | S         | 4103     |
+
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
-
---
+
---
-
---
---
+
2 rows in set (0.0323 sec)

有索引情況下的鎖定狀況

新增索引於 Name 欄位後，鎖定記錄數大幅減少至 3 個。

Connection 1> ALTER TABLE world.city ADD INDEX (Name);
Query OK, 0 rows affected (1.5063 sec)

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 1> SELECT ID, Name, CountryCode, District
FROM world.city
WHERE Name = 'Sydney'
FOR SHARE;
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
| ID | Name | CountryCode | District |
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
| 130 | Sydney | AUS | New South Wales |
+
---
--+
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-
---
--+
1 row in set (0.0004 sec)

查詢鎖定狀況：

Connection 2> SELECT index_name, lock_type, lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1145
GROUP BY index_name, lock_type, lock_mode;
+
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
-
---
-
---
-+
---
-
---
---
+
| index_name | lock_type | lock_mode      | COUNT(*) |
+
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
-
---
-
---
-+
---
-
---
---
+
| NULL       | TABLE     | IS             | 1        |
| Name       | RECORD    | S              | 1        |
| PRIMARY    | RECORD    | S,REC_NOT_GAP  | 1        |
| Name       | RECORD    | S,GAP          | 1        |
+
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
-
---
-
---
-+
---
-
---
---
+
4 rows in set (0.0011 sec)

索引對鎖定的影響分析

新增索引可有效減少鎖定的記錄數，但也可能增加死鎖的風險。適當的索引策略需考量查詢模式和資料特性。

記錄存取順序的重要性

不同交易存取記錄的順序不一致可能導致死鎖。確保交易以相同的順序存取記錄可避免死鎖。

如何確保一致的存取順序

  1. 統一查詢順序：在應用層面統一查詢和更新的順序。
  2. 使用最佳化器提示：在必要時使用最佳化器提示（Optimizer Hints）來指定連線順序或其他最佳化策略。

交易隔離級別的選擇

InnoDB 支援多種交易隔離級別，不同級別對鎖定的要求不同。選擇適當的交易隔離級別可有效減少鎖定問題。

READ COMMITTED 的優勢

READ COMMITTED 級別下，InnoDB 較少使用 Gap Locks，且非修改的行在陳述式執行完畢後會釋放鎖。

Connection 1> SET SESSION transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.0002 sec)

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 1> UPDATE world.city
SET Population = 5000000
WHERE Name = 'Sydney'
AND CountryCode = 'AUS';
Query OK, 1 row affected (0.0024 sec)
Rows matched: 1 Changed: 1 Warnings: 0

查詢鎖定狀況：

Connection 2> SELECT index_name, lock_type, lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1149
GROUP BY index_name, lock_type, lock_mode;

REPEATABLE READ 與 SERIALIZABLE 的差異

REPEATABLE READ 和 SERIALIZABLE 級別下，鎖定會在交易結束時釋放，可能導致更多的鎖定問題。

減少鎖定問題的技術探討

在資料函式倉管理系統中，鎖定（Locking）是確保資料一致性和完整性的重要機制。然而，不當的鎖定策略可能導致效能問題甚至死鎖（Deadlock）。本篇文章將探討如何減少鎖定問題，特別是在MySQL資料函式庫的背景下。

交易隔離級別對鎖定的影響

MySQL支援多種交易隔離級別（Transaction Isolation Level），包括READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。不同的隔離級別會影響鎖定的行為。

REPEATABLE READ vs READ COMMITTED

在REPEATABLE READ隔離級別下，MySQL會對查詢涉及的資料範圍加上鎖定，以確保在同一交易中多次執行相同查詢時結果保持一致。然而，這種方式可能導致更多的鎖定和潛在的死鎖問題。

-- 連線1
START TRANSACTION;
UPDATE world.city
SET Population = 5000000
WHERE Name = 'Sydney'
AND CountryCode = 'AUS';

-- 連線2
SELECT index_name, lock_type, lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1149
GROUP BY index_name, lock_type, lock_mode;

在REPEATABLE READ下，查詢結果顯示有多個鎖定被持有，包括表鎖定和記錄鎖定，甚至包括間隙鎖定（Gap Lock）。

切換至 READ COMMITTED

切換到READ COMMITTED隔離級別，可以減少鎖定的數量和範圍，從而降低死鎖的風險。

-- 連線1
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
UPDATE world.city
SET Population = 5000000
WHERE Name = 'Sydney'
AND CountryCode = 'AUS';

-- 連線2
SELECT index_name, lock_type, lock_mode, COUNT(*)
FROM performance_schema.data_locks
WHERE object_schema = 'world'
AND object_name = 'city'
AND thread_id = 1153
GROUP BY index_name, lock_type, lock_mode;

內容解密：

  1. 隔離級別的變更：將交易隔離級別從REPEATABLE READ變更為READ COMMITTED，可以明顯減少鎖定的數量。
  2. 鎖定型別的差異：在READ COMMITTED下，鎖定主要集中在必要的記錄上，避免了不必要的間隙鎖定。
  3. data_locks 表的查詢：透過查詢 performance_schema.data_locks 表，可以清晰地觀察到不同隔離級別下的鎖定情況。

資源分割與鎖定最佳化

除了調整交易隔離級別外，資源分割也是減少鎖定爭用的有效策略。MySQL允許對某些資源（如InnoDB緩衝池、適應性雜湊索引和表開啟快取）進行分割，以減少爭用。

相關組態引數

  • innodb_adaptive_hash_index_parts
  • innodb_buffer_pool_instances
  • table_open_cache_instances

這些引數控制著相關資源的分割粒度，需要在MySQL重啟後生效。適當地調整這些引數，可以在多核心CPU環境下提升效能。

停用InnoDB適應性雜湊索引

在某些情況下，停用InnoDB的適應性雜湊索引可以減少鎖定爭用。適應性雜湊索引是一種動態建立的索引，用於加速查詢。然而，它也可能成為爭用的來源。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。