Intermediate18 min read

Transactions & ACID

Use transactions, isolation levels, and locking for consistent concurrent access.

ACID Properties

Atomicity ensures all statements commit or none do. Consistency preserves constraints. Isolation hides concurrent transactions according to level. Durability persists committed data after crash via WAL.

PostgreSQL MVCC allows readers not to block writers and vice versa for most operations—understand snapshot visibility.

Each statement runs in implicit transaction unless BEGIN started
COMMIT makes changes visible; ROLLBACK discards them
Savepoints allow partial rollback within transaction

Transaction Control

BEGIN starts transaction block. END and COMMIT are synonyms in PostgreSQL. SET TRANSACTION ISOLATION LEVEL configures isolation for current transaction.

Use explicit transactions for multi-statement business operations. Keep transactions short to reduce lock contention and bloat from long-running xmin.

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

Isolation Levels

READ COMMITTED is default—each statement sees committed rows as of statement start. REPEATABLE READ sees consistent snapshot for transaction duration. SERIALIZABLE prevents anomalies via serialization failures requiring retry.

Phantom reads are prevented in REPEATABLE READ in PostgreSQL (stricter than SQL standard minimum).

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- transfer logic
COMMIT;

Deadlocks and Locking

Deadlocks detected automatically; one transaction aborted with error. SELECT FOR UPDATE locks rows for update. Advisory locks coordinate application-level mutexes.

Monitor pg_locks and log_lock_waits for contention hotspots.

Retry serialization_failure and deadlock_detected errors in apps
Lock order consistency prevents many deadlocks
SKIP LOCKED enables work queue consumers without blocking

SELECT * FROM inventory WHERE sku = 'ABC' FOR UPDATE;

Application Integration

ORMs wrap transactions around request handlers or service methods. Use @Transactional patterns carefully with async code and connection pool lifecycle.

Two-phase commit across databases is rare—prefer outbox pattern for distributed consistency.