過於急切的顯式行鎖定會導致死鎖

我在使用 INNODB 引擎的 MariaDB 上遇到了死鎖問題。

在我的案例中，我有許多執行緒在一張桌子上工作。每個執行緒首先讀取一些行，然後更新這些行。我正在使用一條SELECT ... WHERE id IN (...)語句一次載入所有相關行。我一直在使用LOCK IN SHARE MODE以確保其他執行緒不會導致損壞。

在數據集顯著減少的表上進行測試時，我遇到了死鎖——儘管每個執行緒都在一組不同的行上工作。當 id 的數量超過表大小的特定比例時，似乎會SELECT ... WHERE id IN (...)鎖定**所有行。**這會導致死鎖，因為執行緒嘗試獲取由（所有）其他執行緒 S 鎖定的記錄上的 X 鎖。

我已經10.3.12-MariaDB-1:10.3.12+maria~xenial在範例數據集上複製了這種行為。

設置：

CREATE TABLE items (
   id bigint unsigned NOT NULL AUTO_INCREMENT,
   some_col bigint unsigned,
   PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO items (some_col)
   VALUES
       (12),
       (36),
       (72),
       (11),
       (81),
       (53),
       (28),
       (37),
       (58),
       (87)
;

使用兩個執行緒的步驟：

1. 執行緒A：獲取鎖

START TRANSACTION;
SELECT * FROM items WHERE id IN (1, 2, 3, 4, 5) LOCK IN SHARE MODE;

（使用WHERE id = 1 OR id = 3 OR ...具有相同的效果。） 2. 執行緒B：獲取鎖

START TRANSACTION;
SELECT * FROM items WHERE id IN (6, 7, 8, 9, 10) LOCK IN SHARE MODE;

3. 執行緒A：更新行id = 1

UPDATE items SET some_col=0 WHERE id=1;

該執行緒掛起，等待記錄上的 X 鎖id = 1。 4. 執行緒B：更新行id = 6

UPDATE items SET some_col=1 WHERE id=6;

此時 MariaDB 將回滾其中一個事務，因為它檢測到死鎖。

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

SHOW ENGINE INNODB STATUS;表示執行緒A對 items 表中的所有記錄持有 S 鎖，而不僅僅是SELECT步驟 1 中查詢返回的記錄。

在步驟 1 中使用單獨SELECT的查詢進行鎖定時，不會出現此問題。

執行緒A：獲取鎖

SELECT * FROM items WHERE id = 1 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 2 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 3 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 4 LOCK IN SHARE MODE;
SELECT * FROM items WHERE id = 5 LOCK IN SHARE MODE;

LOCK IN SHARE的文件說“……在查詢讀取的行上獲得了鎖……”。觀察到的行為表明這意味著查看了所有行，而不僅僅是選定的行。 ANALYZE format=json確認訪問類型ALL適用於步驟 1 中的SELECT查詢。

{
 "query_block": {
   "select_id": 1,
   "r_loops": 1,
   "r_total_time_ms": 0.0562,
   "table": {
     "table_name": "items",
     "access_type": "ALL",
     "possible_keys": ["PRIMARY"],
     "r_loops": 1,
     "rows": 10,
     "r_rows": 10,
     "r_total_time_ms": 0.0373,
     "filtered": 50,
     "r_filtered": 50,
     "attached_condition": "items.`id` in (1,2,3,4,5)"
   }
 }
}

它是否正確？即是否LOCK IN SHARE MODE鎖定在查詢評估期間查看的所有行，而不僅僅是選定的行？

是否有一種可靠的方法來僅讀取/鎖定選定的行（不使用單個SELECT語句）？

---

關於INs 鎖定行為的更多觀察：

• 當列表中只有一個 id 時IN，訪問類型顯示為ANALYZEasconst並且不會發生“over-eager”鎖定。
• 當列表中有多個 id（少於表中行的 50%）時IN，訪問類型顯示為ANALYZEasrange並且不會發生“過度急切”鎖定。
• 當IN列表中的 id 多於表行數的 50% 時，訪問類型顯示為ANALYZEasall並發生“過度渴望”鎖定。

它是否正確？即 LOCK IN SHARE MODE 是否鎖定在查詢評估期間查看的所有行，而不僅僅是選定的行？

是的，假設您使用的是READ-REPEATABLE隔離級別（預設），情況似乎就是這樣。

對於鎖定讀取（SELECT with FOR UPDATE 或 LOCK IN SHARE MODE）、UPDATE 和 DELETE 語句，鎖定取決於語句是使用具有唯一搜尋條件的唯一索引還是范圍類型的搜尋條件。對於具有唯一搜尋條件的唯一索引，InnoDB 只鎖定找到的索引記錄，而不鎖定它之前的間隙。對於其他搜尋條件，InnoDB 鎖定掃描的索引範圍，使用間隙鎖或下一個鍵（間隙加索引記錄）鎖來阻止其他會話插入到範圍所覆蓋的間隙中。

IN是否被視為範圍類型搜尋？

如果所有值都是常量，則根據 expr 的類型對它們進行評估並排序。然後使用二進制搜尋完成對項目的搜尋。

雖然它沒有明確使用術語“範圍”，但描述看起來像，特別是當您認為 READ-REPEATABLE 描述中的另一個選項是“唯一條件”時，它絕對不適用於多個值。

所以要回答這個問題，是的，看來 LOCK IN SHARE MODE 不僅鎖定了選定的行。

至於你的另一個問題…

是否有可靠的方法來僅讀取/鎖定選定的行（不使用單獨的 SELECT 語句）？

是否有某些原因您不想使用單獨的 SELECT 語句？

您可以嘗試以下方法：

SELECT * FROM items WHERE id = 1 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 2 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 3 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 4 LOCK IN SHARE MODE
UNION ALL
SELECT * FROM items WHERE id = 5 LOCK IN SHARE MODE;

這是一個單一的查詢，雖然技術上仍然是單獨的語句。我在我的應用程序中做了類似的事情。雖然，我使用 MySQL，但仍然使用 InnoDB，並且我使用 FOR UPDATE，而不是 LOCK IN SHARE MODE（我一直無法理解為什麼要使用 LOCK IN SHARE MODE，但我離題了。）

所以，它應該可以工作。

如果這還不夠，您能否說明為什麼不想使用多個 SELECT 語句？

引用自：https://dba.stackexchange.com/questions/230065