The “I” in ACID stands for isolation, and textbooks describe it as “each transaction behaves as if it were running alone”. Real databases rarely give you that by default, because full isolation is expensive. Instead they offer levels, each of which lets certain kinds of interference through in exchange for more concurrency. The levels are easier to understand by starting with the interference itself. Below, two transactions, A and B, run at the same time against a small illustrative inventory and leave-management system.

The anomalies, one at a time

Dirty read. A reduces the stock of a product from 10 to 0 but has not committed. B reads the stock, sees 0, and shows “out of stock” to a customer. A then hits an error and rolls back. The stock was never really 0; B acted on data that never officially existed. A dirty read is reading another transaction’s uncommitted changes.

Non-repeatable read. B reads the price of a product as ₹500 at the start of a report. A updates it to ₹550 and commits. Later in the same transaction, B reads the price again and gets ₹550. Neither read was dirty; both values were committed. But within one transaction, the same row returned two different answers, so B’s report may contradict itself.

Phantom read. B counts orders placed today and gets 120. A inserts a new order and commits. B runs the same count again and gets 121. No existing row changed; a new row appeared that matches B’s condition. Phantoms are about the set of rows matching a query changing, not about a row’s values changing.

Lost update. A and B both read a stock count of 10. A sells 3 and writes 7. B sells 2 and writes 8. B’s write overwrote A’s. The correct answer was 5. This anomaly is not one of the three named in the SQL standard, but it is the one most likely to bite a typical application, because “read, compute in code, write back” is such a common pattern.

Write skew. A hospital rule says at least one doctor must be on call. Two doctors are on call, and both ask for leave at the same moment. Transaction A checks “is someone else on call?”, sees yes, and removes doctor 1. Transaction B does the same check, sees yes, and removes doctor 2. Each transaction updated a different row, so they never conflicted on the same data, yet the result breaks the rule: nobody is on call. Write skew happens when two transactions read overlapping data, make decisions on it, and write to different rows.

The four standard levels

The SQL standard defines four levels by which of the three classic anomalies they forbid.

LevelDirty readNon-repeatable readPhantom
Read UncommittedPossiblePossiblePossible
Read CommittedPreventedPossiblePossible
Repeatable ReadPreventedPreventedPossible
SerializablePreventedPreventedPrevented

Serializable is defined more strongly than “no phantoms”: the outcome must be equivalent to running the transactions one after another in some order. That stronger promise is what rules out lost updates and write skew.

What real databases do, which is not quite the table

The standard sets minimums. Vendors implement the levels in different ways, so the same name can mean different behaviour.

  • Defaults differ. PostgreSQL, Oracle and SQL Server default to Read Committed. MySQL’s InnoDB engine defaults to Repeatable Read. Code moved between databases can change behaviour without a single line changing.
  • PostgreSQL accepts Read Uncommitted but treats it as Read Committed, so dirty reads never happen. Its Repeatable Read gives each transaction a consistent snapshot, which also prevents phantoms, stronger than the standard requires. If two Repeatable Read transactions try to update the same row, the second one fails with a serialisation error rather than silently losing an update. Write skew, however, is still possible at that level. PostgreSQL’s Serializable uses a technique called Serializable Snapshot Isolation, which detects dangerous patterns and aborts one of the transactions.
  • MySQL InnoDB Repeatable Read gives plain reads a consistent snapshot, but locking reads and updates operate on the latest committed data and take gap locks to block inserts into scanned ranges. A plain read followed by an update can therefore still suffer a lost update unless the read is a locking read.
  • SQL Server traditionally implements Read Committed with shared locks, so readers can block behind writers. Turning on the database option for read committed snapshot switches it to a row-versioning approach closer to PostgreSQL’s.

These details change across versions and configurations, so check your database’s documentation for the version you run rather than relying on the standard’s table.

Choosing in practice

Most applications run at the default level and handle the dangerous cases explicitly. That is a sound approach as long as someone has actually identified the dangerous cases. Some patterns that cover most of them:

  1. Let the database do the arithmetic. An update that sets stock to “stock minus 3” in one statement avoids the lost update, because the read and the write happen atomically on the latest value. Add a condition that stock stays non-negative and check how many rows were affected.
  2. Lock what you read when you will write based on it. A locking read (FOR UPDATE in PostgreSQL and MySQL) makes other writers wait until you commit. For the doctors’ example, locking all on-call rows for that shift before checking prevents write skew.
  3. Use version numbers. Optimistic concurrency control stores a version column and makes each update conditional on the version being unchanged. A conflict shows up as zero rows updated, and the application retries or tells the user.
  4. Use Serializable for the few transactions that need it, and write the retry loop. Serializable transactions can fail with a serialisation error under contention; that is the database doing its job, and the correct response is to rerun the transaction from the start.
  5. Push invariants into constraints where possible. A unique constraint or a check constraint is enforced regardless of isolation level. Not every rule can be expressed that way, but the ones that can should be.

A quick way to reason about any transaction

Ask three questions. Does this transaction read something and then write based on what it read? If yes, could another transaction change what was read before this one commits? And if that happened, would the result be wrong, or merely slightly stale? A dashboard showing yesterday’s sales can tolerate a non-repeatable read. A wallet debit, a seat booking or a stock reservation cannot tolerate a lost update. The isolation level is a default; the answer to those three questions tells you where the default is not enough.

Written by the RP Strativa Team. The inventory, prices and hospital rota in the examples are illustrative.

Leave a Reply

Trending