Transactions and ACID — when all or nothing is the point

Day 8 taught you connection pooling: the database gives you a warm connection, and you reuse it. But a connection is just a pipe. What flows through it — the actual work — is a transaction. Today we answer the question every backend engineer must answer in an interview: what is ACID, and what does each letter actually guarantee?

The problem: partial failures

You are building a banking app. Alice has $500. Bob has $300. Alice transfers $100 to Bob. This is two operations:

1. Debit Alice: UPDATE accounts SET balance = balance - 100 WHERE name = ‘Alice’
2. Credit Bob: UPDATE accounts SET balance = balance + 100 WHERE name = ‘Bob’

After step 1, Alice has $400 and Bob still has $300. $100 has vanished. If the database crashes between step 1 and step 2, the money is gone forever. Alice lost $100. Bob never received it. The bank’s books don’t balance.

This is the partial failure problem. Individual SQL statements can fail. The network can fail. The server can crash. If your operations are independent, each failure is isolated. But when your operations are related — debit here must be paired with credit there — a partial failure corrupts your data.

A transaction groups these two operations into a single unit of work. Either both succeed (the transaction commits), or neither happens (the transaction rolls back). If the crash comes after the debit but before the credit, the database undoes the debit on recovery. The money is never lost.

This is the promise. ACID is the four guarantees that make this promise precise.

ACID — the four guarantees

A — Atomicity (all or nothing)

A transaction is indivisible. Either every statement inside it succeeds, or none of them do. There is no “half-committed” state. If any statement fails — or the power goes out, or the network drops — the database rolls back everything the transaction did, as if it never started.

Atomicity is what saves Alice’s $100. The debit and the credit are one unit. Crash between them? The database’s recovery process reads the write-ahead log, sees the transaction never committed, and undoes the debit.

C — Consistency (the rules always hold)

A transaction takes the database from one valid state to another. “Valid” means all constraints hold: primary keys are unique, foreign keys point to real rows, CHECK constraints are satisfied. If a transaction would violate a constraint, the database rejects it and rolls back.

In our bank example, a CHECK (balance >= 0) constraint means Alice cannot overdraw. If Alice has $500 and tries to transfer $600, the UPDATE would set her balance to –$100. The constraint catches this, the statement fails, and the transaction rolls back. The database never enters an invalid state.

I — Isolation (concurrent transactions don’t interfere)

This is the hard one. When two transactions run at the same time, can they see each other’s uncommitted changes? Can they overwrite each other’s writes? Isolation is the degree to which concurrent transactions appear to run one-at-a-time.

Full isolation (as if transactions ran serially) is the safest, but it’s expensive — transactions must wait for each other, or some get aborted and must retry. Real databases offer a spectrum of isolation levels, trading correctness for performance. We’ll benchmark this tradeoff below.

D — Durability (committed data survives crashes)

Once a transaction commits, its changes are permanent. A crash, a power failure, a kernel panic — none of it can lose committed data. PostgreSQL achieves this with a write-ahead log (WAL): before any change is written to the actual data files, it’s written to the WAL on disk. If the database crashes, the recovery process replays the WAL to restore all committed transactions and undo all uncommitted ones.

The isolation tradeoff — four levels, four anomalies

Isolation is where the real engineering happens. The SQL standard defines four isolation levels. Each level prevents certain anomalies (bugs that concurrent transactions can produce) at the cost of performance.

The three anomalies, explained

1. Dirty read — Transaction A reads data that Transaction B has written but not yet committed. If B rolls back, A has read data that never “existed.” Nobody wants this. PostgreSQL doesn’t even allow it — Read Uncommitted behaves like Read Committed in PostgreSQL.
2. Non-repeatable read — Transaction A reads a row, Transaction B modifies it and commits, Transaction A reads it again and gets a different value. Under Read Committed (the default), this is allowed. Under Repeatable Read, A sees the same snapshot for its whole duration.
3. Phantom read — Transaction A runs a SELECT WHERE balance > 400 and gets 5 rows. Transaction B inserts a new row with balance=500 and commits. Transaction A runs the same query again and gets 6 rows. The “phantom” is the new row that appeared.

The benchmark: Read Committed vs Serializable

The theory says Serializable is safest. But what does it cost? We benchmarked it on a real PostgreSQL 16 server (Podman container, not WASM — real TCP connections, real process forking).

The latency difference (1.2×) is modest. The abort rate is the real story. Under Serializable, PostgreSQL uses SSI (Serializable Snapshot Isolation) — it tracks read/write dependencies between transactions and aborts any that could produce a non-serializable outcome. 14.1% of transactions were aborted with error code 40001 (serialization failure).

This means your application code must catch and retry:

async function transferWithRetry(client, fromId, toId, amount) {
  for (let attempt = 0; attempt < 5; attempt++) {
    try {
      await client.query("BEGIN");
      await client.query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
      // ... debit + credit ...
      await client.query("COMMIT");
      return; // success
    } catch (err) {
      await client.query("ROLLBACK");
      if (err.code === "40001") continue; // serialization failure → retry
      throw err; // different error, don't retry
    }
  }
  throw new Error("transfer failed after 5 attempts");
}

This is the price of full isolation. Read Committed needs no retry logic. Serializable needs a retry loop, backoff, and a max-attempts cap. Most applications — even banking — use Read Committed with explicit row locks (SELECT FOR UPDATE) and accept the theoretical anomalies in exchange for simpler code.

The syntax, in one place

-- Start a transaction
BEGIN;

-- Set the isolation level (optional, default is READ COMMITTED)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Do the work
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- If everything succeeded, make it permanent
COMMIT;

-- If something went wrong, undo everything
-- ROLLBACK;

Every statement between BEGIN and COMMIT is one transaction. If you forget BEGIN, each statement runs in its own implicit transaction (autocommit) — which means a crash between the debit and the credit will lose money. Always wrap related operations in an explicit transaction.

The connection to Day 8 (and Day 1)

Transfer question — click to reveal

Day 8 taught you that a connection pool hands out warm connections. Day 1 taught you that an index speeds up reads. Today: a transaction is what you do inside a borrowed connection.

Here’s the full picture: your app borrows a connection from the pool (Day 8), opens a transaction (BEGIN), runs queries that use indexes to find rows fast (Day 1), and either commits or rolls back. The pool reuses the connection for the next transaction. The index speeds up the SELECT inside the transaction. The transaction guarantees the UPDATEs are atomic.

One connection → many transactions over its lifetime. One transaction → many statements. One statement → uses indexes to find rows fast. Each layer solves a different problem: the pool solves connection cost, the transaction solves partial failure, the index solves lookup speed.

When does the transaction release the connection? Never — the transaction uses the connection. It’s the COMMIT or ROLLBACK that ends the transaction and lets the pool reclaim the connection. A long-running transaction holds a pool connection hostage. This is why you keep transactions short: open late, commit early, return the connection to the pool.

Quiz