ACID, Isolation Levels, and the Anomalies They Prevent

Two requests read the same account balance. Both decide that a withdrawal is safe. Both commit, and the account violates a rule that each request checked correctly in isolation. Nothing crashed, no packet was corrupted, and every SQL statement succeeded. The defect lives in the history formed by otherwise valid statements.

Transactions exist to control such histories. ACID describes the contract of one transaction, while an isolation level defines which concurrent histories the database may expose. Choosing that level is therefore a correctness decision, not merely a performance setting. The right choice depends on the invariants an operation must preserve, the predicates it reads, and how the application responds when concurrency makes progress impossible.

ACID is four guarantees, not one magic property #

Atomicity means a transaction’s database effects commit as one unit or do not commit. It does not make an HTTP call, an email, or a message broker publish roll back automatically. Those effects need an outbox, idempotency, or a larger workflow protocol.

Consistency means a committed transaction moves the database from one valid state to another, assuming the transaction code and declared constraints encode the rules correctly. The database can enforce NOT NULL, foreign keys, uniqueness, and check constraints. It cannot infer that “at least one doctor must remain on call” unless that invariant is represented in a form it can enforce. Consistency in ACID is not the same concept as consistency in distributed replication models.

Isolation constrains what concurrent transactions can observe and which combined outcomes may commit. Perfect isolation is usually described as serializability: the committed result must be equivalent to some serial order of those transactions, even if their statements actually overlapped. A weaker level admits more histories to reduce blocking or aborts.

Durability means that, after the database acknowledges a commit, the result survives the failures covered by its durability configuration. Write-ahead logging, replication, and synchronous flush policies determine the exact failure envelope. Durability does not mean every replica has already applied the write, nor does it replace backups.

The key unit for reasoning is a history: an interleaving of reads, writes, commits, and aborts from multiple transactions. Isolation levels differ by which histories they reject, block, or allow.

Recognize anomalies as concrete interleavings #

Assume an accounts(id, balance) table. A dirty read occurs when T2 observes a write from T1 before T1 commits. If T1 later rolls back, T2 acted on a value that never existed in committed history.

Read committed and stronger levels prevent that schedule. A non-repeatable read is subtler: T1 reads a committed row twice, while T2 commits a change between those reads. T1 sees 100, then 80, despite remaining in one transaction. A phantom is the predicate version: T1 counts four open invoices, T2 inserts another qualifying invoice and commits, then the same query in T1 returns five. The rows previously read did not change; the result set did.

-- T1                                      -- T2
BEGIN;                                     BEGIN;
SELECT balance FROM accounts WHERE id=1;   -- returns 100
                                           UPDATE accounts
                                           SET balance=80 WHERE id=1;
                                           COMMIT;
SELECT balance FROM accounts WHERE id=1;   -- may return 80
COMMIT;

A lost update often appears in application-side read-modify-write logic. Both transactions read stock 10, both compute 9, and both write 9; two sales consume only one unit in storage.

An atomic statement such as UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0 avoids this simple stale calculation because the database evaluates and locks the row as part of the write. Optimistic version checks and SELECT ... FOR UPDATE are other options. Plain repeatable reads alone are not a universal cure; behavior on concurrent writes differs among engines.

Write skew affects disjoint rows and is therefore harder to catch with row-write conflicts. Suppose Alice and Bob are both on call, and the rule requires at least one doctor. Each transaction reads both rows, sees two doctors, and turns off its own row. Under snapshot isolation, both may commit because they write different rows, leaving nobody on call.

-- T1, Alice                               -- T2, Bob
BEGIN;                                     BEGIN;
SELECT count(*) FROM shifts                SELECT count(*) FROM shifts
 WHERE day='2025-10-01' AND on_call;        WHERE day='2025-10-01' AND on_call;
-- 2                                       -- 2
UPDATE shifts SET on_call=false            UPDATE shifts SET on_call=false
 WHERE day='2025-10-01' AND doctor='Alice'; WHERE day='2025-10-01' AND doctor='Bob';
COMMIT;                                    COMMIT;

This history is not serializable: in either serial order, the second transaction would see only one doctor and refuse its update. Preventing write skew requires serializable execution, explicit locking that covers the invariant, or a data model that lets a database constraint serialize the decision.

Isolation levels are contracts with engine-specific details #

The SQL standard names phenomena, but products implement levels with different combinations of locks and snapshots. In particular, PostgreSQL REPEATABLE READ is snapshot isolation and keeps ordinary predicate results stable, yet can permit write skew. Some engines use next-key locks at a similarly named level. Read the documentation for the exact engine and version.

“Prevented” does not mean the database always waits and succeeds. It may reject one participant with a serialization failure. That rejection is the mechanism preserving the contract. Conversely, a stable snapshot is not proof of serializability: both doctors saw a perfectly stable snapshot in the write-skew example.

Choose the weakest level only after naming the invariant and showing why every admitted history preserves it. Read committed is often appropriate for independent CRUD and atomic conditional updates. Repeatable read is useful when a transaction needs a stable analytical view, provided snapshot-specific anomalies are acceptable. Serializable is the clearest default for short, high-value operations whose rules span multiple rows or predicates.

Locks, MVCC, and SSI enforce different histories #

Lock-based concurrency control can hold shared locks for reads and exclusive locks for writes. Strict two-phase locking retains relevant locks until commit, making conflicting operations wait and producing a serializable order. Row locks protect existing rows; predicate, range, or next-key locks are needed when an invariant covers the absence of rows, such as “no overlapping reservation exists.” The costs are blocking, deadlocks, lock memory, and reduced concurrency around hot ranges.

