將 (LEFT) JOIN 與 SELECT FOR UPDATE 組合時數據不一致

我最近偶然發現了SELECT ... FOR UPDATE與(LEFT) JOIN. 這是表結構以及重現結果的場景：

表結構

create table counter (
 counter_id serial primary key,
 current_counter int not null default 0
);

create table diff (
 diff_id serial primary key,
 diff_increase int not null default 0,
 counter_id serial references counter(counter_id) not null
);

設想

有兩個並發事務 A 和 B，都執行相同的查詢。

1. 事務 A 以該查詢開始，並且能夠獲取鎖並繼續。

select *
 from counter
 left join diff on counter.counter_id = diff.counter_id
where counter.counter_id = 1
order by diff.diff_id desc
limit 1
  for update of counter
;

2. 事務 B 嘗試執行相同的查詢，但無法獲取鎖，因此等待。
3. 事務 A 將執行以下查詢：

update counter
  set current_counter = current_counter + 100
where counter_id = 1
;

insert into diff (diff_increase, counter_id) values (100, 1)
;

commit;

4. 事務 A 已完成，數據庫的狀態現在應如下所示：

-- counter table
counter_id | current_counter
------------------------------
1          | 200

-- diff table
diff_id | diff_increase | counter_id
--------------------------------------
1       | 50            | 1
2       | 50            | 1
3       | 100           | 1

預期行為

事務 B 看到更新的計數器 ( current_counter = 200) 和最後的差異 ( diff_id = 3)。

實際行為

事務 B 繼續使用counter表的新狀態（意思是current_counter = 200），而diff_id仍然是 2 而不是 3。

這種行為是預期的嗎？如果是這樣，為什麼同一個查詢會看到數據庫的不同狀態？這不違反READ COMMITTED隔離級別的保證嗎？

在 Linux 上使用 PostgreSQL 13 進行測試。

本質上，考慮了並發更新的行，但不考慮並發插入的行。

在預設READ COMMITTED隔離級別下，每個命令只能看到在它開始之前已經送出的行。UPDATE添加新行版本，而不是新行。

diff_id = 3在您的範例中插入的行 ( )對於在送出該行之前啟動的並發事務不可見。鎖定與行的可見性FOR UPDATE無關。但是會考慮並發添加的新行版本。UPDATE

手冊中的基本報價：

$$ … $$SELECT查詢（沒有FOR UPDATE/SHARE子句） 只看到查詢開始之前送出的數據；

UPDATE, DELETE, SELECT FOR UPDATE, 和SELECT FOR SHARE 命令在搜尋目標行方面的****行為相同SELECT：它們只會找到在命令開始時間送出的目標行。

WHERE重新評估命令（子句）的搜尋條件，以查看行的更新版本是否仍然匹配搜尋條件。如果是這樣，則第二個更新程序使用行的更新版本繼續其操作。

整章推薦閱讀。

這種情況下最好的行動方案可能是SERIALIZABLE交易

由於鎖定子句，您可能會看到對diff錶的無序影響。FOR UPDATE

SELECT FOR UPDATE的文件在以下“警告”段落中對此提出了警告：

SELECT 命令可以在 READ COMMITTED 事務隔離級別上執行並使用 ORDER BY 和鎖定子句來亂序返回行。這是因為首先應用 ORDER BY。該命令對結果進行排序，但隨後可能會阻止嘗試獲取一個或多個行的鎖定。一旦 SELECT 解除阻塞，某些排序列值可能已被修改，導致這些行看起來是亂序的（儘管它們按照原始列值的順序排列）。

您可能還對該問題的 pgsql 郵件列表中的答案感興趣：SELECT … FOR UPDATE OF SKIP LOCKED 返回可以在與 lock 不同的表上過濾表時返回同一行，其中 Thomas Munro 指向該部分文件並解釋：

這可能看起來很令人驚訝，但這是因為 FOR UPDATE 遵循更新鏈，允許您查看活動快照不可見的已送出元組，只要它們仍然滿足 WHERE 子句

然後

您需要確保行鎖定適用於與 WHERE 子句相同的關係以避免這種情況。

您的查詢無法確定這一點，如果它被鎖定並期望具有相同可見性級別的counter相應值。diff

一般來說，Read Committed隔離級別在面對並發寫入時並不能提供任何保證，所以並發異常的通用解決方案是使用更高的隔離級別並處理序列化失敗。

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