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.