We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
Controls how much one transaction can see changes made by other concurrent transactions. Ranges from Read Uncommitted (fastest, least safe) to Serializable (slowest, safest).
In short
An isolation level is a database setting that controls how much one transaction is allowed to see uncommitted or in-progress changes made by other transactions running at the same time. The four standard SQL levels, from weakest to strongest, are Read Uncommitted, Read Committed, Repeatable Read, and Serializable, and each one trades away a bit of concurrency to prevent more kinds of read anomalies.
In short
An isolation level is a database setting that controls how much one transaction is allowed to see uncommitted or in-progress changes made by other transactions running at the same time. The four standard SQL levels, from weakest to strongest, are Read Uncommitted, Read Committed, Repeatable Read, and Serializable, and each one trades away a bit of concurrency to prevent more kinds of read anomalies.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Isolation Level across a run of lessons, not one page. These 4 alone are about 151 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
Snapshot isolation, explained: each transaction reads one frozen view of the data. How MVCC builds it, the write skew it allows, and the Postgres fix.
Transactions execute as if they ran one at a time, the gold standard of isolation.
Renaming one category rewrote 200,000 rows in a flat table and 1 in a normalised one. A partial update left the flat table holding two answers to one question, and the join it costs measured 1.34 times.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with ACID on a Lakehouse: Two Writers, One Table, Who Winspay once,
yours for life
When two or more transactions run against the same rows at the same time, they can step on each other. Isolation level is the knob that decides how much they are allowed to interfere. A weaker level lets transactions run faster and with less locking, but they can see messy intermediate data. A stronger level makes them behave as if they ran one after another, at the cost of speed and more conflicts.
The SQL standard defines four levels by which read anomalies they forbid. A dirty read is reading a row another transaction wrote but has not committed yet. A non-repeatable read is reading the same row twice in one transaction and getting two different values because someone else committed an update in between. A phantom read is running the same query twice and getting a different set of rows because someone else inserted or deleted matching rows.
Read Uncommitted allows all three. Read Committed forbids dirty reads. Repeatable Read forbids dirty and non-repeatable reads. Serializable forbids all three plus guarantees the end result is the same as if transactions ran one at a time.
There are two main strategies. The classic approach is locking: a transaction takes shared locks to read and exclusive locks to write, and holds them for varying lengths of time depending on the level. Stronger levels hold locks longer, which blocks other transactions and reduces throughput.
The modern approach most engines use is Multi-Version Concurrency Control, or MVCC. Instead of blocking readers, the database keeps multiple versions of each row. When a transaction reads, it sees a consistent snapshot of the data as of a certain point in time, so readers never block writers and writers never block readers. PostgreSQL and Oracle both work this way.
One trap is that the same level name behaves differently across engines. PostgreSQL Repeatable Read is actually snapshot isolation and prevents phantom reads, which the standard does not require. Oracle has no true Serializable in the lock-based sense and uses snapshot isolation for its strongest practical level. MySQL with InnoDB defaults to Repeatable Read, while PostgreSQL and SQL Server default to Read Committed. Always check what your specific database does rather than trusting the label.
Most applications run fine on Read Committed, which is why it is the default in PostgreSQL, SQL Server, and Oracle. You get protection from reading garbage that was never committed, and you keep high concurrency. The cost is that values can shift under you between two reads in the same transaction.
Step up to Repeatable Read or Serializable when correctness depends on a stable view of data across multiple statements: think reporting that sums many tables, inventory checks before a purchase, or a transfer that reads a balance then writes it back. Serializable is the safest and the easiest to reason about because the database guarantees no anomalies at all, but under contention it produces serialization failures that your application must catch and retry.
Read Uncommitted is rarely a good idea on a transactional system because dirty reads can show data that gets rolled back. The general rule is to use the weakest level that still keeps your application correct, then handle the conflicts that the stronger levels would have prevented either with explicit locking or with retry logic.
Say you move 100 dollars from account A to account B. The transaction reads A's balance, checks it is at least 100, subtracts 100 from A, and adds 100 to B. Now imagine two transfers from account A run at the same time and A only has 100 dollars.
Under Read Committed, both transactions can read the balance as 100, both see it as sufficient, and both proceed, leaving A overdrawn by 100. This is a check-then-act race on a single row. Read Committed did its job of hiding uncommitted data, but it did not stop two transactions from making decisions on the same stale snapshot.
Under Serializable, the database detects that these two transactions could not have happened in any serial order without conflict, aborts one of them, and you retry it. The retried transaction now sees the updated balance, finds it insufficient, and correctly rejects the second transfer. This is exactly the class of bug that picking the right isolation level prevents.
PostgreSQL
Uses MVCC; defaults to Read Committed and offers true Serializable Snapshot Isolation that detects conflicts and aborts one transaction.
MySQL InnoDB
Defaults to Repeatable Read using MVCC plus gap locks to prevent phantom reads on range queries.
Oracle Database
Relies on MVCC snapshots; its strongest practical mode is snapshot isolation rather than lock-based Serializable.
Amazon Aurora and RDS
Inherit the isolation semantics of their engine (PostgreSQL or MySQL) and expose the same level settings per transaction.
PostgreSQL, SQL Server, and Oracle default to Read Committed. MySQL with the InnoDB engine defaults to Repeatable Read. Always confirm for your specific engine because the same name can behave differently.
It gives the strongest correctness guarantee, behaving as if transactions ran one at a time. But it produces serialization failures under contention, so your application must catch those errors and retry. It also lowers throughput, which is why most systems use Read Committed and only escalate where needed.
A non-repeatable read is when you read the same row twice and get different values because another transaction updated and committed it. A phantom read is when you run the same query twice and get a different set of rows because another transaction inserted or deleted rows that match your filter.
No. MVCC is the mechanism many databases use to implement isolation efficiently by keeping multiple row versions so readers do not block writers. You still choose a level, and that level decides which snapshot a transaction sees and which anomalies are prevented.
PostgreSQL implements Repeatable Read as snapshot isolation, which gives each transaction a single consistent snapshot for its whole duration. That snapshot also blocks phantom reads, even though the standard only requires Serializable to do so.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
Transaction
A sequence of database operations treated as a single atomic unit. Either all operations succeed (commit) or none of them do (rollback).
Serializability
A guarantee that concurrent transactions produce the same result as if they were executed one at a time in some serial order. The gold standard for database transaction isolation.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
This page explains the idea. The course covers it step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
See the System Design Masterclass coursecourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Isolation Level in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
