Understanding Transactions And Locking In PostgreSQL

Automation With PLPGSQL

Now we need to actually do something useful with this information.

We will create a plpgsql function block that will determine if the user has a 5 dollar positive balance, insert into some other table (to simulate the purchase of some item), and then post a 5 dollar debit to the users account.

For those not familiar with plpgsql it’s extremely easy to use. Just be sure to use the createlang binary in the PostgreSQL distribution to add plpgsql support to your database. (/usr/local/pgsql/bin/createlang plpgsql yourdbname) as the postgres user.

CREATE OR REPLACE FUNCTION buysomething(varchar(255),TEXT) RETURNS integer AS $$
    DECLARE
        account_balance NUMERIC(22,9);
    BEGIN
        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;

Note: there are better ways to write this function, but this method demonstrates the most functionality.

So this function looks like it would do the job, but there’s a trick. What happens if this function is called twice at the same identical time?

Well what happens is between the balance selection and the insert into accounting, another debit could have been committed to the accounting table. There is no guarantee that after the select is run the balance will not change until the end of the function.

• Next: Introduction To Locks