PostgreSQL Zero to Advanced #9: Transactions, Locking, Functions & Triggers

Buying a course is not one operation: we must take money from the student's wallet and create the order. If the server crashes between those two steps, the student loses money and gets nothing. And if two requests from the same student arrive at the same moment, both might spend the same balance. In this part we fix these problems with transactions, isolation levels and row locks, and then move the purchase logic into the database with PL/pgSQL functions and triggers.

A wallet table

Let's give some students a prepaid wallet. The CHECK (balance >= 0) constraint is our last line of defence: the database will never allow a negative balance, no matter what the application code does.

CREATE TABLE wallets (
    user_id INTEGER PRIMARY KEY REFERENCES users (id),
    balance INTEGER NOT NULL DEFAULT 0 CHECK (balance >= 0)
);

INSERT INTO wallets (user_id, balance) VALUES
    (1, 500000),
    (2, 100000),
    (7, 300000);

Output:

CREATE TABLE
INSERT 0 3

Transactions: all or nothing

A transaction groups several statements into one atomic unit. Either all of them are saved (COMMIT) or none of them are (ROLLBACK). Agus buys JavaScript Basics for Rp120,000:

BEGIN;

UPDATE wallets SET balance = balance - 120000 WHERE user_id = 7;

INSERT INTO orders (user_id, course_id, amount, status, ordered_at)
VALUES (7, 3, 120000, 'paid', '2025-10-01 10:00:00+00');

COMMIT;

Output:

BEGIN
UPDATE 1
INSERT 0 1
COMMIT

SELECT * FROM wallets ORDER BY user_id;

Output:

 user_id | balance 
---------+---------
       1 |  500000
       2 |  100000
       7 |  180000
(3 rows)

Agus' balance went from 300,000 to 180,000 and his order was saved, together. These are the ACID guarantees: Atomicity (all or nothing), Consistency (constraints always hold), Isolation (concurrent transactions do not see each other's half-done work) and Durability (once committed, it survives a crash).

When something fails

Now Siti, who has only Rp100,000, tries to buy Next.js for Rp400,000:

BEGIN;

UPDATE wallets SET balance = balance - 400000 WHERE user_id = 2;

INSERT INTO orders (user_id, course_id, amount, status, ordered_at)
VALUES (2, 6, 400000, 'paid', '2025-10-02 09:00:00+00');

COMMIT;

Output:

BEGIN
ERROR:  new row for relation "wallets" violates check constraint "wallets_balance_check"
DETAIL:  Failing row contains (2, -300000).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

Three things to notice. The CHECK constraint rejected the negative balance. After an error, PostgreSQL refuses every further command in that transaction ("current transaction is aborted"). And the final COMMIT was turned into a ROLLBACK, so no order was created and Siti still has her Rp100,000.

ROLLBACK on purpose

You can also undo deliberately. Inside the transaction the deleted row is gone; after ROLLBACK it is back:

BEGIN;
DELETE FROM orders WHERE status = 'refunded';
SELECT COUNT(*) FROM orders;
ROLLBACK;

SELECT COUNT(*) FROM orders;

Output:

BEGIN
DELETE 1
 count 
-------
    14
(1 row)

ROLLBACK
 count 
-------
    15
(1 row)

We now have 15 orders: the original 14 plus Agus' new purchase.

Savepoints

A SAVEPOINT is a checkpoint inside a transaction. You can roll back to it without losing the work done before it:

BEGIN;
UPDATE wallets SET balance = balance + 50000 WHERE user_id = 1;
SAVEPOINT before_bonus;
UPDATE wallets SET balance = balance + 1000000 WHERE user_id = 1;  -- oops
ROLLBACK TO SAVEPOINT before_bonus;
COMMIT;

SELECT * FROM wallets WHERE user_id = 1;

Output:

BEGIN
UPDATE 1
SAVEPOINT
UPDATE 1
ROLLBACK
COMMIT
 user_id | balance 
---------+---------
       1 |  550000
(1 row)

The Rp50,000 bonus survived; the accidental Rp1,000,000 did not.

Isolation levels

Isolation decides what a transaction can see of other transactions running at the same time. PostgreSQL supports three levels in practice:

SHOW transaction_isolation;

Output:

 transaction_isolation 
