PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

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.

In This Article