Transactions & Isolation

A transaction groups multiple SQL statements into a single atomic unit. Either all statements succeed and commit, or all are rolled back as if they never happened. PostgreSQL has excellent transaction support, with multiple isolation levels and MVCC enabling high concurrency.

Basic Transaction Commands

BEGIN;              -- start a transaction
COMMIT;             -- save all changes permanently
ROLLBACK;           -- undo all changes since BEGIN

-- Also valid
START TRANSACTION;  -- same as BEGIN
END;                -- same as COMMIT
ABORT;              -- same as ROLLBACK

Simple Transaction Example

-- Transfer $500 from account 1 to account 2
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

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

COMMIT;  -- makes both changes permanent atomically

If the server crashes between the two UPDATEs, PostgreSQL's WAL (Write-Ahead Log) ensures that on recovery, neither update is applied — the transaction is rolled back automatically.

SAVEPOINT — Nested Rollback Points

SAVEPOINTs let you roll back part of a transaction without losing everything:

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 150.00);

SAVEPOINT after_order;  -- mark this point

INSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 999);
-- Suppose product 999 doesn't exist — this might fail

ROLLBACK TO SAVEPOINT after_order;  -- undo only the order_items insert

-- The order itself is still there
INSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 5);  -- valid product

RELEASE SAVEPOINT after_order;  -- optional cleanup

COMMIT;

ACID Properties

Transaction Isolation Levels

Isolation levels control what a transaction can see from concurrent transactions. PostgreSQL supports all four SQL-standard isolation levels.

-- Set isolation level for the current transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN ISOLATION LEVEL SERIALIZABLE;

The Concurrency Anomalies

Isolation Level Comparison

*PostgreSQL treats READ UNCOMMITTED the same as READ COMMITTED — it never allows dirty reads. **PostgreSQL's MVCC prevents phantom reads even at REPEATABLE READ.

READ COMMITTED (Default)

Each statement sees only data committed before that statement began. Most common for web applications:

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- sees 1000

-- Session B commits: UPDATE accounts SET balance = 900 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;  -- NOW sees 900 (committed between statements!)
COMMIT;

This is fine for most operations but can surprise you in multi-statement transactions.

REPEATABLE READ

All statements in the transaction see a consistent snapshot from when the transaction began:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;  -- sees 1000

-- Even if another transaction commits balance = 900, this transaction still sees 1000

SELECT balance FROM accounts WHERE id = 1;  -- still sees 1000
COMMIT;

Use REPEATABLE READ for:

• Generating consistent reports across multiple queries
• Batch reads where you need a point-in-time snapshot

SERIALIZABLE

The highest isolation level. Transactions behave as if they ran one at a time, even though they run concurrently. PostgreSQL uses Serializable Snapshot Isolation (SSI), which is more sophisticated than two-phase locking:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT SUM(amount) FROM payments WHERE user_id = 1;
-- ... make decisions based on sum ...
INSERT INTO payments (user_id, amount) VALUES (1, 100);

COMMIT;  -- may fail with: ERROR: could not serialize access due to concurrent update

If PostgreSQL detects that the combined effect of two concurrent transactions cannot be achieved by any serial execution, one of them is aborted with a serialization error. The application must retry.

MVCC — How PostgreSQL Implements Isolation

PostgreSQL uses Multi-Version Concurrency Control (MVCC). Key points:

• Every row has hidden xmin (created by transaction ID) and xmax (deleted/updated by transaction ID) system columns
• Writers create new versions of rows rather than overwriting
• Readers see rows based on their transaction's snapshot
• Readers never block writers; writers never block readers

-- See the system columns (usually hidden)
SELECT xmin, xmax, id, name FROM products LIMIT 3;

xmax = 0 means the row is current (not deleted). xmax = 12400 means transaction 12400 deleted or updated it.

Locking — SELECT FOR UPDATE

For explicit row-level locking (to prevent another transaction from modifying a row you're working with):

-- Lock the row for update (other transactions that try to lock or update this row will wait)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Now update safely — no other transaction can modify this row until we commit
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

NOWAIT — Fail Instead of Wait

-- Fail immediately if the row is locked, rather than waiting
SELECT * FROM inventory WHERE product_id = 5 FOR UPDATE NOWAIT;
-- If locked: ERROR: could not obtain lock on row in relation "inventory"

SKIP LOCKED — Skip Already-Locked Rows

SKIP LOCKED is perfect for implementing job queues:

-- Worker picks up a job without waiting for locked jobs
BEGIN;
SELECT *
FROM job_queue
WHERE status = 'pending'
ORDER BY priority DESC, created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Process the job, then:
UPDATE job_queue SET status = 'done' WHERE id = :job_id;
COMMIT;

Multiple workers can run this query simultaneously. Each picks a different unlocked job — no collisions, no waits, no duplicate processing.

Autocommit

By default, PostgreSQL runs in autocommit mode when you're outside of an explicit transaction. Every statement is its own transaction:

-- This DELETE commits immediately — no rollback possible!
DELETE FROM orders WHERE order_date < '2020-01-01';

-- Safer: wrap in explicit transaction so you can check before committing
BEGIN;
DELETE FROM orders WHERE order_date < '2020-01-01';
SELECT COUNT(*) FROM orders;  -- sanity check
COMMIT;  -- only after you're sure