Course contentsShow
← Software Engineering
Lesson 907 of 1,93328. Transactions and ConsistencyFree lesson

Repeatable Read Isolation

Ensuring consistent reads within a transaction while still allowing phantom reads; understanding snapshot-based implementations.

Repeatable Read Isolation

What you'll learn: How Repeatable Read isolation ensures consistent reads within a transaction while still allowing phantom rows to appear.

Core Idea

Repeatable Read is the third isolation level that guarantees if you read the same row twice within a transaction, you'll see the same data both times—even if other transactions have committed changes to that row in between. It solves the non-repeatable read problem you learned about at Read Committed level.

However, Repeatable Read still permits phantom reads: if your transaction runs a query twice (like SELECT * WHERE age > 30), new rows matching your criteria might appear in the second execution if another transaction inserted and committed them.

How It Works: Snapshot-Based Implementation

Most modern databases implement Repeatable Read using snapshots. When your transaction begins, the database captures a consistent view of the data as it existed at that moment. Throughout your transaction, you read from this snapshot—ignoring any changes committed by other transactions after yours started.

Analogy: Imagine taking a photograph of a library's bookshelf when you enter. You can look at that photo repeatedly during your visit and see the exact same books in the same places. However, if someone adds a new shelf to the library, you might notice it when you physically walk around—that's like a phantom read.

Key Differences

  • Protects against: Non-repeatable reads (row-level changes)
  • Does NOT protect against: Phantom reads (new rows matching your query)
  • Implementation: Typically multi-version concurrency control (MVCC) with transaction-start snapshots
  • Performance: Better read concurrency than serializable isolation, with minimal locking

Key Takeaway: Repeatable Read gives you a stable snapshot of data for consistent reads within your transaction, but new rows matching your queries can still "appear" as phantoms—acceptable for many applications that don't need absolute serializability.