Transactions & Isolation

Code

-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Savepoint for partial rollback
BEGIN;
INSERT INTO orders(customer_id, total) VALUES (1, 50);
SAVEPOINT sp1;
INSERT INTO orders(customer_id, total) VALUES (2, -1);  -- may fail
ROLLBACK TO sp1;
INSERT INTO orders(customer_id, total) VALUES (2, 10);
COMMIT;

-- Set isolation level (must be before any query)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM products WHERE stock > 0;
-- business logic here
COMMIT;

Explanation

PostgreSQL transactions are atomic and support savepoints for partial rollbacks within a transaction. The default READ COMMITTED isolation level avoids dirty reads; SERIALIZABLE prevents all anomalies but may require retry on serialization failures. Always commit or rollback to release locks.

More PostgreSQL Snippets