主页 / dba / 问题 / 226880
Accepted
thousandsofthem
Asked: 2019-01-12 02:26:43 +0800 CST

使用 SELECT INTO 插入最新行是否安全？

  • 772

尝试使行保持一致，然后引用之前的行。

CREATE TABLE transactions (
    id         int     PRIMARY KEY
       GENERATED BY DEFAULT AS IDENTITY,

    account_id bigint  REFERENCES account,
    change     integer NOT NULL,
    balance    integer NOT NULL,
)

来自客户端的查询基本上是

SELECT balance
FROM transactions
WHERE account_id = X
ORDER BY id DESC
LIMIT 1;

INSERT INTO transactions (account_id, change, balance)
VALUES (X, 100, $balance + 100);

此代码将在并发插入时产生不正确的结果。那么，问题是如何让余额计算永远正确呢？

会INSERT INTO ... SELECT(...)够吗？

postgresql consistency

3 个回答

  1. 如果在表中包含一个 change_id，在 (account_id, change_id) 上有一个唯一索引，并且始终将 change_id 从该 account_id 的先前值递增 1，则可以使它工作。然后，在对同一 account_id 进行冲突插入时，将因违反唯一约束而失败，需要重试。

    你可以在一个insert into ... select声明中做到这一点。或者您可以在两个单独的语句中执行此操作，只要它们在一个事务中即可，并且第一个语句同时选择 balance 和 change_id。

    我认为这是可怕的设计。但它可以工作。实际上，您有两个实体，一个交易和一个帐户，因此应该有两个表而不是试图将它们塞进一个表中。

    • 0

  2. 有两种方法可以做到这一点。

    • 当您第一次选择并仅在插入并提交新记录后释放它时，在您正在读取的行上获得独占锁。这将防止其他进程读取相同的记录并基于该记录进行更新并给出不正确的最终余额。这种方法意味着您可以根据需要更新表格，但一次只能更新一个帐户。
    • 在执行该过程时锁定表。这将具有相同的总体结果，但也意味着一次只能有一个进程更新表。

    最好的方法是根据需要动态计算余额，因为这将确保它始终是最新的，并且您不会有任何竞争条件，即多个进程试图同时更新余额并计算不正确平衡因此。

    请注意，还可以创建一个流程来按计划的时间间隔计算和更新计算出的余额，并且可以将其设置为仅对时间范围内发生变化的帐户执行此操作。

    • 0
  3. Best Answer

    使用此架构根本不安全。

    INSERT INTO ... SELECT(...)

    将在单个事务中执行查询，但该事务不会锁定您正在访问的表SELECT以防止 future INSERTs。这里的问题是，SELECT本身有ORDER BY id DESC LIMIT 1;没有表锁，在这个schema下是没办法做到的。数据库不理解后面的插入动态id（没有数据库理解）。因此，您充其量可以隐式锁定使用 获得的单行SELECT，但不能保证在您到达 时那是最新的行INSERT。

    那么这里的解决方案是什么，

    1. 显式表级锁定（不要这样做）。
    2. 锁定共享资源，例如获取行上SELECT FROM accounts WHERE account_id = x FOR UPDATE的FOR UPDATE行级独占锁。如果该语句在INSERT INTO .. SELECTthen future中，则SELECT FOR UPDATE该行上的 s 将阻塞并等待。或者，它可以在从事务表读取之前的同一事务中的任何位置。这就是我会做的。
    3. 谓词锁定也可以在模式下工作，SERIALIZABLE但只是因为id = x. SELECT但是，老实说，我不确定并且我不使用该模式，因为我更喜欢 less voodoo 并且通过扩展表上的聚合sum(txns)肯定不会触发谓词锁，然后它就会变得非常混乱。

    作为旁注，当你这样做时

    INSERT INTO transactions (account_id, change, balance)
VALUES (X, 100, $balance + 100);

    那不安全。但是如果balance存储在 accounts 表中（我们没有理由相信它存在），

    UPDATE accounts
SET balance = balance + 100
WHERE account_id = $1;

    会安全的。

    • 0

相关问题