以無衝突和非阻塞方式刪除孤立行：在雙重檢查鎖定的情況下預測執行謂詞的順序

我有桌子person和toy. person.favorite_toy_id從personto有一個外鍵toy。此外鍵聲明為ON DELETE RESTRICT. 我現在想以無違規和非阻塞的方式刪除所有不再被聲明為收藏的玩具：

• 我想刪除盡可能多的孤立玩具，同時避免外鍵違規，因為我們試圖刪除仍在使用的玩具。
• 我不想等待其他正在進行的事務完成，這些事務可能會引入對玩具的引用（需要密鑰共享鎖）或只是更新玩具（需要*（無密鑰）更新鎖*）。兩種鎖類型都會阻止我們的更新鎖請求，這是刪除玩具所必需的。

第一種天真的方法是：

delete from toy
where       not exists(select 1 from person where person.favorite_toy_id = toy.id)

這在並發環境中不起作用：在not exists謂詞完成後，並發事務可以將相關玩具聲明為收藏。在這種情況下，我們最終會違反外鍵。另外，如前所述，我更喜歡以非阻塞方式進行此刪除，這在此查詢中未嘗試。

因此，我試圖避免這種外鍵違規和任何阻塞的第二種方法是：

delete from toy
where       toy.id in
           (
             select toy.id
             from   toy
             where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
             for update skip locked
           )

但是，這並不能解決避免外鍵違規的要求，因為在評估not exists謂詞之後會獲取鎖定。因此，我們嘗試刪除仍標記為收藏的玩具的可能性很小，從而導致外鍵違規。

我第三次嘗試解決這個問題如下：

delete from toy
where       toy.id in
           (
             select toy.id
             from   toy
             where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
             for update skip locked
           ) and
           not exists(select 1 from person where person.favorite_toy_id = toy.id)

這適用於雙重檢查鎖定（https://en.wikipedia.org/wiki/Double-checked_locking）。當且僅當我們保證始終在附加not exists謂詞之前評估子查詢時，這才有效。據我所知，沒有這樣的保證。

我的問題很有教育意義：這可以在純 SQL 查詢中解決嗎？我們當然可以在plpgsql如下所示的函式中實現這一點，但假設我們想在一個簡單的 SQL 查詢中解決這個問題。

create function prune_toys() returns void as
$$
declare
 _id int;
begin
 for _id in select toy.id from toy where not exists(...) for update skip locked loop
   delete from toy where toy.id = _id and not exists(...);
 end loop;
end;
$$
language plpgsql;

在這一切中，我假設讀送出的事務隔離級別。

這更像是一場業務流程/ UI 競賽。它通常通過在刪除和/或不再接受它們為有效之前棄用/隱藏值一段時間來解決。

一種方法是在DateDeprecated玩具中添加一列。當您檢查時將其更新為目前日期，並且沒有人喜歡這個玩具。如果/當玩具被選為收藏夾時，清除該欄位。在 UI 中，根本不顯示/列出已棄用的玩具，或者在特定時間段（如 7 天以上）內不顯示已棄用的玩具。在某個時候刪除長期棄用的玩具，比如在它們被棄用 14 天之後。

好吧，我不知道這是否會有所幫助，但使用“不存在”之類的否定邏輯意味著必須檢查（鎖定）每一行以確定它是否符合標準。如果你能找到一種方法讓它像“EXISTS”這樣的正邏輯，它應該只評估符合加速查詢和減少鎖的行。只是一個想法…

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