主页 / dba / 问题 / 244415
Accepted
rubik
Asked: 2019-08-03 04:48:23 +0800 CST

如何在 INSERT-SELECT 循环期间处理并发？

  • 772

应用程序应将每个用户对某个资源的访问限制为每 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交易失败，我会重试它。

在这种情况下应该怎么做，以确保适当的访问限制？

postgresql transaction

2 个回答

  1. Best Answer

    不确定这是否是最好的方法，但只要应用程序始终使用它，它似乎是一种快速可靠的方法。创建一个新表，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将被自动删除。

    • 2

  2. 假设这不会造成太大的瓶颈，您可以使用 PostgreSQL 咨询锁来序列化对表的访问。这样，您的应用程序只有在锁定时才能执行任何操作。

    https://www.postgresql.org/docs/11/explicit-locking.html

    • 0

相关问题