How to make queries atomic in PostgreSQL stored procedures?
I think the balance will not be updated correctly if one process fetches the balance from the unique user id and tries to insert, but another process reads the balance before that happens. How to fix it?
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;
source to share
For this particular query, you can rewrite it as a separate SQL statement:
UPDATE users SET balance = balance + $2 WHERE userid = $1;
More generally, you want a transactional system to handle data atomicity and consistency. In Postgres, stored procedures are always executed inside a transaction context - if you don't call it from an explicit transaction block, it will create one for you.
http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html discusses how to set the isolation level if the default is not strong enough.
You will want to read http://www.postgresql.org/docs/9.2/static/mvcc.html to decide which tier 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 must be caught and the transaction is rolled back as a consistency mechanism.
If I have a procedure like this, I add a statement at the beginning of the first BEGIN block to ensure that the transaction is executed at a sufficient isolation level. If no operation has yet been performed in the transaction , it will raise it if necessary. If the calling context was a transaction being executed with , this would raise an error if the incoming transactional block had already insufficiently raised the isolation level. It does not lower the isolation level if it is already higher than what you specify here.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
source to share