Transactions & Concurrency

What Is a Transaction?

A transaction groups one or more SQL statements into a single all-or-nothing unit of work. Either every statement succeeds and the changes are committed to disk, or the entire transaction is rolled back and the database is left unchanged. This guarantee is essential for operations like transferring money between accounts, where a partial write would be catastrophic.

ACID Properties

• Atomicity — all or nothing. If the transaction fails mid-way, all changes are undone.
• Consistency — the database moves from one valid state to another. Constraints (NOT NULL, UNIQUE, FK) are enforced at commit time.
• Isolation — concurrent transactions behave as if they ran serially. What isolation level controls how visible uncommitted changes are.
• Durability — once committed, changes survive crashes thanks to the Write-Ahead Log (WAL).

Basic Transaction Syntax

BEGIN;

-- Transfer $100 from account 1 to account 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Verify before committing
SELECT id, balance FROM accounts WHERE id IN (1, 2);

COMMIT;   -- write to disk permanently

-- Or, if something went wrong:
ROLLBACK; -- undo everything since BEGIN

Isolation Levels

Isolation levels control what one transaction can see about changes made by other concurrent transactions.

*Postgres's REPEATABLE READ prevents phantom reads via MVCC snapshot isolation, which is stronger than the SQL standard requires.

-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... your queries ...
COMMIT;

Row-Level Locking

Use SELECT ... FOR UPDATE to lock specific rows so other transactions cannot modify them until yours commits.

BEGIN;

-- Lock the row while you process it
SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE;

-- Now safely update
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

-- FOR UPDATE SKIP LOCKED: skip rows already locked by another transaction
-- Great for job queues where multiple workers process tasks
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
SAVEPOINT before_payment;
INSERT INTO payments (order_id, amount) VALUES (1, 99.99);
-- If payment fails:
ROLLBACK TO SAVEPOINT before_payment;
-- Order record is still intact; COMMIT or ROLLBACK the transaction
COMMIT;