Transactions & Concurrency

In multi-user databases, correct concurrent access is critical. This lesson covers ACID properties, transaction isolation levels, deadlocks, advisory locks, optimistic vs pessimistic locking, and how PostgreSQL's MVCC works under the hood.

---

ACID Properties

Every transaction in PostgreSQL guarantees:

Property	Description
Atomicity	All statements succeed or all are rolled back
Consistency	The database moves from one valid state to another
Isolation	Concurrent transactions do not interfere
Durability	Committed data survives crashes

Basic Transaction

BEGIN;

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

-- If everything is fine:
COMMIT;

-- If something goes wrong:
-- ROLLBACK;

Savepoints

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 250.00);

SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 999, 1);
-- Oops, product 999 does not exist
ROLLBACK TO SAVEPOINT before_items;

-- Continue with valid items
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 42, 2);
COMMIT;

---

Isolation Levels

PostgreSQL supports four isolation levels, though it implements them as three distinct behaviours:

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Serialisation Anomaly
READ UNCOMMITTED*	No	Possible	Possible	Possible
READ COMMITTED	No	Possible	Possible	Possible
REPEATABLE READ	No	No	No**	Possible
SERIALIZABLE	No	No	No	No

• In PostgreSQL, READ UNCOMMITTED behaves the same as READ COMMITTED. ** PostgreSQL's REPEATABLE READ also prevents phantom reads (unlike the SQL standard minimum).

Setting Isolation Level

-- Per transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;
-- ... other operations ...
COMMIT;

-- Per session
SET default_transaction_isolation = 'serializable';

READ COMMITTED (Default)

Each statement sees the latest committed data at the time the statement starts:

-- Transaction A                  -- Transaction B
BEGIN;                            BEGIN;
SELECT balance FROM accounts      UPDATE accounts
WHERE id = 1;                     SET balance = 500
-- Returns 1000                   WHERE id = 1;
                                  COMMIT;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 500 (sees B's commit)
COMMIT;

REPEATABLE READ

The transaction sees a snapshot from the start of the first query. If another transaction modifies the data, your UPDATE will fail:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Returns 1000

-- Meanwhile Transaction B updates and commits...

UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;

SERIALIZABLE

The strongest level. PostgreSQL detects when concurrent transactions could produce results inconsistent with any serial execution order:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE branch = 'London';
INSERT INTO audit (total) VALUES (12500.00);
COMMIT;
-- May fail with: ERROR: could not serialize access
-- Your application must retry the transaction

---

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs:

Transaction A: UPDATE accounts SET balance = 100 WHERE id = 1;  -- locks row 1
Transaction B: UPDATE accounts SET balance = 200 WHERE id = 2;  -- locks row 2
Transaction A: UPDATE accounts SET balance = 300 WHERE id = 2;  -- waits for B
Transaction B: UPDATE accounts SET balance = 400 WHERE id = 1;  -- waits for A → DEADLOCK

PostgreSQL detects deadlocks automatically and aborts one of the transactions.

Preventing Deadlocks

1. Lock resources in a consistent order: