如何在 INSERT-SELECT 循環期間處理並發？

應用程序應將每個使用者對某個資源的訪問限制為每 24 小時的最大訪問次數。為了跟踪這些，我們有下表：

CREATE TABLE accesses (
   user_id         INTEGER NOT NULL,
   dt              TIMESTAMPZ
);

每次訪問資源時，我們都會將其記錄在表中。但是，我們還需要檢查是否達到了訪問限制。為此，我們需要執行以下查詢：

SELECT count(*) 
FROM accesses 
WHERE dt >= timezone('utc', now()) - interval '24 hours' AND user_id = $1

我們需要根據使用者限制檢查結果。

現在，如果同時請求兩個訪問，我們可能會遇到問題：

• 如果我們首先讀取、比較和插入，我們可能會授予比限制更多的訪問權限；
• 如果我們先插入，然後讀取和比較，我們可能會禁止合法訪問。

我知道的兩種方法是使用SERIALIZABLE事務或SELECT ... FOR UPDATE. 我不確定後者在這種情況下是否真的有效，因為我們不會更新讀取的行，而是插入新的行。所以我認為這將是一個無用的鎖，結果可能仍然不正確。

但是，如果有SERIALIZABLE交易失敗，我會重試它。

在這種情況下應該怎麼做，以確保適當的訪問限制？

不確定這是否是最好的方法，但只要應用程序似乎是一種快速可靠的方法

$$ s $$一直使用它。創建一個新表，access_attempt (user_id integer not null primary key).

修改您的工作流程：

begin ;
insert into attempt_access (user_id) values (:user_id);
-- do you verification, raise an error if needed
insert into access (user_id, dt) values(:user_id, current_timestamp) ;
delete from access_attempt where user_id = :user_id;
commit;  

簡要說明。首先，啟動一個新事務，我們將一個 user_id 插入到attempt_access只有一列的新表中。在事務完成之前，其他事務將無法插入具有相同 user_id 的行，並將等待第一個事務完成。然後進行驗證，將記錄插入到access表中，最後將最開始插入的記錄刪除。現在可以送出事務了。如果事務被回滾，記錄attempt_access將被自動刪除。

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