ACID Transactions

Why ACID?

ACID ensures that changes to your data are correct and reliable even in the face of concurrency, crashes, and power loss. If money moves, inventory changes, or identities update, you likely need ACID.

The four guarantees

Atomicity: all operations in a transaction succeed or none do.
Consistency: the transaction moves the database from one valid state to another, preserving invariants.
Isolation: concurrent transactions do not see each other's partial results; levels govern how isolated they are.
Durability: once committed, data survives crashes (write‑ahead logs, fsync, replication).

Isolation levels and anomalies

READ UNCOMMITTED: may see dirty reads (rarely enabled in modern systems).
READ COMMITTED: no dirty reads; non‑repeatable reads may occur.
REPEATABLE READ: no non‑repeatable reads; phantom rows can occur (Postgres prevents many via MVCC + predicate locks).
SERIALIZABLE: as if transactions ran one at a time; strongest and safest.

Anomalies

Dirty read: T2 reads uncommitted changes of T1.
Non‑repeatable read: T1 reads a row; T2 updates it; T1 reads again and sees a different value.
Phantom read: T1 queries a set (e.g., WHERE amount > 100); T2 inserts a matching row; T1 queries again and sees a “phantom.”

MVCC in a nutshell

Multi‑Version Concurrency Control keeps multiple versions of a row. Readers don’t block writers and vice versa (mostly), enabling high concurrency without coarse locks.

Code: PostgreSQL transaction (SERIALIZABLE)

-- language-sql
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  UPDATE accounts SET balance = balance - 500 WHERE id = $from;
  UPDATE accounts SET balance = balance + 500 WHERE id = $to;
  INSERT INTO ledger(account_id, delta, reason) VALUES ($from, -500, 'transfer');
  INSERT INTO ledger(account_id, delta, reason) VALUES ($to, 500, 'transfer');
COMMIT;

Handling serialization failures

Under SERIALIZABLE, the database may abort your transaction to preserve correctness. You must retry with backoff.

// language-typescript
async function transfer(db, from, to, amount) {
  for (let attempt = 1; attempt <= 5; attempt++) {
    try {
      await db.tx(async (t) => {
        await t.none('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')
        await t.none('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from])
        await t.none('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to])
        await t.none('INSERT INTO ledger(account_id, delta) VALUES ($1, $2)', [from, -amount])
        await t.none('INSERT INTO ledger(account_id, delta) VALUES ($1, $2)', [to, amount])
      })
      return
    } catch (e) {
      if (!/could not serialize/.test(String(e))) throw e
      await sleep(20 * attempt) // backoff
    }
  }
  throw new Error('transfer failed after retries')
}

Two‑phase commit (2PC) and sagas

Distributed transactions across services/datastores are fragile and slow. Prefer local ACID transactions per service and coordinate with outbox + sagas for end‑to‑end consistency.

Constraints are part of consistency

-- language-sql
ALTER TABLE orders
  ADD CONSTRAINT total_non_negative CHECK (total_cents >= 0),
  ADD CONSTRAINT unique_paid UNIQUE (id, status) DEFERRABLE INITIALLY DEFERRED;

Durability details

WAL (write‑ahead‑log) fsync on commit; group commit to reduce latency.
Replication does not replace backups. Test restores regularly.

Analogy

Think of ACID like packaging a set of fragile items in a sealed box: either the whole box ships intact (atomic), the contents obey packing rules (consistent), other packers can’t touch the items mid‑pack (isolated), and once shipped it’s recorded and can’t be lost (durable).

FAQ

Isn’t SERIALIZABLE slow? It’s safer; with good indexing and short transactions it’s often fine. Use when correctness matters.
Do I need explicit locks? Rarely; rely on MVCC and constraints. Use SELECT ... FOR UPDATE for hot‑row coordination.

Try it

Use pgbench to compare p95 latency between READ COMMITTED and SERIALIZABLE under contention; observe serialization failures and retries.

*ACID Transactions