intermediate~4h

Transactions — ACID, COMMIT, ROLLBACK, SAVEPOINT

A transaction is a logical unit of work that must execute completely or not at all. Transactions ensure database integrity even in the face of errors, crashes, and concurrent access.

Nearly every real application performs multi-step operations that must succeed or fail together (debit one account, credit another) — transactions are the mechanism that makes "all of these steps, or none of them" an actual guarantee the database enforces, rather than something the application has to hope for.

Without transactions, a crash or error partway through a multi-step operation leaves the database in an inconsistent, half-completed state — money debited but never credited, an order created but its inventory never decremented — with no built-in way to detect or undo it.

Transaction — a sequence of operations treated as one atomic unit. ACID — Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't see each other's uncommitted changes), Durability (a committed transaction survives a crash). COMMIT — makes a transaction's changes permanent. ROLLBACK — undoes every change made since the transaction began. SAVEPOINT — a named point inside a transaction you can roll back to, without undoing the entire transaction.

A transaction is a logical unit of work that must execute completely or not at all. Transactions ensure database integrity even in the face of errors, crashes, and concurrent access.

ACID Properties

Atomicity — all operations in a transaction succeed, or none do (all-or-nothing).
Consistency — a transaction brings the database from one valid state to another.
Isolation — concurrent transactions don't interfere with each other.
Durability — committed transactions survive system failures (written to disk/WAL).

Transaction control

BEGIN / START TRANSACTION — starts a transaction.
COMMIT — permanently saves all changes.
ROLLBACK — undoes all changes since BEGIN.
SAVEPOINT name — creates a checkpoint within a transaction.
ROLLBACK TO SAVEPOINT name — rolls back to the savepoint, not the beginning.
RELEASE SAVEPOINT name — releases a savepoint (cannot roll back to it anymore).

Autocommit: By default in SQL, each statement is its own transaction (autocommit=true). Explicit BEGIN..COMMIT groups multiple statements into one transaction.

PostgreSQL transaction implementation

BEGIN: acquires a transaction ID (XID). Starts tracking changes.
DML statements: changes written to shared buffer pool (memory).
Each change logged to WAL (Write-Ahead Log) on disk — durability.
COMMIT: WAL records flushed to disk. Transaction XID marked as committed in pg_clog (commit log).
Other sessions can see the committed rows (based on isolation level).
Background processes (checkpointer, bgwriter) flush buffer pool to data files.

ROLLBACK mechanism

PostgreSQL keeps the old row versions (MVCC — Multi-Version Concurrency Control).
On ROLLBACK: transaction's XID marked as aborted.
Changes remain in buffer pool temporarily but are invisible to other sessions.
Dead tuples cleaned up by VACUUM.

WAL durability

Every change is written to WAL before being applied.
WAL is written to sequential disk — fast.
On crash: PostgreSQL replays WAL to recover committed transactions.
fsync=on ensures WAL is on durable storage before COMMIT returns.

Step 1: BEGIN — start the transaction; get transaction ID.

Step 2: Execute DML statements — changes buffered; not yet committed.

Step 3: If all steps succeed — COMMIT; changes permanent and visible.

Step 4: If any step fails — ROLLBACK; all changes undone atomically.

Step 5: Use SAVEPOINT for complex workflows — partial rollback capability.

Step 6: Use READ-ONLY transactions for reports — allows optimizations.

Step 7: Keep transactions SHORT — long transactions hold locks, cause bloat.

