Transactions
BEGIN/COMMIT/ROLLBACK, ACID properties, isolation levels, deadlocks
Skills taught: pg.transactions, pg.savepoints, pg.isolation-levels, pg.deadlocks
Basic transaction syntax
-- Transfer $100 from Alice (id=1) to Bob (id=2)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Check the result (within the same transaction)
SELECT id, balance FROM accounts WHERE id IN (1, 2);
COMMIT; -- make it permanent
-- Or: ROLLBACK; -- undo everything since BEGIN
-- Error handling with savepoints
BEGIN;
INSERT INTO orders (user_id, total) VALUES (5, 99.99);
SAVEPOINT order_created;
INSERT INTO order_items (order_id, product_id) VALUES (currval('orders_id_seq'), 99); -- fails
ROLLBACK TO SAVEPOINT order_created; -- undo only to the savepoint
-- handle the error, try again with different product_id
COMMIT;
Isolation levels
-- PostgreSQL default: READ COMMITTED
-- Transactions see committed data at the time each statement runs
-- Most appropriate for most web applications
-- REPEATABLE READ: sees a snapshot from the start of the transaction
-- No non-repeatable reads or phantom reads
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 1000
-- Even if another transaction commits between here...
SELECT balance FROM accounts WHERE id = 1; -- still 1000 (same snapshot)
COMMIT;
-- SERIALIZABLE: strongest isolation — transactions appear to run one at a time
-- Use for financial calculations, inventory management
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your queries
COMMIT;
-- Isolation level anomalies:
-- Dirty read: read uncommitted data from another transaction (not possible in Postgres)
-- Non-repeatable read: same row gives different values in same transaction (possible in READ COMMITTED)
-- Phantom read: new rows appear matching a query (possible in READ COMMITTED)
Tip: Use READ COMMITTED (the default) for most operations. Use REPEATABLE READ when you need to do several reads that must be consistent with each other. Use SERIALIZABLE only when correctness is critical and you have retry logic for serialization failures.
Frequently Asked Questions
What is a deadlock?
A deadlock occurs when Transaction A holds a lock that B needs, and B holds a lock that A needs — both wait forever. PostgreSQL detects deadlocks and automatically cancels one transaction with ERROR: deadlock detected. Prevent deadlocks by always acquiring locks in the same order across transactions, and by keeping transactions short.