Repeatable Read

Why interviewers love this: it sounds simple, but the real answer tests whether you understand transactions, snapshots, and hidden concurrency bugs.

Question: What is Repeatable Read in SQL?

Answer: Repeatable Read is a transaction isolation level that gives one transaction a stable view of the data. If I read the same row twice before I commit, I should see the same value both times, even if another transaction commits a change in the middle. In plain words: once my transaction takes its snapshot, it keeps looking at that same snapshot until it ends.

Interview-Ready Answer: I’d say Repeatable Read means every read inside my transaction sees the same committed snapshot, so a row I read once will not appear to change when I read it again. That prevents non-repeatable reads and makes multi-step logic easier to reason about. The important nuance is that it is still weaker than Serializable, so some business-rule anomalies like write skew can still slip through depending on the database engine.

Detailed Explanation:

What Repeatable Read Really Means

Repeatable Read is about keeping your reads stable inside one transaction. A non-repeatable read happens when you read a row, someone else commits an update, and then you read that same row again and get a different value. Repeatable Read is designed to stop that surprise.

How It Works Under the Hood

1. The transaction begins and the database establishes a view of what is committed right now.
2. Your first read uses that view, often called a snapshot (a frozen picture of committed data at one moment).
3. If another transaction updates the row, the database creates a new version of the row instead of changing the version your transaction already sees. This is the common MVCC model, where MVCC means Multi-Version Concurrency Control.
4. Your later reads keep using the same snapshot, so the row looks unchanged to you.
5. If you try to update a row that changed after your snapshot, the engine may block briefly and then fail the transaction or raise a conflict, because your stale view can no longer safely win.
6. When you commit or roll back, the snapshot disappears and the next transaction gets a fresh view.

Repeatable Read vs Other Isolation Levels

That table is the big interview clue: Repeatable Read is stronger than Read Committed, but weaker than Serializable. Also, engine behavior is not identical. PostgreSQL’s Repeatable Read is snapshot-based and very stable for reads, while other systems may use locks differently for locking reads. So always think “what does this database mean by the label?”

When and Why to Use It

• Use it when one transaction needs a consistent story across multiple reads, like checking a customer balance, generating a report, or finishing a checkout flow.
• It is a good middle ground when Read Committed is too noisy but Serializable is too expensive or causes too many retries.
• If you need to enforce a business invariant across multiple rows, like “only one active booking per seat,” Repeatable Read alone is often not enough; use Serializable or explicit locking.

Performance and Edge Cases

Big-O is not the best lens here, but reads in an MVCC engine are usually close to O(1) to find the visible version. The hidden cost is storage and cleanup: long-running transactions can pin old row versions, so the database must keep more history around. On a busy table, that can mean anything from a few extra megabytes to gigabytes of old versions if a transaction stays open for minutes or hours.

Common gotcha: Repeatable Read stops your view from changing, but it does not magically make every business rule safe. Two transactions can still each see a valid snapshot and make decisions that conflict together later. That is why interviewers often pair this topic with write skew, phantom behavior, and Serializable isolation.

Version note: PostgreSQL defaults to Read Committed, MySQL InnoDB defaults to Repeatable Read, and SQL Server commonly defaults to Read Committed. Same name, different defaults, and sometimes different locking behavior. That is exactly why you should describe the guarantee, not just memorize the label.

Real-World Story: Imagine a checkout service for an e-commerce app. A customer opens the cart, the service checks item prices, shipping, and available stock, and then calculates the final charge. If the transaction uses Repeatable Read, the cart total stays consistent through the whole checkout flow, so the user does not see the price change halfway through payment.

What goes wrong when people misunderstand this? A team uses Read Committed for the checkout read path and assumes it is “good enough.” Mid-checkout, a promo job updates pricing, so the first read shows one subtotal and the second read shows another. Users report “my total changed before I paid,” payment logs show mismatched authorization amounts, and support sees complaints like subtotal_changed_during_checkout or cart_total_mismatch. The bug is not just annoying; it can cause failed charges, duplicate retries, and reconciliation headaches between the order service and the payment gateway.

Follow-up & Tricky Questions:

• How is Repeatable Read different from Serializable? Repeatable Read gives you a stable snapshot, but Serializable goes further and makes the outcome behave like the transactions ran one at a time. Serializable is the safer choice for strict business rules, but it can abort more often under contention.
• Does Repeatable Read prevent non-repeatable reads? Yes, that is its main purpose. If you read the same row twice inside one transaction, you should not see it change underneath you.
• Can Repeatable Read still allow write skew? Yes, in snapshot-style implementations it can. Two transactions can each read a consistent snapshot, make separate updates, and still violate a rule when the results are combined.
• Should I use Repeatable Read for money transfers? It can help, but for important invariants you often still want row locks, unique constraints, or Serializable isolation. For example, if the rule is global, not just row-by-row, Repeatable Read alone may not be enough.
• Is Repeatable Read the default everywhere? No. Defaults vary by database, and that is a common interview trap. PostgreSQL defaults to Read Committed, while MySQL InnoDB defaults to Repeatable Read.
• Can the same name mean different behavior? Yes. The label is standardized, but each engine can implement it differently, especially around locking reads and phantom behavior.
• Does Repeatable Read stop lost updates? It reduces that risk, but you should not rely on it blindly. If two transactions compute a new value from the same old read, you often need explicit locking or a stronger isolation level.
• What happens if I keep a Repeatable Read transaction open too long? The database may need to keep old versions around for your snapshot, which increases storage pressure and can slow cleanup. Long transactions are a hidden source of bloat and performance trouble.

Tricky point: many candidates say Repeatable Read is “just like Serializable but weaker.” That is directionally right, but incomplete. The real interview-grade answer is that it guarantees a stable read view, yet some cross-row anomalies can still happen unless the engine uses stronger locking rules or you move to Serializable.

Common Mistakes:

• Confusing Repeatable Read with Serializable. Correction: Repeatable Read stabilizes reads, but Serializable is the level that tries to make transactions behave as if they executed one by one.
• Assuming every database implements it identically. Correction: Always mention the engine; PostgreSQL, MySQL InnoDB, and SQL Server do not behave exactly the same.
• Thinking it fixes all race conditions. Correction: It prevents rereads from changing, but it does not automatically protect every business invariant across multiple rows.
• Leaving long transactions open. Correction: Old row versions stay alive longer, which can increase bloat and hurt performance.

Memory Hook: Repeatable Read is like taking a photo of the database when the transaction starts. Every reread is you looking at the same photo, not the live scene.

Cheat Sheet:

• Same transaction, same snapshot.
• Prevents non-repeatable reads.
• Usually stronger than Read Committed, weaker than Serializable.
• Great for consistent multi-step reads.
• Still not enough for every cross-row rule.
• Long transactions can increase version storage.

Practice Tasks:

• Open two SQL sessions and watch one transaction keep the same row value while another session updates it.
• Compare the same test under Read Committed and Repeatable Read, and write down the different results.
• Try to design a booking rule that Repeatable Read does not fully protect, then decide whether you need Serializable or explicit locks.