Isolation Levels — Read Uncommitted to Serializable

Isolation level — কোনটা কখন

Read: ~30 min Hard 12 practice problems Live SQLite runner

1. Why Isolation Has a Dial

In Module 31 we learned that locking and MVCC can give us full serializability — every schedule behaves as if transactions ran one after another. But full serializability is expensive: writes queue, readers may block, and throughput drops. Real systems give the developer a dial labelled isolation level: pick how much safety you actually need, and the database will give you the best speed it can within that bound.

পুরোপুরি serializable হওয়া নিরাপদ, কিন্তু ধীর। তাই SQL স্ট্যান্ডার্ড ডেভেলপারকে একটি "isolation level" dial দিয়েছে — আপনি বেছে নিতে পারেন কতটা নিরাপত্তা দরকার, এবং ডেটাবেস সেই বাউন্ডারির মধ্যে সর্বোচ্চ গতি দেবে। প্রতিটি level কিছু anomaly-কে অনুমতি দেয়, কিছুকে নিষেধ করে — সঠিক level বাছাই করতে শেখাটাই এই মডিউল।

The SQL-92 standard defines four levels — from least safe and fastest, to safest and slowest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. The whole standard is built around: which of the four anomalies does this level forbid?

2. The Four Levels at a Glance

Each higher level forbids strictly more anomalies than the one below. The matrix you must memorise:

Isolation Level Dirty Read Non-Repeatable Read Phantom Read Cost / speed
READ UNCOMMITTED Possible Possible Possible Fastest, least safe
READ COMMITTED Prevented Possible Possible Default in PG, Oracle, SQL Server
REPEATABLE READ Prevented Prevented Possible* Default in MySQL InnoDB
SERIALIZABLE Prevented Prevented Prevented Slowest, totally safe

* In InnoDB and Postgres, REPEATABLE READ is implemented strongly enough to prevent most phantoms — see §6.

উপরের ম্যাট্রিক্স সহজে মনে রাখার মন্ত্র: "নিচ থেকে ওপরে গেলে সুরক্ষা বাড়ে কিন্তু গতি কমে"। প্রতিটি level আগের level-এর চেয়ে কঠোর — এক বা একাধিক নতুন anomaly বন্ধ করে দেয়। কোন level কোন anomaly বন্ধ করে এটি মুখস্ত থাকা চাকরির ইন্টারভিউতে আশীর্বাদ।

3. READ UNCOMMITTED — The Wild West

At READ UNCOMMITTED, a transaction can see another transaction's uncommitted writes. This is sometimes called a "dirty read" because the data may be rolled back. The level was originally meant for analytics: a finance dashboard that wants a fast, approximate count of today's orders and does not care if a few are rolled back later.

READ UNCOMMITTED-এ আপনি অন্য transaction-এর uncommitted (এখনো rollback হতে পারে এমন) data পড়ে ফেলতে পারেন। সাধারণত এটি real-time analytics বা rough dashboard-এর জন্য ব্যবহৃত হয়, যেখানে আপনি আনুমানিক মান পেলেও চলে। গুরুত্বপূর্ণ লেনদেনে এটি ব্যবহার করবেন না।

Setting it (different syntax across databases):

-- ANSI / SQL Server / PostgreSQL / MySQL
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- PostgreSQL note: PG silently treats READ UNCOMMITTED as READ COMMITTED.
-- MySQL InnoDB: actually allows dirty reads.

3.1 Demo (atomic version in SQLite)

SQLite does not expose dirty-read mode, but we can show what a dirty-read scenario looks like at the SQL level:

read_uncommitted.sql 
-- Dashboard counts pending orders. Under READ UNCOMMITTED, an
-- in-flight insert that may roll back can still inflate the count.
SELECT COUNT(*) AS pending_count
  FROM orders
 WHERE status = 'PENDING';

4. READ COMMITTED — The Sensible Default

At READ COMMITTED, every read sees only data that has already been committed. Dirty reads are gone. But within a single transaction, two reads of the same row can return different values, because another transaction may have committed between them — that is the non-repeatable read anomaly, still allowed at this level.

