DB Isolation Levels (ACID) Explained — From Dirty Reads to Serializable (with Spring Boot examples)
Picture this: you and your friend are trying to book the last movie ticket on a Saturday night. You both open the app, see “1 seat left”, and smash the “Book Now” button at the same time.
In one universe: only one of you gets it (correct).
In another universe: both of you get “Booking confirmed” (😬 oversold seat).
In the worst universe: the app charges both of you and then refunds one later (support ticket nightmare).
That “multiple people doing things at the same time” problem is what transaction isolation is trying to make boring, predictable, and safe.
ACID in 2 minutes (but we’ll zoom into “I”)
ACID is a set of guarantees databases try to provide for transactions:
A — Atomicity: All or nothing (either the booking is done, or it’s as if it never happened).
C — Consistency: Constraints/invariants hold (no negative inventory, no duplicate unique keys, etc).
I — Isolation: Concurrent transactions shouldn’t step on each other in surprising ways.
D — Durability: Once committed, it stays committed even if the DB crashes.
This blog is about Isolation: what each isolation level means, what bugs it prevents, what it allows, and how to use it correctly in Spring Boot.
The enemy: “Concurrency anomalies” (the weird bugs isolation fights)
The SQL standard defines common “phenomena” (problems) that can occur when transactions overlap. PostgreSQL docs summarize them clearly: dirty read, non-repeatable read, phantom read, and a broader category called serialization anomaly. (PostgreSQL)
Let’s make them super concrete.
1) Dirty Read
You read data that another transaction wrote but hasn’t committed yet. If they rollback, you read something that never truly existed.
Real-world analogy: your friend tells you “I booked the seat!” before payment completes, and you celebrate… then their payment fails and the seat goes back.
(PostgreSQL explicitly defines dirty read that way.) (PostgreSQL)
2) Non-Repeatable Read
You read a row twice in the same transaction and get different values, because another transaction committed an update in between.
Analogy: you refresh your cart total and it changed while you’re checking out.
3) Phantom Read
You run the same query twice and the set of rows changes (new rows appear/disappear) because another transaction inserted/deleted rows that match your condition.
Analogy: you search “available seats” twice and new seats show up (or disappear), even though you didn’t change filters.
4) Serialization anomaly (the “boss-level” problem)
Even if you don’t see dirty/non-repeatable/phantoms, the final result of multiple transactions can still end up inconsistent with any serial (one-at-a-time) order. PostgreSQL calls this a serialization anomaly. (PostgreSQL)
This is the one that bites people doing “check-then-act” logic.
The 4 isolation levels (the dial: efficiency ⚡ vs consistency 🛡️)
Here’s the big picture: as you go up isolation levels, you typically get stronger correctness but you may pay with lower concurrency / more locking / more retries.
More performance / concurrency ⚡
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
More correctness / consistency 🛡️
Important detail: databases implement these slightly differently. PostgreSQL, for example, supports the four standard names, but internally implements only three distinct levels: in PostgreSQL, READ UNCOMMITTED behaves like READ COMMITTED. (PostgreSQL)
So think of isolation as:
✅ the contract you ask for + ⚙️ the implementation your DB actually provides.
Isolation level 1: READ UNCOMMITTED
What it promises
Almost nothing. Dirty reads can happen in theory.
PostgreSQL lists “dirty read allowed” for READ UNCOMMITTED in the standard table, but also notes that PostgreSQL does not actually allow dirty reads, because it maps READ UNCOMMITTED to READ COMMITTED. (PostgreSQL)
MySQL/InnoDB says READ UNCOMMITTED can cause inconsistent reads (dirty reads), where an earlier version of a row might be used. (MySQL)
What can go wrong
Dirty reads
Non-repeatable reads
Phantom reads
Weird app logic based on “data that never commits”
When would anyone use this?
Mostly analytics/bulk reporting where you truly don’t care if the numbers are slightly off mid-run, and you want minimal locking overhead. Even MySQL’s docs describe relaxing consistency for bulk reporting scenarios. (MySQL)
Toy example (dirty read)
Session A:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
Session B:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- not committed yet
If A can see B’s uncommitted update → dirty read.
(Some DBs won’t allow this; PostgreSQL won’t.) (PostgreSQL)
Efficiency vs consistency
Efficiency: ⭐⭐⭐⭐⭐ (usually)
Consistency: ⭐ (very weak)
Isolation level 2: READ COMMITTED
What it promises
You never read uncommitted changes. That prevents dirty reads.
SQL Server’s documentation says READ COMMITTED prevents dirty reads but allows non-repeatable reads and phantoms. It also states READ COMMITTED is the default isolation level in SQL Server. (Microsoft Learn)
PostgreSQL explains that in READ COMMITTED, each SELECT sees a snapshot of the database as of the start of that statement, and it is also PostgreSQL’s default isolation level. (PostgreSQL)
The “statement snapshot” mental model
In READ COMMITTED:
Query 1 sees “the world as of query start”
Query 2 (later) sees “the world as of query 2 start”
So, two reads inside one transaction can legitimately see different committed data.
PostgreSQL explicitly calls out that two successive SELECTs can see different data if other transactions commit between them. (PostgreSQL)
What can go wrong (classic)
Non-repeatable reads
Phantoms
Lost updates (not always prevented by isolation level alone)
Real-world analogy
You’re reading a live scoreboard. You won’t see unfinished edits, but you can see the score change every time you look.
Toy example (non-repeatable read)
Session A (READ COMMITTED):
BEGIN;
SELECT price FROM products WHERE id = 10; -- returns 100
-- do some work...
SELECT price FROM products WHERE id = 10; -- might return 120 now
COMMIT;
Session B:
UPDATE products SET price = 120 WHERE id = 10;
COMMIT;
Totally allowed under READ COMMITTED.
Efficiency vs consistency
Efficiency: ⭐⭐⭐⭐
Consistency: ⭐⭐⭐
This is why READ COMMITTED is a common default: fast enough, simple enough, safe enough for many apps. PostgreSQL even notes it’s adequate for many applications and “fast and simple to use.” (PostgreSQL)
Isolation level 3: REPEATABLE READ
What it promises (high level)
Within a transaction, you get a stable view of data: if you read something once, you read it again and it’s the same (no non-repeatable reads).
PostgreSQL: in REPEATABLE READ, a transaction sees only data committed before the transaction began, and successive SELECTs see the same snapshot. (PostgreSQL)
MySQL/InnoDB: in REPEATABLE READ (InnoDB’s default), consistent reads within the same transaction read the snapshot established by the first read, making multiple plain SELECTs consistent with each other. (MySQL)
The huge nuance (a.k.a. where people get tricked)
Many databases implement REPEATABLE READ using MVCC “snapshot isolation” semantics.
PostgreSQL explicitly says its Repeatable Read is implemented using Snapshot Isolation, and warns that this stable view is not necessarily consistent with some serial execution of concurrent transactions (i.e., serialization anomalies can still happen). (PostgreSQL)
So:
✅ stable reads
❌ not guaranteed “as-if-one-at-a-time” correctness for every business rule
Phantom reads: depends on DB behavior
PostgreSQL’s table indicates its REPEATABLE READ implementation does not allow phantom reads, which is stronger than the minimum SQL standard requirement. (PostgreSQL)
In MySQL/InnoDB, the story involves MVCC + locking strategy. For locking reads/updates/deletes with range scans, InnoDB can use gap locks / next-key locks to block inserts into scanned ranges. (MySQL)
What can still go wrong (the sneaky one): write skew
Classic example: “At least one doctor must be on call.”
Two transactions both read “Doctor A on call = true, Doctor B on call = true”.
Each transaction turns one doctor off. Both commit.
Now nobody is on call.
That’s a serialization anomaly type problem. PostgreSQL says REPEATABLE READ can still allow serialization anomalies and that applications must be prepared to retry due to serialization failures. (PostgreSQL)
Efficiency vs consistency
Efficiency: ⭐⭐⭐
Consistency: ⭐⭐⭐⭐
(Stable view, but still not fully serial for all business constraints.)
Isolation level 4: SERIALIZABLE
What it promises
Serializable means: the effect of concurrent Serializable transactions is equivalent to running them one at a time in some order (serially). PostgreSQL quotes the SQL standard definition in its docs. (PostgreSQL)
How databases achieve it (conceptually)
Two broad approaches (conceptually; implementations differ):
Locking (block others so the schedule becomes serial)
Optimistic monitoring + abort/retry (let them run, detect dangerous patterns, and fail one transaction)
PostgreSQL explains that its SERIALIZABLE works like REPEATABLE READ plus monitoring for conditions that could cause serialization anomalies, and such detection triggers a serialization failure. (PostgreSQL)
The price you pay: retries
With Serializable (and often Repeatable Read), you must be ready to retry.
PostgreSQL explicitly states that both REPEATABLE READ and SERIALIZABLE can produce errors intended to prevent serialization anomalies, and that failures will have SQLSTATE 40001 (serialization_failure). (PostgreSQL)
Efficiency vs consistency
Efficiency: ⭐⭐ (sometimes ⭐, depending on workload/DB)
Consistency: ⭐⭐⭐⭐⭐
Serializable is your “make it correct even if it hurts” option — but you must design your application to handle retries gracefully.
A clear “Efficiency ⚡ vs Consistency 🛡️” scorecard
| Isolation Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads | Serialization Anomalies | Typical Efficiency | Typical Consistency |
|---|---|---|---|---|---|---|
| READ UNCOMMITTED | ✅ possible | ✅ possible | ✅ possible | ✅ possible | ⚡⚡⚡⚡⚡ | 🛡️ |
| READ COMMITTED | ❌ prevented | ✅ possible | ✅ possible | ✅ possible | ⚡⚡⚡⚡ | 🛡️🛡️🛡️ |
| REPEATABLE READ | ❌ prevented | ❌ prevented | depends / DB-specific | ✅ possible | ⚡⚡⚡ | 🛡️🛡️🛡️🛡️ |
| SERIALIZABLE | ❌ prevented | ❌ prevented | ❌ prevented | ❌ prevented | ⚡⚡ | 🛡️🛡️🛡️🛡️🛡️ |
PostgreSQL’s table and notes show interesting DB-specific strength: it maps READ UNCOMMITTED → READ COMMITTED, and its REPEATABLE READ doesn’t allow phantom reads. (PostgreSQL)
Defaults in popular databases (because defaults silently shape your app)
When you don’t set isolation explicitly, your app inherits the DB default.
PostgreSQL default: READ COMMITTED. (PostgreSQL)
MySQL/InnoDB default: REPEATABLE READ. (MySQL)
SQL Server default: READ COMMITTED (but behavior differs depending on
READ_COMMITTED_SNAPSHOT). (Microsoft Learn)Microsoft also notes Azure Synapse uses READ UNCOMMITTED by default. (Microsoft Learn)
Oracle default: READ COMMITTED. (Oracle Docs)
Oracle also enforces statement-level read consistency (each query sees committed, consistent data for a point in time). (Oracle Docs)
How this maps to Spring Boot (@Transactional) — Isolation vs Propagation
Spring gives you two key knobs:
Isolation: what guarantee you want from the database.
Propagation: how your transaction behaves when one transactional method calls another.
1) Isolation in Spring
Spring’s @Transactional supports isolation values via the Isolation enum. (Home)
Example:
@Transactional(isolation = Isolation.READ_COMMITTED)
public void checkout(...) { ... }
And remember:
Isolation.DEFAULTmeans “use the database default.” (Home)
2) Propagation in Spring (why it matters a LOT)
Spring’s transaction propagation explains whether a method:
joins an existing transaction
starts a brand-new one
suspends one
runs without one
etc.
Spring’s reference guide highlights the difference between physical and logical transactions and how propagation affects that. (Home)
The biggest gotcha:
Isolation settings only apply if Spring actually starts a new physical transaction.
Spring’s TransactionDefinition docs state isolation/timeout settings won’t be applied unless a new transaction gets started, and only REQUIRED, REQUIRES_NEW, and NESTED can cause that. (Home)
So if your method joins an existing transaction, its local isolation setting might be ignored.
Spring’s propagation docs say that when participating in an outer transaction, it silently ignores local isolation/timeout/readOnly by default, unless you configure validation. (Home)
Spring Boot examples that feel like real life
Example A — “Seat booking” done safely (even at READ COMMITTED)
Goal: ensure you don’t oversell the last seat.
Best pattern: do an atomic update with a condition and check affected rows (works great with READ COMMITTED).
@Service
public class BookingService {
private final JdbcTemplate jdbc;
public BookingService(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
@Transactional(isolation = Isolation.READ_COMMITTED)
public void bookSeat(long seatId, long userId) {
int updated = jdbc.update("""
UPDATE seats
SET status = 'BOOKED', booked_by = ?
WHERE id = ? AND status = 'AVAILABLE'
""", userId, seatId);
if (updated == 0) {
throw new IllegalStateException("Seat already taken 😭");
}
}
}
Why this is strong:
No “read-modify-write” race.
No reliance on multiple reads being consistent.
The DB enforces correctness via the conditional update.
This is often more scalable than jumping straight to SERIALIZABLE.
Example B — When you actually want SERIALIZABLE (and must retry)
Scenario: “Only 100 discount coupons per day.”
If multiple transactions check remaining count and then insert usage, you can get a race unless the schedule is truly serial.
@Transactional(isolation = Isolation.SERIALIZABLE)
public void claimDailyCoupon(long userId) {
// check remaining, record claim, decrement counter...
}
Because SERIALIZABLE may fail with serialization_failure, your app should retry. PostgreSQL documents SQLSTATE 40001 for serialization failure. (PostgreSQL)
A simple retry loop:
public void claimWithRetry(long userId) {
int attempts = 0;
while (true) {
try {
attempts++;
claimDailyCoupon(userId);
return;
} catch (CannotSerializeTransactionException ex) {
if (attempts >= 3) throw ex; // stop after a few attempts
// small backoff could help in real systems
}
}
}
(Exact exception types differ by driver/stack, but the idea is: retry whole transaction logic, which PostgreSQL also emphasizes. (PostgreSQL))
Example C — Propagation.REQUIRES_NEW for audit logging (and the connection-pool warning)
You might want audit logs to commit even if the main business transaction fails.
@Service
public class AuditService {
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void writeAudit(String msg) {
// insert into audit_log...
}
}
Spring’s propagation reference explains REQUIRES_NEW uses an independent physical transaction and that it can exhaust the connection pool if overused; it explicitly warns to avoid it unless the pool is sized appropriately. (Home)
Example D — Propagation.NESTED for partial rollback (savepoints)
Sometimes you want “try step B, if B fails rollback only B, but keep A”.
@Transactional
public void checkout() {
reserveInventory(); // A
try {
applyCoupon(); // B
} catch (Exception ignored) {
// continue without coupon
}
chargePayment(); // C
}
@Transactional(propagation = Propagation.NESTED)
public void applyCoupon() { ... }
Spring explains PROPAGATION_NESTED uses a single physical transaction with savepoints to roll back partially, typically mapped to JDBC savepoints. (Home)
Bonus “gotcha” that surprises many people: transactions don’t magically follow new threads
Spring’s @Transactional typically uses thread-bound transactions and does not propagate to newly started threads inside the method. (Home)
So if you do new Thread(...).start() inside a transactional method, that new thread won’t automatically share the same transaction.
A practical decision guide (what to choose in real systems)
Choose READ COMMITTED when:
You have OLTP workloads (typical web apps).
You can design updates to be atomic (conditional updates).
You can use explicit locks only where needed.
You want good performance with solid safety.
PostgreSQL explicitly calls READ COMMITTED fast and adequate for many applications. (PostgreSQL)
Choose REPEATABLE READ when:
You need a stable read view across a transaction (e.g., multi-step reports).
You understand snapshot-style behavior and still guard invariants.
You can tolerate occasional serialization-type retries in some DBs (PostgreSQL warns about retries). (PostgreSQL)
Choose SERIALIZABLE when:
Business rules depend on “as-if-one-at-a-time” correctness.
You can implement retry logic.
You’ve tested under contention.
PostgreSQL states Serializable emulates serial execution and may trigger serialization failures via monitoring. (PostgreSQL)
Avoid READ UNCOMMITTED unless:
You’re doing analytics/reporting where inconsistency is acceptable.
You explicitly want minimal locking/overhead (MySQL describes this use case). (MySQL)
Mini-lab: reproduce anomalies (great for blog readers + your YouTube demo)
Open two SQL shells to the same DB.
Non-repeatable read demo (READ COMMITTED)
Session A
BEGIN;
SELECT price FROM products WHERE id = 10;
-- wait
SELECT price FROM products WHERE id = 10;
COMMIT;
Session B
UPDATE products SET price = price + 10 WHERE id = 10;
COMMIT;
You’ll see session A’s second read change in READ COMMITTED (expected behavior).
Stable reads demo (REPEATABLE READ)
Do the same, but in session A:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM products WHERE id = 10;
-- wait
SELECT price FROM products WHERE id = 10;
COMMIT;
In snapshot-based systems (like PostgreSQL’s Repeatable Read), those two reads stay consistent. (PostgreSQL)
Final mental model (the “make it stick” summary)
Think of isolation levels like how strict the bouncer is at a club:
READ UNCOMMITTED: “Yeah sure, walk in, I didn’t check anything.”
READ COMMITTED: “Only people with a real stamp get in (committed data), but the crowd can change while you’re inside.”
REPEATABLE READ: “Once you enter, you see the club exactly as it was when you first stepped in (stable snapshot).”
SERIALIZABLE: “One group at a time enters in a strict queue; if we detect a conflict, someone gets sent back to the line (retry).” (PostgreSQL)
If you want, I can tailor the blog to your exact stack
Tell me:
Which DB you’re using most (Postgres / MySQL / SQL Server / Oracle)
Whether you use Spring Data JPA or JdbcTemplate
Your target audience (beginner / intermediate / interview prep / production engineering)
…and I’ll adapt the examples, add a complete runnable demo (Docker compose + two-session scripts), and include diagrams you can drop directly into your YouTube description and GitHub README.
Comments
Post a Comment