我有以下并發用例：可以隨時呼叫端點并且應該發生操作。該操作在偽代碼中如下所示（當前隔離級別為READ COMMITTED）：
SELECT * FROM TABLE_A WHERE IS_LATEST=true FOR UPDATE
// DO SOME APP LOGIC TO TEST VALIDITY
// ALL GOES WELL => INSERT OR UPDATE NEW ROW WITH IS_LATEST=TRUE => COMMIT
// OTHERWISE => ROLLBACK (all good not interesting)
現在，SELECT FOR UPDATE如果這些操作中的兩個在更新方面同時開始，則這種方法很好。因為兩個事務看到的行數相同，所以一個事務將更新行，第二個事務將等待輪到它，然后才能更新，SELECT FOR UPDATE并且狀態有效。
我遇到的問題是當我在第一筆交易中有插入時。發生的情況是，例如，當第一個事務使該鎖SELECT FOR UPDATE有兩行時，然后事務繼續，在事務的中間，第二個事務想要SELECT FOR UPDATE（最新）并等待第一個事務完成..第一個事務完成并且在資料庫中實際上有一個新的第三個專案，但是第二個事務在等待行鎖被釋放時只拾取兩行。（這是因為在呼叫SELECT FOR UPDATE快照時不同，只有兩行匹配IS_LATEST=true）。
有沒有辦法進行此事務，以便SELECT鎖在等待后獲取最新的快照？
uj5u.com熱心網友回復：
問題是每個命令只能看到在查詢開始之前已經提交的行。有各種可能的解決方案...
更嚴格的隔離級別
您可以使用更嚴格的隔離級別來解決這個問題，但這相對昂貴。
Laurenz 已經為此提供了解決方案。
只需啟動一個新命令
保持（便宜的）默認隔離級別READ COMMITTED，然后啟動一個新命令。
只有幾行要鎖定
雖然只鎖定了滿滿一排的手，但最簡單的解決方案是重復相同的操作SELECT ... FOR UPDATE。第二次迭代查看新提交的行并另外鎖定它們。
理論上存在與其他事務的競爭條件，這些事務可能會在等待事務之前鎖定新行。這將導致僵局。極不可能，但可以肯定的是，以一致的順序鎖定行：
BEGIN; -- default READ COMMITTED
SELECT FROM table_a WHERE is_latest ORDER BY id FOR UPDATE; -- consistent order
SELECT * FROM table_a WHERE is_latest ORDER BY id FOR UPDATE; -- just repeat !!
-- DO SOME APP LOGIC TO TEST VALIDITY
-- pseudo-code
IF all_good
UPDATE table_a SET is_latest = true WHERE ...;
INSERT table_a (IS_LATEST, ...) VALUES (true, ...);
COMMIT;
ELSE
ROLLBACK;
END;
部分索引(id) WHERE is_latest將是理想的。
更多要鎖定的行
對于超過一手的行，我會改為創建一個專用的單行令牌表。一個防彈實作可能如下所示，以管理員或超級用戶身份運行：
CREATE TABLE public.single_task_x (just_me bool CHECK (just_me) PRIMARY KEY DEFAULT true);
INSERT INTO public.single_task_x VALUES (true);
REVOKE ALL ON public.single_task_x FROM public;
GRANT SELECT, UPDATE ON public.single_task_x TO public; -- or just to those who need it
看：
- 如何只允許一個表的一行？
然后：
BEGIN; -- default READ COMMITTED
SELECT FROM public.single_task_x FOR UPDATE;
SELECT * FROM table_a WHERE is_latest; -- FOR UPDATE? ①
-- DO SOME APP LOGIC TO TEST VALIDITY
-- pseudo-code
IF all_good
ROLLBACK;
ELSE
UPDATE table_a SET is_latest = true WHERE ...;
INSERT table_a (IS_LATEST, ...) VALUES (true, ...);
COMMIT;
END;
單鎖更便宜。
① 你可能想要也可能不想額外加鎖，以防止其他寫操作，可能使用較弱的鎖......
無論哪種方式，所有鎖都會在事務結束時自動釋放。
咨詢鎖
或者使用咨詢鎖。但是你必須在完成后手動解鎖。像：
BEGIN; -- default READ COMMITTED
SELECT pg_advisory_lock(123);
SELECT * FROM table_a WHERE is_latest;
...
SELECT pg_advisory_unlock(123); -- in any case, even if there was an error!!!
只需確保為您的特定任務使用唯一令牌。123在我的例子中。
uj5u.com熱心網友回復：
使用您的方法，第二個事務中的查詢將在鎖定消失后回傳一個空結果，因為它is_latest = FALSE在有問題的行上看到，而新行尚不可見。因此，在這種情況下，您將不得不重試交易。
我建議您改用REPEATABLE READ隔離級別和樂觀鎖定：
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM table_a WHERE is_latest; -- no locks!
/* perform your application ruminations */
UPDATE table_a SET is_latest = FALSE WHERE id = <id you found above>;
INSERT INTO table_a (is_latest, ...) VALUES (TRUE, ...);
COMMIT;
那么可能會發生三件事：
您的查詢找到一行，并且事務成功。
您的查詢找不到任何行，然后您可以插入第一行。
查詢找到一行，但該行的更新會導致序列化錯誤。
在這種情況下，您知道并發事務受到干擾，并且您重復整個事務作為回應。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/463922.html
標籤：sql PostgreSQL 并发 锁定