READ COMMITTED-এ আপনি কেবল committed data পড়েন — dirty read শেষ। তবে একই transaction-এর ভেতরে দুইবার পড়লে ভিন্ন ফলাফল আসতে পারে, কারণ মাঝখানে অন্য transaction commit করে যেতে পারে। এটিই non-repeatable read। PostgreSQL, Oracle, SQL Server-এর default এটি।

4.1 Visualising Non-Repeatable Read

Time → T1 read price=100 read price=120 ✗ T2 UPDATE price=120; COMMIT Same read, two values — non-repeatable read. এটি READ COMMITTED-এ অনুমোদিত, REPEATABLE READ-এ নয়। Figure 32.1 — Non-Repeatable Read scenario।

4.2 Atomic Patterns That Work Even at READ COMMITTED

The classic safe pattern is compute the new value inside a single statement, so you never carry a stale read across a write: 

-- A Daraz seller decrements inventory by 1 per order.
-- Even at READ COMMITTED, this is safe because the read
-- and write happen as ONE statement.
UPDATE stock
   SET qty = qty - 1
 WHERE sku = 'CHAL-1KG'
   AND qty > 0;
SELECT * FROM stock;

5. REPEATABLE READ — Stable Rows for the Whole Transaction

REPEATABLE READ guarantees that any row your transaction reads will return the same value if you read it again. This kills dirty read and non-repeatable read. The classic SQL-92 definition still allows phantom reads — i.e., new rows that appear in a range query — but most modern implementations close that gap too.

REPEATABLE READ-এ একটি transaction যে row পড়েছে, পরে আবার পড়লেও একই মান পাবে। তাই non-repeatable read বন্ধ। তবে SQL-92 অনুযায়ী range query-তে নতুন row "ভূত"-এর মতো দেখা দিতে পারে (phantom)। MySQL InnoDB-তে next-key locks এই phantom-ও বন্ধ করে দেয়, যা স্ট্যান্ডার্ড থেকে কঠোরতর।

5.1 The Phantom Read

Time → T1 (manager) SELECT COUNT(*)=5 SELECT COUNT(*)=6 ✗ T2 (HR) INSERT employee; COMMIT A new row "ghost" appeared between two identical queries. এটিই phantom read — strict REPEATABLE READ এই কেস বন্ধ করে দেয়। Figure 32.2 — Phantom Read scenario।

How databases implement REPEATABLE READ:

  • MySQL InnoDB — combines a snapshot with next-key locks on indexes, which lock the gaps between rows and so block phantom inserts. In practice InnoDB's REPEATABLE READ is essentially phantom-free.
  • PostgreSQL — implements REPEATABLE READ as snapshot isolation: each transaction reads from a single snapshot taken at start. Phantoms cannot appear in your reads.
  • SQL Server — at REPEATABLE READ, holds shared locks until commit on every row read, but does not add range locks; phantoms are technically possible.

6. SERIALIZABLE — The Gold Standard

SERIALIZABLE guarantees a result equivalent to some serial execution of all transactions. No anomaly is allowed. Implementations differ:

  • SQL Server / DB2 — Strict 2PL with range locks on indexes. Slow but correct.
  • PostgreSQL — Serializable Snapshot Isolation (SSI). Uses MVCC plus background detection of dangerous read-write cycles; if one is found, one transaction is aborted with a serialization-failure error and the application retries.
  • MySQL InnoDB — promotes every plain SELECT into SELECT ... LOCK IN SHARE MODE, which together with next-key locks gives a serial-equivalent schedule.
Always handle the abort. On Postgres SERIALIZABLE you must retry on SQLSTATE 40001 (serialization_failure). The application is part of the protocol — without retry the user just sees an error.

Postgres SERIALIZABLE-এ retry handler না লেখলে user একটি প্লেইন error দেখবে — এটি বাগ নয়, ফিচার। আপনি retry করলে দ্বিতীয় চেষ্টায় সাধারণত transaction পাশ করে যায়।

6.1 SQLite's Quirk: Always (Sort-of) Serializable

SQLite implements concurrency at the database level: only one writer at a time, and a writer blocks all readers (in rollback-journal mode) or runs alongside them (in WAL mode, see Module 33). The net effect is that any single SQLite connection is effectively serializable; you cannot weaken it. This is why most of our demos can show SERIALIZABLE behaviour without explicit syntax.

