Databases
Advanced 16 min read Databases

ACID and Transactions

The guarantees your ETL assumes without realizing it

ACID (Atomicity, Consistency, Isolation, Durability) is the contract a single-node or single-partition database keeps. Isolation levels decide which anomalies your concurrent transactions can exhibit.

atomicityisolationdurabilityisolation levelphantomread committedserializable
Prerequisites: Transactions and Isolation CAP Theorem
Before we start

What you will learn

  • Define each ACID property with a failure example
  • Predict the outcome of two transactions under each isolation level
  • Choose an isolation level from a workload's tolerance for anomalies
Introduction

What is this?

ACID is a contract a database keeps around each transaction. Atomicity means the transaction all-or-nothing, Consistency means invariants hold, Isolation means concurrent transactions behave like they ran serially (or to the degree you choose), and Durability means an acknowledged commit survives crashes.

Motivation

Why do you need it?

  • Your ingestion process trusts that a failed bulk load rolled back cleanly (atomicity).
  • Warehouse snapshots rely on the same isolation level the analytics query thinks it is using.
  • REQUIRED motivator: choosing the wrong isolation level is a silent data-integrity bug, the scariest kind.
Think about it

Mental model

Imagine

Bank double-entry made with paper, one transaction at a time

Think of

ACID serializability

Paper clerks write each transaction fully or strike it out completely (atomic), and never let two tellers edit the same page at once (isolated). The database automates this at web scale.

Mechanics

How it works

  • Atomicity is implemented by undo (rollback segments) or versioned row garbage-collected on abort.
  • Isolation tracked by locks, MVCC snapshots, or both; PostgreSQL PostgreSQL uses MVCC snapshots plus explicit locks.
  • Anomalies: dirty read (read uncommitted), non-repeatable read (values change mid-transaction), phantom (rows appear), write skew (two writers commit a jointly-invalid pair of decisions).
  • Isolation ladder: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE (most strict but most expensive).
  • Postgres treats READ COMMITTED as default and documents that REPEATABLE READ stops phantom re-reads inside one snapshot.

Anomalies across the isolation ladder.

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Workload's choice
Reference

Key commands

SHOW default_transaction_isolation;

See the session's isolation level.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Raise the strictness for this transaction.

SELECT xmin, xmax, ctid FROM t;

Inspect Postgres version metadata that makes MVCC visible.

Hands-on

Examples

Where COMMIT lands

Where COMMIT lands
BEGIN;
UPDATE balances SET amount = amount - 100 WHERE id = 1;
-- until COMMIT:
--   - another session cannot see the -100 (atomicity + isolation)
--   - a crash here rolls everything back
COMMIT;  -- durability: acked only after the WAL is fsynced

Each property is a promise about a different moment: atomicity at the moment of abort, isolation during the flight, durability after the ack.

Real world

In production

  • A double-spend guard runs SELECT ... FOR UPDATE on the payment account before adjusting it.
  • ETL claims 'consistent snapshot' by wrapping a whole table extract in REPEATABLE READ.
  • A vaccine appointment system switches to SERIALIZABLE after two concurrent bookings both succeed.
Guidelines

Best practices

  • Pick the weakest isolation level that still satisfies your invariant; SERIALIZABLE is notationally nice but competes with latency.
  • Use SELECT ... FOR UPDATE for read-modify-write on a row instead of hoping REPEATABLE READ prevents lost updates.
  • Keep transactions short; long ones raise deadlock odds and pin MVCC snapshots.
Watch out

Common mistakes

  • Confusing transaction isolation with correct application-level locking (they solve different races).
  • Reading a snapshot for an hour then writing based on it — you may be acting on data that changed underneath.
  • Assuming REPEATABLE READ means no phantom rows in every database; dialect quirks differ.
When things break

Debugging tips

  • Reproduce a 'same query, different answers' report by isolating the two sessions and re-running at known isolation levels.
  • Check for FOR UPDATE and long transactions when 'leftover data' appears after a bulk load.
  • Watch MySQL's and Postgres's documented anomaly matrices; they are subtly different even at the same label.
Interviews

Interview checkpoint

Practice

Hands-on lab

Bob the SQL session hits isolation walls
40 min · advanced
In progress
Objectives
  • Reproduce dirty, non-repeatable, and phantom reads by hand
  • Watch isolation levels change the outcome
  • Demonstrate that SERIALIZABLE stops conflicts or forces a retry

Bob the SQL session hits isolation walls

40 min · advanced

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

Quick quiz

0/2
1

READ COMMITTED prevents dirty reads but allows non-repeatable reads.

2

Transaction Q reads a row as 100, waits, reads it again and sees 90 because another transaction committed an update. Which anomaly is this?

Go deeper

Official documentation

PostgreSQL transaction isolation
docs
MySQL isolation levels
docs
SQLite transactions
docs