Understanding Transactions And Locking In PostgreSQL
Locking With Our Plpgsql Function
Applying locking to your function:
Listing 4 listing-4.sql
CREATE OR REPLACE FUNCTION buysomething(varchar(255),TEXT) RETURNS integer AS $$ DECLARE account_balance NUMERIC(22,9); BEGIN LOCK TABLE accounting IN EXCLUSIVE MODE; SELECT (SUM(COALESCE(credit,0)) - SUM(COALESCE(debit,0)) INTO account_balance FROM accounting WHERE account_id = $1; IF account_balance >= 5 THEN INSERT INTO purchases (SomeTextCol) VALUES ($2); INSERT INTO accounting (account_id, debit) VALUES ($1, 5); ELSE RAISE EXCEPTION ‘The account % does not have enough free balance’, $1; END IF; END; $$ Language plpgsql;
Now the function will properly wait for an exclusive lock before continuing and you can be assured that by the time debit occurs the balance has not been modified and the user has enough money to complete the operation.
The lock will expire automatically at the end of the transaction as there is no unlock statement.