How to make queries atomic in PostgreSQL stored procedures?

Question

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?

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;

gwaigh · Accepted Answer · 2022-01-26T22:20:02.290

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

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.

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

You will want to read http://www.postgresql.org/docs/14/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.

If I have such a procedure, I add a statement at the beginning of the 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 had not already raised the isolation level sufficiently. It will not lower the isolation level if it was already higher than what you specify here.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

What happens if we, say, execute simultaneously thousands of queries, each of which makes `balance = balance + $2` for that specific user? Wouldn't it be that at some point `balance` would be taken from older time, increased/decreased by $2, loosing some intermediate additions/decreases? And why? — PF4Public, Dec 23 '15 at 14:10
@PF4Public The whole point of using an [ACID](http://stackoverflow.com/questions/3740280/acid-and-database-transactions) compliant database is so you can avoid such problems. ISOLATION LEVEL READ COMMITTED is sufficient for the single UPDATE statement shown. More complex transactions may warrant a higher level as is discussed in the documentation links provided. — gwaigh, Dec 23 '15 at 15:08

How to make queries atomic in PostgreSQL stored procedures?

1 Answers1