Database
Intermediate 14 min read Database

Transactions & ACID

Transactions make multi-step changes all-or-nothing. Understand ACID, isolation levels, MVCC, locks and savepoints — and how to keep money-moving workloads correct under concurrency.

ACIDtransactionisolationMVCClocksavepointdeadlockWAL
Prerequisites: SQL Fundamentals
Introduction

What is this?

A transaction is a unit of work that either applies completely or not at all. ACID — Atomicity, Consistency, Isolation, Durability — is the contract that makes this reliable. Databases deliver it with write-ahead logging for durability, MVCC for isolation and atomicity, and locks to serialize conflicting writers. Isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) trade strictness for concurrency.

Motivation

Why do you need it?

  • Money movement, inventory decrements and multi-row updates must never half-apply.
  • ACID gives applications a simple mental model: if a commit returns, the change is durable and visible.
  • Isolation levels let you choose exactly how much concurrency versus consistency a workload needs.
  • MVCC lets readers never block writers and writers never block readers, unlocking real parallelism.
  • Savepoints and ROLLBACK provide fine-grained recovery inside a long unit of work.
Think about it

Mental model

Imagine

Confirming a purchase at the register

Think of

A transaction

Every line can be reversed until you confirm the whole purchase. Once confirmed (committed), the receipt (the WAL) makes it final — the shop remembers it even if the lights go out.

Mechanics

How it works

  • BEGIN starts a transaction; the engine assigns a transaction ID and a snapshot.
  • Each statement writes new row versions (MVCC tuples) tagged with the transaction that created them.
  • Write locks prevent two transactions from overwriting the same row concurrently.
  • Before commit, the changes are appended to the write-ahead log (WAL) for durability.
  • COMMIT makes the transaction visible to other transactions according to the isolation level.
  • ROLLBACK discards the versions and releases locks, leaving no trace.

A transaction's lifecycle from BEGIN to COMMIT under READ COMMITTED.

BEGIN
UPDATE accounts SET balance = balance - 100
New row version
COMMIT
Error? ROLLBACK
Other transactions
Reference

Key commands

BEGIN;

Start an explicit transaction.

COMMIT;

Make all changes durable and visible to other transactions.

ROLLBACK;

Abort the transaction and discard every change.

SAVEPOINT sp1;

Mark a point you can roll back to within the transaction.

ROLLBACK TO SAVEPOINT sp1;

Undo changes made after the savepoint while keeping earlier work.

SHOW transaction_isolation;

Show the current isolation level for the session.

read committed
Hands-on

Examples

A money transfer with a savepoint

A money transfer with a savepoint
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
-- A follow-up fee fails because the row is missing.
-- Roll back only that statement, not the whole transfer.
SAVEPOINT after_transfer;
UPDATE accounts SET balance = balance - 10 WHERE id = 999;
-- ERROR: no row with id 999
 
ROLLBACK TO SAVEPOINT after_transfer;
COMMIT;

The debit and credit move together atomically. The failed fee statement would otherwise abort the entire transaction; a savepoint lets you discard just that statement and COMMIT the transfer intact.

Real world

In production

  • Payment and ledger flows wrap debit plus credit in one transaction so balances never diverge.
  • Inventory systems decrement stock and create the order atomically to prevent overselling.
  • Scheduled jobs run multi-statement updates in a transaction so a mid-way crash leaves consistent state.
  • High-write services stay on READ COMMITTED (the default) to maximize concurrency safely.
Guidelines

Best practices

  • Keep transactions short — long transactions hold locks and grow bloat, starving concurrency.
  • Do external I/O (HTTP, user input) before BEGIN, never inside the transaction.
  • Choose the weakest isolation level that is correct; escalate only when a real anomaly matters.
  • When contention is real, prefer SELECT ... FOR UPDATE over blind optimistic retries.
  • Treat deadlocks as retryable: re-run the whole transaction with bounded backoff.
Watch out

Common mistakes

  • Holding a transaction open across an HTTP call or slow API, locking rows for seconds.
  • Assuming a SELECT sees data written by another uncommitted transaction — it does not at any level.
  • Forgetting that REPEATABLE READ still allows serialization anomalies unless you use SERIALIZABLE with retries.
  • Catching an error mid-transaction and continuing, leaving the transaction in an aborted state.
Root cause analysis

Common issues

Error
ERROR: current transaction is aborted, commands ignored until end of transaction block
Root cause

A statement failed but the transaction continued running.

Fix

Run ROLLBACK (or ROLLBACK TO SAVEPOINT) to clear the aborted state, then retry the work.

Error
ERROR: deadlock detected
Root cause

Two transactions each hold a lock the other needs.

Fix

Acquire locks in a consistent order across the codebase and add bounded retry logic.

Error
ERROR: could not serialize access due to concurrent update
Root cause

Two SERIALIZABLE transactions conflicted on the same rows.

Fix

Retry the whole transaction, or reduce contention on the hot rows.

When things break

Debugging tips

  • Watch pg_locks to see which transactions hold locks and who is blocked behind whom.
  • Log deadlock reports and reorder statements so every path acquires locks in the same order.
  • Use SELECT txid_current() to tag transactions while debugging visibility issues.
  • Wrap suspect code in BEGIN; ...; ROLLBACK; to test it without persisting side effects.
Interviews

Interview checkpoint

Practice

Hands-on lab

Atomic Money Transfer
30 min · intermediate
In progress
Objectives
  • Create accounts and prove balance invariants
  • Run a transfer inside a single transaction
  • Observe visibility under READ COMMITTED
  • Recover from errors with savepoints and ROLLBACK

Atomic Money Transfer

30 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/3
1

Which ACID property guarantees a committed transaction survives a crash?

2

At the default READ COMMITTED level, a transaction can see rows written by other uncommitted transactions.

3

Two transactions deadlock on the same two rows. What does the database normally do?

Quick reference

Cheat sheet

Transaction control
BEGINStart a transaction
COMMITMake changes durable
ROLLBACKDiscard all changes
SAVEPOINT spSet a rollback point
ROLLBACK TO SAVEPOINT spUndo back to a point
SHOW transaction_isolationCurrent isolation level
Isolation levels
READ UNCOMMITTEDDirty reads possible
READ COMMITTEDDefault; per-statement snapshot
REPEATABLE READFixed snapshot per transaction
SERIALIZABLESerializable outcome, needs retries
Go deeper

Official documentation

PostgreSQL Transactions tutorial
docs
Transaction isolation levels
docs
Explicit locking
docs
MongoDB Transactions
docs