PostgreSQL 在 READ_COMMITTED 模式的特性

在閱讀官方文件時，雖然官方文件是寫得挺詳細的，但有種很像是把程式邏輯攤出來解釋的感覺，有時候需要稍微思考一下才能把前後的描述合併起來理解。

首先，READ_COMMITTED 有個最主要的特性是交易中的每個指令開始前，都會先重新獲取一次當前的 snapshot。也就是說，即使在這個指令之前剛被其他交易 commit 的變更，在這個指令開始時都會被囊括進 snapshot（會產生 non-repeatable read 的結果）。

UPDATE/DELETE 指令的特性

接著，官方文件中有著以下的描述：

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). ……..The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. ……..

剪得好像有點長…。不過總之這段描述中有個很重要的性質：

1. 這些指令在搜尋條件中，只會去找在 snapshot 建立的時候（也就是正要開始執行這行指令時）已經 commit 的資料。
2. 如果這些被找出來的資料，其中包含了正在被其他交易更新中的資料，那它會等待其他交易完成，然後「重新衡量這些資料是否仍然符合條件」。這裡最重要的地方在於，它並不是重新執行一次搜尋，而是只針對之前搜尋的結果，重新確認這些結果是否依然滿足條件。

然後再往下看到官方文件後面幾段的描述：

For example, consider a DELETE command operating on data that is being both added and removed from its restriction criteria by another command, e.g., assume website is a two-row table with website.hits equaling 9 and 10:

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

The DELETE will have no effect even though there is a website.hits = 10 row before and after the UPDATE. This occurs because the pre-update row value 9 is skipped, and when the UPDATE completes and DELETE obtains a lock, the new row value is no longer 10 but 11, which no longer matches the criteria.

在這個例子中，一開始資料表裡有 hits = 9 和 hits = 10 兩筆資料，然後有個並行的交易在執行 UPDATE，分別把 hits = 9 和 hits = 10 更改成 hits = 10 和 hits = 11。在這個時候，我們剛好下了 DELETE 指令要刪除 hits = 10 的資料，但結果有可能什麼事也沒發生！這裡搭配上頭的描述，就可以理解到發生了什麼事：

1. 先假設在最開始的兩筆資料分別叫做 row-#1 和 row-#2。當 DELETE 指令要開始執行的時候，它會先建立當下的 snapshot，此時如果 UPDATE 指令尚未 commit，UPDATE 指令的影響對於 DELETE 指令這邊來說就會完全被忽略，因此 DELETE 指令這時建立的 snapshot 會是原始的 row-#1（hits = 9）和 row-#2（hits = 10）。
2. DELETE 指令開始執行時，會先進行搜尋，會搜尋滿足條件的資料列。而這時因為是對 snapshot 進行搜尋，所以搜尋到的會是 row-#2，這裡非常正常～。
3. 接著 DELETE 開始要刪除資料列，但此時它發現 row-#2 正在被其他交易（也就是 UPDATE 指令的交易）更改中，所以會先等到其他交易完成。當 UPDATE 指令完成以後，DELETE 就會依照文件所說的，重新檢查一次之前搜尋的結果是否依然滿足條件，此時因為之前搜尋的結果是 row-#2，因此它只會檢查 row-#2 是否仍然滿足條件，而結果自然是不滿足，因為 UPDATE 執行過後，row-#2 已經被更新為 hits = 11 了。所以這時 DELETE 指令就會產生什麼事也不做就直接完成的結果，因為它會判定沒有任何滿足條件的資料列可以被它刪除。