Module 1 - Data Storage

Database Isolation Levels

Control how transactions interact with each other. Trade consistency for performance.

1The Bank Account Analogy

💡 Simple Analogy
You and your spouse both check your joint bank balance ($1000) at the same time, then both try to withdraw $800.
  • Poor isolation: Both see $1000 → Both withdraw → Account goes -$600!
  • Good isolation: First withdrawal locks account → Second sees $200 → Correctly denied
Isolation levels control how much transactions can "see" each other's uncommitted work.

2The Four Isolation Levels

Read Uncommitted

Transactions can read uncommitted changes from other transactions (dirty reads).

Problem allowed: Dirty Read: Read data that might be rolled back.
Use case: Almost never used. Analytics on non-critical data.

Read Committed

Only read committed data. Most common default (PostgreSQL, Oracle).

Problem allowed: Non-Repeatable Read: Same query returns different results within transaction.
Use case: Default for most applications. Good balance.

Repeatable Read

Same query always returns same results within transaction. MySQL default.

Problem allowed: Phantom Read: New rows can appear in range queries.
Use case: Financial calculations, reports.

Serializable

Transactions execute as if serial (one after another). Highest isolation.

Problem allowed: None, but very slow. Lots of locking/retries.
Use case: Critical financial transactions, inventory systems.

3Read Anomalies Explained

Dirty Read

Reading uncommitted data that might be rolled back.

T1 updates row → T2 reads updated value → T1 rolls back → T2 has invalid data
Prevented by: Read Committed+

Non-Repeatable Read

Same row returns different values when read twice.

T1 reads row → T2 updates & commits → T1 reads again → Different value!
Prevented by: Repeatable Read+

Phantom Read

New rows appear in range queries between reads.

T1 counts rows WHERE x=1 → T2 inserts new row → T1 counts again → Different count!
Prevented by: Serializable

4Comparison Table

Isolation LevelDirty ReadNon-RepeatablePhantomPerformance
Read UncommittedPossiblePossiblePossibleFastest
Read CommittedPreventedPossiblePossibleFast
Repeatable ReadPreventedPreventedPossibleMedium
SerializablePreventedPreventedPreventedSlowest

5Database Defaults

PostgreSQL
Read Committed
Can use Serializable efficiently
MySQL (InnoDB)
Repeatable Read
Uses MVCC, avoids most locks
SQL Server
Read Committed
Snapshot isolation available
Oracle
Read Committed
Serializable supported

6When to Change Isolation

Use Higher Isolation (Serializable)

  • • Financial transactions (transfers, payments)
  • • Inventory management (stock levels)
  • • Booking systems (double-booking prevention)
  • • Anywhere race conditions are critical

Use Lower Isolation (Read Committed)

  • • Read-heavy workloads
  • • Analytics queries
  • • Where slight inconsistency is acceptable
  • • High-throughput applications
Interview Insight

Understand the trade-off: Higher isolation = More consistent but slower. Be ready to explain when you'd use each level and why.

7Key Takeaways

1Isolation levels control how much transactions can see each other's uncommitted work.
2Read Committed is the most common default-prevents dirty reads.
3Serializable prevents all anomalies but is slowest.
4Higher isolation = more consistency but worse performance.
5Know the anomalies: dirty read, non-repeatable read, phantom read.
6In critical systems (banking), use higher isolation for sensitive operations.

8Interview Follow-up Questions

Interview Follow-up Questions

Common follow-up questions interviewers ask

9Test Your Understanding

Test Your Understanding

5 questions

1

What is a dirty read?

2

Which isolation level is the most commonly used default in production databases?

3

Phantom reads are possible at which isolation levels?

4

What is the trade-off of using Serializable isolation?

5

Transaction A reads row X, then Transaction B modifies and commits row X, then Transaction A reads row X again with different values. This is called:

0 of 5 answered