7. Snapshot Isolation and the Write-Skew Anomaly

Snapshot Isolation (SI) is the level offered by most MVCC databases when you ask for "REPEATABLE READ" or sometimes "SERIALIZABLE". It is almost serializable, but a famous gap remains: write-skew.

Consider a hospital rota: at least one doctor must be on call. Two doctors, Dr. Faria and Dr. Imran, both check the constraint at the same instant, both see "yes, the other is on call", both update their own row to off. Each transaction's own writes don't conflict with the other's, so SI lets both commit. The constraint is now violated — zero on-call doctors.

Write-skew: দু'জন একই বিজনেস-নিয়ম পড়ছে — "অন্তত একজন ডাক্তার অন কল থাকতে হবে" — দু'জনেই দেখছে অন্যজন অন কল আছে, এবং দু'জনেই নিজেকে অফ করে দিচ্ছে। যেহেতু কেউ একই row লিখছে না, snapshot isolation এই দুইটি commit-কেই অনুমতি দেয়। কিন্তু পুরো নিয়মটি ভেঙে যায়। এটি SI-এর সবচেয়ে বিখ্যাত ত্রুটি।

We can simulate the dangerous logic in SQLite (which runs serializably, so the bug won't actually occur, but we can model the pattern): 

-- Naive logic: if at least one OTHER doctor is on call, I can go off.
-- Imagine T_faria and T_imran each running this concurrently under SI:
SELECT COUNT(*) FROM doctors
 WHERE on_call = 1 AND id != 1;   -- Faria sees 1

UPDATE doctors SET on_call = 0 WHERE id = 1;

SELECT COUNT(*) FROM doctors
 WHERE on_call = 1 AND id != 2;   -- Imran (in his snapshot) STILL sees 1

UPDATE doctors SET on_call = 0 WHERE id = 2;

SELECT name, on_call FROM doctors;
-- Final state on a true SI system: BOTH off-call. Constraint violated.

Fixes for write-skew: (a) use SERIALIZABLE isolation, which detects the dangerous cycle and aborts one transaction; (b) take an explicit row lock with SELECT ... FOR UPDATE so the second transaction blocks until the first commits; (c) materialise the constraint into a row everyone has to update (so they conflict on writes).

8. SET TRANSACTION ISOLATION LEVEL — and Defaults

The standard syntax is the same across most databases: 

-- Per-transaction (recommended):
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your statements ...
COMMIT;

-- Per-session (PostgreSQL):
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL syntax variant:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

8.1 What Each Big DB Defaults To

DatabaseDefault levelImplementationNotes
PostgreSQLREAD COMMITTEDMVCC snapshot per statementSERIALIZABLE uses SSI; expect retries on 40001.
MySQL InnoDBREPEATABLE READMVCC + next-key locksPhantom-free in practice. The default on RDS, PlanetScale, etc.
SQL ServerREAD COMMITTEDStrict 2PL (or RCSI snapshot)ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON switches to MVCC mode.
OracleREAD COMMITTEDMVCC since 1980sSERIALIZABLE = full snapshot. No READ UNCOMMITTED at all.
SQLiteSERIALIZABLEWhole-DB lockStrict by construction; cannot weaken.
ইন্টারভিউতে এই টেবিলটা দেখে কাজে আসবে: PostgreSQL, Oracle, SQL Server-এর default READ COMMITTED; MySQL InnoDB-র default REPEATABLE READ; SQLite ডিফল্ট-এই SERIALIZABLE। প্রতিটি ডিফল্ট সেই database-এর design philosophy-র প্রতিফলন।

9. Which Level Should I Use?

✅ READ COMMITTED is enough when…

  • Most transactions are short.
  • You write atomic UPDATE … SET col = col ± n WHERE ….
  • Multi-row constraints are enforced by FK or single-row checks.

⚠️ Reach for SERIALIZABLE / explicit locks when…

  • You read a value, then decide based on it, then write somewhere else (write-skew shape).
  • You implement bookings, inventory, transfers across rows.
  • Auditors require provable correctness.

A useful rule of thumb: start at the database's default; promote a specific transaction to SERIALIZABLE only when its business logic crosses rows in a way READ COMMITTED can't protect. Promoting per-transaction is far better than promoting the whole database.

একটি ভালো নিয়ম: ডিফল্ট level-এই থাকুন; কোনো নির্দিষ্ট transaction যখন একাধিক row জুড়ে নিয়ম ভাঙার ঝুঁকিতে থাকে — তখনই সেটিকে SERIALIZABLE-এ তুলুন। পুরো database-এর level বদলে দেওয়া সাধারণত অপ্রয়োজনীয় ও costly।

10. Practice Problems

Mix of memorisation, concept and runnable. Work through each before opening the answer.

প্রতিটি প্রশ্ন আগে নিজে চেষ্টা করুন, তারপর Show Answer দেখুন। SQLite SERIALIZABLE-এ চলে, তাই runnable উদাহরণগুলো সঠিক ফলাফল দেখায়।
  1. Reproduce the four-by-four anomaly matrix from memory.
    স্মৃতি থেকে চারটি level আর তিনটি anomaly-র টেবিল আঁকুন।
    ✨ Show Answer (উত্তর দেখুন)

    READ UNCOMMITTED: dirty ✓, non-repeat ✓, phantom ✓ (all possible). READ COMMITTED: dirty ✗, non-repeat ✓, phantom ✓. REPEATABLE READ: dirty ✗, non-repeat ✗, phantom ✓ (in some impls). SERIALIZABLE: all ✗.

  2. Translate this English to SQL: "Run the following block at the strongest isolation, but only for this transaction."
    "শুধু এই transaction-এ সবচেয়ে কঠোর isolation চাই" — SQL-এ অনুবাদ করুন।
    ✨ Show Answer (উত্তর দেখুন) 
    BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;
  3. An e-commerce site reads order totals into a dashboard. Speed matters more than accuracy. Which level fits?
    একটি ই-কমার্স সাইট dashboard-এ order total দেখাচ্ছে — গতি বেশি গুরুত্বপূর্ণ। কোন level?
    ✨ Show Answer (উত্তর দেখুন)

    READ UNCOMMITTED (or READ COMMITTED on Postgres, since PG silently upgrades). The dashboard is approximate; an occasional dirty count rolling back a moment later is acceptable.

  4. Why does PostgreSQL silently treat READ UNCOMMITTED as READ COMMITTED?
    PostgreSQL কেন READ UNCOMMITTED-কে চুপচাপ READ COMMITTED-এ promote করে?
    ✨ Show Answer (উত্তর দেখুন)

    Because PG uses MVCC, every read already comes from a committed snapshot — there is literally no cheaper way to read uncommitted data. Honouring the standard's weaker level would not save any work.

  5. In SQLite, run the safe inventory decrement and verify it never goes below zero, even with two back-to-back invocations.
    SQLite-এ inventory decrement চালান, দুবার চালালেও negative হবে না নিশ্চিত করুন।
    ✨ Show Answer (উত্তর দেখুন) 
    UPDATE stock SET qty = qty - 1
 WHERE sku = 'CHAL-1KG' AND qty > 0;
UPDATE stock SET qty = qty - 1
 WHERE sku = 'CHAL-1KG' AND qty > 0;
SELECT * FROM stock;   -- qty = 0, never -1
  6. Define write-skew in your own words and give one Bangladeshi-context example.
    নিজের ভাষায় write-skew সংজ্ঞা দিন এবং একটি বাংলাদেশি উদাহরণ দিন।
    ✨ Show Answer (উত্তর দেখুন)

    Write-skew: two transactions read overlapping data, each writes to a non-overlapping row, but the combination violates a multi-row constraint. Example: a hospital in Dhaka requires at least one ENT specialist on duty per shift; two ENT doctors both check, both see "the other is here", both go off. Both transactions commit at SI; constraint broken.

  7. Suggest two ways to prevent write-skew without raising the global isolation level.
    Global isolation level না বাড়িয়ে write-skew বন্ধ করার দুটি উপায় বলুন।
    ✨ Show Answer (উত্তর দেখুন)

    (1) Add SELECT ... FOR UPDATE on every row whose value drives the decision — both transactions then conflict on the lock and serialize naturally. (2) Materialise the constraint into a single "guard" row that every doctor must update on shift-change; this turns a multi-row constraint into a single-row write, which any isolation level handles correctly.

  8. A Postgres app at SERIALIZABLE sometimes fails with SQLSTATE 40001. Is this a bug?
    Postgres SERIALIZABLE-এ কখনো 40001 error আসে — এটি কি বাগ?
    ✨ Show Answer (উত্তর দেখুন)

    No. SSI signals "I detected a dangerous read-write cycle and aborted one party to keep the schedule serializable". The application must catch 40001 (serialization_failure) and re-run the transaction. After 1-2 retries it almost always succeeds.

  9. Which databases default to READ COMMITTED, and which to REPEATABLE READ?
    কোন কোন database-এর default READ COMMITTED, কোনগুলো REPEATABLE READ?
    ✨ Show Answer (উত্তর দেখুন)

    READ COMMITTED → PostgreSQL, Oracle, SQL Server. REPEATABLE READ → MySQL InnoDB. SQLite is effectively SERIALIZABLE by construction.

  10. Run the SQLite block and explain why a single statement UPDATE ... = balance + ... is safe at every isolation level.
    কেন একক statement-এ relative-update প্রতিটি isolation level-এ নিরাপদ?
    ✨ Show Answer (উত্তর দেখুন) 
    UPDATE accounts SET balance = balance + 500 WHERE id=1;
SELECT * FROM accounts;

    A single statement holds an X-lock (or row version) for both its read and its write. There is no application-side gap where another transaction's commit could slip in, so even READ COMMITTED is enough.

  11. Predict the difference between InnoDB and SQL Server REPEATABLE READ on the phantom test.
    InnoDB এবং SQL Server-এর REPEATABLE READ phantom test-এ কিভাবে আলাদা?
    ✨ Show Answer (উত্তর দেখুন)

    InnoDB uses next-key locks: the gap between rows is locked, so concurrent inserts inside the range block — phantoms are prevented. SQL Server REPEATABLE READ holds shared locks only on read rows, not on the gaps; a concurrent insert that lands in the range can succeed and a phantom may appear on re-read.

  12. A bKash transfer reads a balance, validates it, then writes a new row to a transfer-history table. Without locks, which anomaly can bite?
    bKash transfer balance পড়ে validate করে, তারপর history table-এ insert করে। কোন anomaly এখানে বিপজ্জনক?
    ✨ Show Answer (উত্তর দেখুন)

    Write-skew. Two parallel transfers each see the same balance (e.g., 100৳), each independently passes the "balance ≥ 100" check, and both insert a debit. Account ends below zero. Fix: SELECT ... FOR UPDATE on the account row, or a single atomic UPDATE accounts SET balance = balance - 100 WHERE id = ? AND balance >= 100 that returns affected-row count.

Summary — Module 32

Isolation levels let you trade safety for speed. SQL-92 defines four: READ UNCOMMITTED (dirty/non-repeatable/phantom all allowed), READ COMMITTED (no dirty reads), REPEATABLE READ (no dirty/non-repeatable), and SERIALIZABLE (no anomalies at all). PostgreSQL/Oracle/SQL Server default to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ. Snapshot isolation, the level used by most MVCC databases, is almost serializable but allows the write-skew anomaly. Either escalate to SERIALIZABLE for that transaction or use SELECT ... FOR UPDATE to lock the row your decision depends on.

Isolation level হলো safety আর speed-এর dial — ৪টি level (READ UNCOMMITTED → SERIALIZABLE) মুখস্ত রাখুন এবং প্রতিটি কোন anomaly আটকায় তা মনে রাখুন। PostgreSQL/Oracle/SQL Server-এর default READ COMMITTED; MySQL InnoDB-এর default REPEATABLE READ। MVCC-ভিত্তিক snapshot isolation-এ write-skew থাকে — সমাধান হিসেবে ঐ transaction-কে SERIALIZABLE-এ তুলুন বা FOR UPDATE ব্যবহার করুন।

Next Module → Recovery, Logging & Checkpoints — power cut হলে database কীভাবে বাঁচে।

Previous (পূর্ববর্তী) Next: Recovery, Logging & Checkpoints