Keep transactions short — long transactions hold locks and cause bloat in PostgreSQL (vacuum can't remove old versions).
Always handle the error path — explicitly ROLLBACK on exception; don't leave transactions open.
Read-only transactions with SET TRANSACTION READ ONLY — allows optimization and signals intent.
Use SAVEPOINT for complex multi-step operations — enables partial rollback without aborting everything.
Order operations consistently to prevent deadlocks — always lock resources in the same order.
Use explicit transaction boundaries — don't rely on autocommit for multi-statement operations.
Set lock_timeout to prevent indefinite waiting: SET lock_timeout = '5s'.
Batch large operations: avoid one giant transaction for 10M row UPDATE; use batches of 10,000.
Set statement_timeout as a safeguard against runaway queries.
Monitor pg_stat_activity for long-running transactions.

Leaving transactions open — application crashes without ROLLBACK; idle transaction holds locks. Fix: use connection pool with transaction management.
One large transaction for millions of rows — holds locks for minutes, causes massive WAL growth. Fix: batch operations.
Not handling ROLLBACK in application code — transaction left in failed state after exception.
Using transactions for SELECT-only queries without READ ONLY — implicit write lock potential.
Mixing DDL and DML in transactions (MySQL) — DDL causes implicit commit, breaking transaction semantics.
SAVEPOINT name conflicts — reusing a savepoint name overwrites the previous one.
Long transactions causing vacuum bloat — PostgreSQL cannot VACUUM rows visible to any open transaction.
Not using transaction for related multi-table operations — partial failure leaves inconsistent state.

Batch commits: accumulate multiple rows, commit in batches of 1000-10000 for bulk loads.
COPY vs INSERT: COPY (PostgreSQL) is 10-100x faster for bulk loading; uses single transaction.
Asynchronous commit (async_commit=on): PostgreSQL can return COMMIT before WAL is flushed. Faster but risk losing last few transactions on crash. OK for non-critical data.
Reduce transaction size: one transaction per logical business operation, not per HTTP request.
Monitor bloat: long transactions prevent VACUUM → table bloat → slower queries.
idle_in_transaction_session_timeout: kills sessions with idle open transactions.

A long-held open transaction can hold row/table locks far longer than intended, creating a denial-of-service-like effect on legitimate concurrent operations — always set and enforce a statement/transaction timeout in production so a stuck or forgotten transaction can't silently block the whole system.

Monitor for long-running transactions (pg_stat_activity in PostgreSQL) and alert past a reasonable threshold — an open transaction that's been running for minutes is either stuck, forgotten by application code, or actively blocking other work, and needs investigation regardless of which.

SET lock_timeout = '10s': prevents indefinite lock wait.
SET idle_in_transaction_session_timeout = '60s': kills idle-in-transaction sessions.
Monitor pg_stat_activity: SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'.
Use application-level retry logic for deadlock and serialization failures.
Alert on transaction duration > 30 seconds: likely a bug or runaway query.
Review pg_locks regularly for lock contention patterns.
Archive WAL for point-in-time recovery: archive_mode=on, archive_command set.

Simulate a bank transfer using BEGIN/COMMIT. Then simulate a failure (transfer to a non-existent account). Verify the ROLLBACK restores the balance correctly.
Use SAVEPOINT to implement a multi-step order process: create order → add items → update inventory. If inventory update fails, ROLLBACK TO the savepoint after order creation. Verify the order and items remain.
Intentionally create a deadlock: in two psql sessions, Session A locks table A then B; Session B locks B then A. Observe PostgreSQL detecting and resolving the deadlock.
Demonstrate WAL durability: start a transaction, insert a row, COMMIT. Kill the PostgreSQL process (pg_ctl stop -m immediate). Restart. Verify the row exists.

ACID: Atomicity (all-or-nothing), Consistency (valid states), Isolation (no interference), Durability (survives crashes).
BEGIN/COMMIT: group multiple statements into one atomic unit.
ROLLBACK: undo all changes; ROLLBACK TO SAVEPOINT: partial undo, transaction stays open.
WAL (Write-Ahead Log): changes logged to disk before commit — enables crash recovery.
Keep transactions short: long transactions hold locks and cause PostgreSQL MVCC bloat.
SAVEPOINT: create rollback points within a transaction for complex workflows.
SET lock_timeout + idle_in_transaction_session_timeout: essential production safeguards.

Want a visual for this concept?

Generate a diagram tailored to “Transactions — ACID, COMMIT, ROLLBACK, SAVEPOINT” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Isolation Levels — Dirty Read, Phantom Read, Non-Repeatable Read →← Back to all SQL chapters