PostgreSQL — Transactions

Basic Transaction

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

Rollback

BEGIN;
DELETE FROM users WHERE id = 1;
ROLLBACK; -- Undo the delete

Savepoint

BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product) VALUES (1, 'Widget');
ROLLBACK TO sp1; -- Undo only the insert
COMMIT;

Transaction Properties

Property	Description
Atomicity	All or nothing
Consistency	Valid state
Isolation	Concurrent access
Durability	Permanent

Isolation Levels

Level	Description
READ COMMITTED	Default
REPEATABLE READ	Consistent reads
SERIALIZABLE	Highest isolation

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Transaction
COMMIT;

Mini Practice

1. Start a transaction
2. Commit changes
3. Use rollback
4. Set isolation level

Up Next

Continue with JSON — JSON support.