-----------------------
 read committed
(1 row)

Level	What you see	Typical use
READ COMMITTED (default)	Each statement sees data committed before that statement started	Most OLTP workloads
REPEATABLE READ	The whole transaction sees one snapshot taken at its first statement	Reports that must be consistent across several queries
SERIALIZABLE	Behaves as if transactions ran one after another; conflicting ones fail with a serialization error	Complex invariants; the app must retry on failure

Picture two psql sessions running at the same time. Session A reads Agus' balance twice while session B tops it up by Rp20,000 in between:

Step	Session A	Session B
1	BEGIN; SELECT balance FROM wallets WHERE user_id = 7; returns 180000
2		UPDATE wallets SET balance = balance + 20000 WHERE user_id = 7; (auto-commit)
3	Same SELECT again: 200000 under READ COMMITTED, 180000 under REPEATABLE READ

To choose a level, start the transaction with BEGIN ISOLATION LEVEL REPEATABLE READ;.

Row locking with SELECT ... FOR UPDATE

The classic bug is the lost update: two requests read balance 550,000, both check "enough money", both subtract and both succeed. SELECT ... FOR UPDATE locks the selected rows until the transaction ends, so the second request must wait for the first one to finish and then sees the updated balance:

BEGIN;

SELECT balance
FROM wallets
WHERE user_id = 1
FOR UPDATE;

UPDATE wallets SET balance = balance - 150000 WHERE user_id = 1;

COMMIT;

Output:

BEGIN
 balance 
---------
  550000
(1 row)

UPDATE 1
COMMIT

Budi paid Rp150,000 for something; his balance is now Rp400,000. A few locking tips:

• Keep transactions that hold locks short. Never wait for user input or an HTTP call while holding a lock.
• Use FOR UPDATE NOWAIT to fail immediately instead of waiting, or FOR UPDATE SKIP LOCKED to build job queues where workers skip rows another worker already took.
• Always lock rows in the same order (for example, by ID) to avoid deadlocks. When PostgreSQL detects one, it aborts one of the transactions with ERROR: deadlock detected.

PL/pgSQL functions

Functions let you store reusable logic inside the database. Here is a simple one that returns how much a user has spent on paid orders:

CREATE OR REPLACE FUNCTION user_total_spent(p_user_id INTEGER)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    v_total BIGINT;
BEGIN
    SELECT COALESCE(SUM(amount), 0)
    INTO v_total
    FROM orders
    WHERE user_id = p_user_id
      AND status = 'paid';

    RETURN v_total;
END;
$$;

Output:

CREATE FUNCTION

SELECT name, user_total_spent(id) AS total_spent
FROM users
ORDER BY total_spent DESC, name;

Output:

     name     | total_spent 
--------------+-------------
 Budi Santoso |      775000
 Dewi Lestari |      520000
 Maya Sari    |      400000
 Siti Rahma   |      370000
 Rudi Hartono |      300000
 Andi Wijaya  |      150000
 Rina Putri   |      150000
 Agus Salim   |      120000
(8 rows)

Agus now shows Rp120,000 thanks to the JavaScript purchase earlier in this part. COALESCE makes sure a user with no orders gets 0 instead of NULL.

A purchase function

Now let's put the whole purchase flow into one function. It checks the course, locks the wallet, validates the balance, deducts it and creates the order. A function call runs inside a single transaction, so any RAISE EXCEPTION rolls back everything it did:

