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;  

Answer 1

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;