Multi-version concurrency control (MVCC) stores multiple row versions. A reader chooses versions visible to its snapshot, so readers usually do not block writers. Writers still coordinate on conflicting rows, and old versions eventually require cleanup. MVCC is an implementation technique, not an isolation level: it can implement read committed, snapshot isolation, or serializable behavior.

Snapshot isolation prevents dirty reads and gives a transaction a stable snapshot. It also rejects some write-write conflicts. It does not, by itself, detect the cycle behind write skew because Alice and Bob update different row versions.

Serializable snapshot isolation (SSI) adds tracking of read-write dependencies. If T1 reads data later changed by T2, the system records an anti-dependency. A dangerous dependency structure can imply a cycle in the serialization graph, so the database aborts a participant before the cycle commits. SSI preserves much of MVCC’s reader/writer concurrency, at the cost of tracking state and serialization failures.

Explicit locking remains useful under MVCC. SELECT ... FOR UPDATE is appropriate when the rows whose values govern a decision are known and should be serialized. It is insufficient when the protected fact is a predicate and no qualifying row currently exists, unless the engine supplies a suitable range or predicate lock. Advisory locks can serialize a logical key, but they are safe only when every writer follows the same convention.

Retry the transaction and test the invariant #

Serializable transactions and lock-based designs can abort due to serialization conflicts or deadlocks. Treat those outcomes as expected control flow. Retry the entire transaction from a fresh snapshot, cap attempts, add jittered backoff, and surface persistent contention. Never retry arbitrary errors, and do not perform a non-idempotent external side effect inside a replayable callback.

interface TransactionClient {}

interface Database {
  transaction<Result>(options: {
    isolationLevel: 'serializable';
  }, operation: (client: TransactionClient) => Promise<Result>): Promise<Result>;
}

interface DatabaseError extends Error {
  code?: string;
}

const retryableCodes = new Set([
  '40001', // SQLSTATE serialization_failure
  '40P01', // PostgreSQL deadlock_detected
]);

const delay = (milliseconds: number) =>
  new Promise<void>((resolve) => setTimeout(resolve, milliseconds));

export async function withSerializableRetry<Result>(
  database: Database,
  operation: (client: TransactionClient) => Promise<Result>,
  maxAttempts = 5,
): Promise<Result> {
  for (let attempt = 1; ; attempt += 1) {
    try {
      return await database.transaction({ isolationLevel: 'serializable' }, operation);
    } catch (error) {
      const code = (error as DatabaseError).code;
      if (!code || !retryableCodes.has(code) || attempt >= maxAttempts) throw error;

      const ceiling = Math.min(25 * 2 ** (attempt - 1), 400);
      await delay(Math.floor(Math.random() * ceiling));
    }
  }
}

Adapt retry codes to the driver and engine; for example, MySQL exposes different deadlock and timeout identifiers. A timeout is retryable only when the operation’s commit status is known or the operation is idempotent. If transaction code must publish an event, write an outbox record in the same transaction and deliver it afterward.

Tests should orchestrate overlap rather than hope concurrency occurs. Use barriers so both transactions read before either writes, release them together, wait for commits or retries, then query the final state from a new transaction. Assert the business invariant, not merely that an expected error appeared. Run the scenario repeatedly against the real database engine because an in-memory substitute rarely models its lock and MVCC semantics.

For the on-call rule, assert count(on_call) >= 1 after every concurrent run. Also record retry counts and latency under load. A design that is correct but repeatedly exhausts retries on one hot key needs a different access pattern, such as a conditional atomic update, an invariant-owning row, or deliberate queueing.

The transaction boundary stops at the resource boundary #

A database transaction protects work coordinated by that database. It does not atomically include another service’s database, a cache, an email provider, or a message broker merely because the calls occur between BEGIN and COMMIT. Keeping a transaction open across network calls also extends lock lifetime and still cannot roll back the remote effect.

Use a transactional outbox to couple a local state change with durable intent to publish. Make consumers idempotent because delivery is normally at least once. For a multi-service business process, use a saga or another explicit distributed protocol and define compensation, intermediate states, and reconciliation. Two-phase commit is an option only when all resource managers and operational requirements support its availability and coupling tradeoffs.

Isolation also does not define replica freshness. A serializable write on the primary followed by a read from a lagging replica can appear to go backward unless the routing layer provides read-your-writes behavior. State the guarantee at the system boundary, not just at the SQL connection.

Takeaways #

• ACID properties are distinct; isolation governs concurrent histories, while consistency still depends on encoded invariants and correct transaction logic.
• Dirty reads, non-repeatable reads, phantoms, lost updates, and write skew are schedules. Reproduce the schedule before selecting a remedy.
• Read committed, repeatable read, and serializable are product contracts, not portable implementation recipes. Verify the engine’s exact semantics.
• Locks serialize conflicts directly; MVCC supplies snapshots; SSI makes snapshot execution serializable by detecting dangerous dependency structures and aborting work.
• Serialization failures and deadlocks require bounded whole-transaction retries, fresh reads, jitter, and idempotent handling of effects.
• Test final invariants under deliberately synchronized concurrency, and measure contention as well as correctness.
• Database isolation ends at its transaction boundary. Use outboxes, idempotency, sagas, and explicit consistency guarantees across distributed components.