如何在PostgreSQL存储过程中使查询原子化？

I think the balance will be updated incorrectly if one process select the balance from a unique userid and attempts to do the insert, but another process reads the balance before that happens. How do I fix this?

我认为如果一个进程从唯一的用户ID中选择余额并尝试执行插入，则会错误地更新余额，但是另一个进程在此之前读取余额。我该如何解决？

CREATE OR REPLACE FUNCTION incBalance(INTEGER, BIGINT) RETURNS void AS $$   
DECLARE   
    balanceRecord record;
    newBalance bigint;  
BEGIN   
    FOR balanceRecord IN    
        SELECT balance FROM users WHERE userid = $1
    LOOP
        newBalance := balanceRecord.balance + $2;
        UPDATE users SET balance = newBalance WHERE userid = $1;   

    END LOOP; 
    RETURN;   
END;   
$$ LANGUAGE plpgsql;

1 个解决方案

#1

For this particular query, you could rewrite it be a single SQL statement:

对于此特定查询，您可以将其重写为单个SQL语句：

UPDATE users SET balance = balance + $2 WHERE userid = $1;

More generally, you want to let the transaction system handle atomicity and data consistency. In Postgres, stored procedures are always executed inside a transaction context - if you aren't calling it from an explicit transaction block, it will create one for you.

更一般地说，您希望让事务系统处理原子性和数据一致性。在Postgres中，存储过程总是在事务上下文中执行 - 如果您没有从显式事务块中调用它，它将为您创建一个。

http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html discusses how to set the isolation level if the default isn't stringent enough.

http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html讨论了如果默认值不够严格，如何设置隔离级别。

You will want to read http://www.postgresql.org/docs/9.2/static/mvcc.html to help decide which level is appropriate for a particular stored procedure. Note sections 13.2.2 and 13.2.3 which warn that higher isolation levels are subject to serialization exceptions that should be caught and the transaction retried as a mechanism for ensuring consistency.

您需要阅读http://www.postgresql.org/docs/9.2/static/mvcc.html以帮助确定哪个级别适合特定存储过程。请注意第13.2.2节和第13.2.3节，它们警告更高的隔离级别受到应该捕获的序列化异常的影响，并且重试事务作为确保一致性的机制。

If I have such a procedure, I add a statement at the beginning of procedure's first BEGIN block to ensure the transaction is running at a sufficient isolation level. If no work has been done in the transaction yet, it will raise it if necessary. If the calling context was a transaction that has done work, it will cause an error if the enclosing transaction block did not already raise the isolation level sufficiently. It will not lower the isolation level if it was already higher than what you specify here.

如果我有这样的过程，我在过程的第一个BEGIN块的开头添加一个语句，以确保事务以足够的隔离级别运行。如果在交易中尚未完成任何工作，则会在必要时提出。如果调用上下文是已完成工作的事务，则如果封闭事务块尚未充分提高隔离级别，则会导致错误。如果隔离级别已高于此处指定的隔离级别，则不会降低隔离级别。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

#1