主页 / 问题 / 1389882
Accepted
Inventor
Asked:2022-08-13 22:58:30 +0000 UTC2022-08-13 22:58:30 +0000 UTC

如果已经有 3 行具有相同的值，则防止插入一行

  • 772

有一个这样的表：

CREATE TABLE sessions (
    id SERIAL,
    user_id INT NOT NULL,
    active bool
);

我需要在表中插入一个新行，但是在插入时，请确保与我在表中的行一起，一个 user_id 的 active=true 行不会超过 3 行。我不是目前唯一可以执行请求的人，即 可以进行并行记录。无法创建索引，因为其他用户必须能够插入超过 3 行。到目前为止，只有用于写入的表锁（EXCLUSIVE）浮现在脑海中。我支持这样一个事实，即只有在没有其他出路的情况下才应该使用阻塞，几乎总是有这样的出路。有没有更好的屏蔽方法？

sql

1 个回答

  1. Best Answer

    在 postgresql 中有两种方法可以解决这个问题。第一种方式是使用可序列化的事务隔离级别。在它上面，postgresql 保证两个并发事务的行为就像它们严格按顺序执行一样。如果数据库检测到相互影响的可能性，这样做的代价是有可能使其中一个事务崩溃。因此，如果发生序列化错误，您的应用程序必须准备好重试事务。程序：

    1. 开始交易begin isolation level serializable;
    2. 使用简单的选择获取给定用户的当前记录数 ... where user_id=X and active=true
    3. 根据数量，决定插入或可能修改记录
    4. commit
    5. 如果在提交期间发生序列化错误，请从第 1 点重新执行操作

    注意：步骤 5 的执行是强制性的，由于存在 3 个合适的记录，可能没有发生错误的序列化错误。例如，在具有相似用户 ID 的 2 个事务中工作时可能会发生错误，因为谓词锁将用于受事务中读写影响的索引页上。并且一页索引可以包含多个用户的数据。

    第二种方法是使用具有特殊功能之一的咨询锁。程序：

    1. 开始交易
    2. 例如，运行 pg_advisory_xact_lock(UserID) 以在事务中对给定用户获取排他锁。
    3. 获取匹配记录数
    4. 必要时执行插入
    5. commit
    • 2

相关问题