Gyaan

Isolation Levels & Concurrency Anomalies

advanced isolation-levels concurrency mvcc transactions sql

An isolation level is a dial that controls how much one running transaction can see of another running transaction’s work. Turn it up and transactions feel more alone; turn it down and they run faster but can trip over each other.

In simple language — it’s the setting for “how strictly do we keep concurrent transactions from stepping on each other”.

The “I” in ACID (Isolation) isn’t all-or-nothing — it’s a spectrum. To understand the levels, we first need the bad things they’re protecting us from.

The three anomalies

When transactions overlap, three classic weirdnesses can happen. All of them come down to reading data that’s changing under our feet.

  • Dirty read — we read another transaction’s uncommitted change. It might roll back, and now we acted on data that never really existed. The worst one.
  • Non-repeatable read — we read a row, someone else commits an UPDATE to it, we read the same row again in the same transaction and get a different value. The row changed mid-transaction.
  • Phantom read — we run a range query (WHERE age > 30), someone commits a new row that matches, we run it again and get an extra row that wasn’t there before. The set of rows changed.

Think of it like re-reading a paragraph in a book someone else is editing: dirty = you read a pencilled note they later erase; non-repeatable = a word you read got swapped; phantom = a whole new sentence appeared.

The four isolation levels

The SQL standard defines four levels. Each one prevents more anomalies than the last — and costs a bit more performance. Here’s the map to memorize.

Level → anomalies still possible
Level
Dirty
Non-repeat
Phantom
  <div style="border-top: 1px solid var(--color-border); padding-top: 6px;">Read Uncommitted</div>
  <div style="border-top: 1px solid var(--color-border); padding-top: 6px; text-align: center; color: var(--color-tag-advanced);">yes</div>
  <div style="border-top: 1px solid var(--color-border); padding-top: 6px; text-align: center; color: var(--color-tag-advanced);">yes</div>
  <div style="border-top: 1px solid var(--color-border); padding-top: 6px; text-align: center; color: var(--color-tag-advanced);">yes</div>

  <div>Read Committed</div>
  <div style="text-align: center; color: var(--color-accent);">no</div>
  <div style="text-align: center; color: var(--color-tag-advanced);">yes</div>
  <div style="text-align: center; color: var(--color-tag-advanced);">yes</div>

  <div>Repeatable Read</div>
  <div style="text-align: center; color: var(--color-accent);">no</div>
  <div style="text-align: center; color: var(--color-accent);">no</div>
  <div style="text-align: center; color: var(--color-tag-intermediate);">maybe*</div>

  <div>Serializable</div>
  <div style="text-align: center; color: var(--color-accent);">no</div>
  <div style="text-align: center; color: var(--color-accent);">no</div>
  <div style="text-align: center; color: var(--color-accent);">no</div>
</div>
<div style="color: var(--color-text-muted); margin-top: 8px;">* The standard allows phantoms at Repeatable Read; Postgres (via MVCC) actually blocks them here.</div>

Reading the ladder:

  • Read Uncommitted — no protection. We can even see uncommitted data. Almost nobody uses it (Postgres doesn’t even really implement it).
  • Read Committed — we only ever see committed data. Kills dirty reads. This is the default in Postgres and Oracle.
  • Repeatable Read — a row we’ve read won’t change if we read it again. Kills non-repeatable reads too. Default in MySQL’s InnoDB.
  • Serializable — the strongest. Transactions behave as if they ran one at a time, in a line. Kills everything, including phantoms.

The trade-off

Higher isolation = more locking or more version-checking = less concurrency and more contention. Serializable is the safest but can force transactions to wait or even abort and retry.

The rule of thumb: use the lowest level that’s still correct for our use case. Most apps live happily at Read Committed. Reach for Serializable only when the logic genuinely can’t tolerate any anomaly — think money movements with complex invariants.

MVCC in one breath

Old databases enforced isolation with heavy locks — readers blocked writers and vice versa. Modern ones (Postgres, MySQL/InnoDB, Oracle) use MVCC — Multi-Version Concurrency Control.

In simple language — instead of locking a row, the database keeps multiple versions of it. Each transaction reads a consistent snapshot frozen at its start. So readers never block writers and writers never block readers — they just look at different versions. That’s why “select a snapshot” databases feel so much smoother under load.

-- set the level explicitly for one transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 'asha';
-- ... same row read again here returns the SAME value, guaranteed
COMMIT;

Interview soundbite

Isolation levels trade correctness against concurrency. The three anomalies are dirty read (reading uncommitted data), non-repeatable read (a row changes mid-transaction), and phantom read (new rows appear in a range). The four levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — each block progressively more, with Serializable making transactions behave as if run one-at-a-time. Use the lowest level that’s still correct; Read Committed is the common default. Modern engines get this cheaply via MVCC, giving each transaction a consistent snapshot instead of locking.

References