SELECT … FOR UPDATE 等待鎖定後返回舊值

似乎如果SELECT ... FOR UPDATE必須等待鎖定，同時另一個執行緒更改結果並送出，則初始查詢返回舊結果，即更改之前的結果。

這是預期的行為還是錯誤？它當然似乎沒有用。

這很容易展示。我們來看下表：

CREATE TABLE `orders`(  
 `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `description` VARCHAR(50),
 PRIMARY KEY (`id`)
) ENGINE=INNODB;

而這一系列的陳述：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

SELECT MAX(id)
FROM orders
FOR UPDATE;

# PAUSE HERE

INSERT INTO orders (description)
VALUES ('bla');

COMMIT;

現在我們執行以下操作：

1. 打開兩個連接。
2. 在連接 1 上，執行到暫停。這成功了。
3. 在連接 2 上，執行到暫停。這會卡住等待鎖定。
4. 在連接 1 上，執行其餘部分。這成功了。
5. 連接 2 現在返回了以前的id！
6. 在連接 2 上，SELECT再次執行。現在它返回更新的值。

因此，為了獲得看似明智和可取的行為，我們似乎被迫執行SELECT兩次。

這是預期的行為還是錯誤？

（在 MySQL 5.7.25 上測試）

通過做一些實驗，我已經弄清楚了這種行為的解釋。我使用連接 1 來執行一些其他更改，而不是插入新行。綜合結果揭示了該過程的工作原理。

• 如果連接 1 更新了description，id連接 2 將看到更新的description。（索引掃描在鎖定行之前停止。）
• 如果連接 1 將 更新id為較小的 值，例如從 10 到 9，則連接 2 將返回新值 9。（索引掃描在鎖定行之前停止，看到它消失，並以降序方式繼續，從而到達9點。）
• 如果連接 1 將 更新id為更大的值，例如從 10 到 11，則連接 2 將跳過該值，而是返回id小於其原始值的最大值。id在此範例中，如果存在，則返回9。（索引掃描在鎖定行之前停止，看到它消失，並以降序方式繼續，因此永遠不會超過 11，而是到達 9。）
• 如果連接 1 將 更新id為更大的值，例如從 10 到 11，然後插入新行代替舊行，例如使用id10，連接 2 將選擇新插入的行。（索引掃描在鎖定行之前停止，並且會看到在其位置插入了一些東西。）
• TODO：如果連接 1 將 更新id為更大的值，例如從 10 到 20，然後插入新行代替舊行，但值大於原始值，例如 11，連接 2 會看到嗎？很可能不是，但這應該進行測試。
• 如果連接 1 鎖定了MAX(id)10 的 a，而連接 2 選擇了硬編碼id的 9，則鎖定不會重疊，兩者都可以獨立進行。

我們可以得出結論，這MAX(id)是使用反向掃描確定的，並且鎖位於實際（索引）行上。這是有道理的。我們的隔離級別READ COMMITTED不獲取間隙鎖。REPEATABLE READ可能，但它會導致連接 2 的事務完全失敗，因此沒有更深入地探索該場景。

結束確切的工作

連接 2 開始反向掃描，直到它鎖定某一​​行。

它將等待該鎖被釋放，並繼續從（並包括）id它在. 不是什麼行，而是什麼*id*。

• 結果，如果 那裡不再有一行id，則掃描繼續超出它，直到更小id的 s。
• 因此，如果行已更新，則更新的值是可見的（如果選擇的不僅僅是id）。
• 結果，如果該行已被移動並為其id插入了一個新行，則選擇該新行。

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