CREATE OR REPLACE FUNCTION buy_course(p_user_id INTEGER, p_course_id INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_price    INTEGER;
    v_balance  INTEGER;
    v_order_id INTEGER;
BEGIN
    SELECT price INTO v_price
    FROM courses
    WHERE id = p_course_id AND is_published;

    IF v_price IS NULL THEN
        RAISE EXCEPTION 'Course % is not available', p_course_id;
    END IF;

    -- lock the wallet row so concurrent purchases wait for us
    SELECT balance INTO v_balance
    FROM wallets
    WHERE user_id = p_user_id
    FOR UPDATE;

    IF v_balance IS NULL OR v_balance < v_price THEN
        RAISE EXCEPTION 'Insufficient balance for user % (needs %, has %)',
            p_user_id, v_price, COALESCE(v_balance, 0);
    END IF;

    UPDATE wallets SET balance = balance - v_price WHERE user_id = p_user_id;

    INSERT INTO orders (user_id, course_id, amount, status)
    VALUES (p_user_id, p_course_id, v_price, 'paid')
    RETURNING id INTO v_order_id;

    RETURN v_order_id;
END;
$$;

Output:

CREATE FUNCTION

Budi buys Python for Data Analysis (Rp300,000):

SELECT buy_course(1, 7) AS new_order_id;

Output:

 new_order_id 
--------------
           17
(1 row)

The new order ID is 17. ID 15 was consumed by the failed foreign-key test in Part 6 and ID 16 is Agus' order, another reminder that gaps are normal. Siti tries Next.js again:

SELECT buy_course(2, 6);

Output:

ERROR:  Insufficient balance for user 2 (needs 400000, has 100000)
CONTEXT:  PL/pgSQL function buy_course(integer,integer) line 22 at RAISE

SELECT * FROM wallets ORDER BY user_id;

Output:

 user_id | balance 
---------+---------
       1 |  100000
       2 |  100000
       7 |  180000
(3 rows)

Budi's balance dropped from 400,000 to 100,000; Siti's balance is untouched. The application only needs to call SELECT buy_course($1, $2) and handle the error message.

Triggers

A trigger runs a function automatically when something happens to a table. A common use is an audit log. Let's record every status change of an order:

CREATE TABLE order_status_log (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id   INTEGER     NOT NULL REFERENCES orders (id),
    old_status VARCHAR(20) NOT NULL,
    new_status VARCHAR(20) NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_order_status_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO order_status_log (order_id, old_status, new_status)
    VALUES (NEW.id, OLD.status, NEW.status);
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_status_change
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_order_status_change();

Output:

CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER

Key details: AFTER UPDATE OF status fires only when the status column is in the UPDATE, and the WHEN clause skips updates that do not actually change the value. Inside the function, OLD and NEW hold the row before and after the change. Let's test it. Andi finally pays for his pending Python order (order 6):

UPDATE orders SET status = 'paid' WHERE id = 6;
UPDATE orders SET status = 'paid' WHERE id = 1;  -- already paid, no log

SELECT order_id, old_status, new_status
FROM order_status_log;

Output:

UPDATE 1
UPDATE 1
 order_id | old_status | new_status 
----------+------------+------------
        6 | pending    | paid
(1 row)

Only one log row: the second UPDATE set order 1 to paid, which it already was, so the WHEN condition filtered it out. From now on, every status change is logged no matter which application or script made it.

Common mistakes

• Relying on auto-commit for multi-step operations. Without BEGIN, each statement commits on its own, and a crash in between leaves half-finished data.
• Check-then-act without a lock. Reading a balance and updating it later in separate steps, without FOR UPDATE or an atomic UPDATE ... SET balance = balance - x, causes lost updates.
• Long-running transactions. They hold locks and prevent VACUUM from cleaning up old row versions. Keep them short.
• Not retrying serialization failures. Under SERIALIZABLE or REPEATABLE READ, error code 40001 is expected; the application must retry the transaction.
• Hiding too much logic in triggers. Triggers are invisible from application code. Use them for audit logs and invariants, and document them.

Exercise

1. Write a transaction that refunds order 13: set its status to refunded and credit its amount to Maya's wallet. Maya has no wallet yet, so insert one inside the same transaction. Finish with ROLLBACK instead of COMMIT if you want your data to keep matching Part 10.
2. Check order_status_log: did the trigger record your change?
3. Write a BEFORE INSERT trigger on orders that raises an exception when NEW.amount does not match the current price of the course.
4. Open two psql sessions and reproduce the isolation table above with both READ COMMITTED and REPEATABLE READ.

Summary

You learned how transactions make multi-step changes atomic, how errors abort a transaction, how savepoints give partial rollbacks, what the three isolation levels mean, and how SELECT ... FOR UPDATE prevents lost updates. You then wrapped the purchase flow in a PL/pgSQL function and added an audit trigger.

In the final Part 10 we explore PostgreSQL's most powerful features: window functions, JSONB, full-text search, UPSERT with ON CONFLICT, roles and permissions, and backups with pg_dump.