What is the Repeatable Read Isolation Level?
Learn how the Repeatable Read isolation level keeps re-read rows consistent, blocks non-repeatable reads, and handles phantoms.
Expected Interview Answer
Repeatable Read is an isolation level that guarantees any row a transaction has read will return the exact same values if read again later in that same transaction, preventing both dirty reads and non-repeatable reads, though it may still allow phantom reads in some implementations.
The database achieves this by taking a consistent snapshot of the data (or holding shared locks on read rows) at the start of the transaction, so subsequent reads of the same rows never reflect other transactions’ commits made afterward. This protects against a value changing out from under you mid-transaction, which matters for logic that reads a row, computes something, and writes back based on that read. Some engines, like MySQL’s InnoDB, extend Repeatable Read to also prevent phantom reads via gap locking, while the SQL standard technically still permits them at this level.
- Guarantees consistent values across repeated reads in one transaction
- Prevents both dirty reads and non-repeatable reads
- Safer for read-then-write logic than Read Committed
- MySQL InnoDB additionally blocks phantom reads at this level
AI Mentor Explanation
Imagine a commentator who takes a printed snapshot of the full scorecard the moment a review begins, and refers only to that printed copy for the rest of the review discussion, ignoring any updates happening live elsewhere. No matter how many times they check the printed sheet during the review, the numbers on it never change. Repeatable Read works this way: once a transaction reads a row, checking it again later in the same transaction returns the identical value.
Step-by-Step Explanation
Step 1
Set the isolation level
Configure with SET TRANSACTION ISOLATION LEVEL REPEATABLE READ before starting the transaction.
Step 2
Begin the transaction
Start the transaction that will take a consistent snapshot or acquire shared locks on rows it reads.
Step 3
Read rows consistently
Every read of a previously read row returns the same value, regardless of other transactions committing in between.
Step 4
Watch for phantom rows
Depending on the engine, new rows matching a repeated range query may or may not appear — MySQL InnoDB blocks this via gap locks, other engines may not.
What Interviewer Expects
- Clear definition of what "repeatable" guarantees across reads in a transaction
- Understanding that dirty reads and non-repeatable reads are both prevented
- Awareness that phantom reads are standard-permitted but InnoDB blocks them via gap locks
- A concrete example, like reading a balance twice before writing back
Common Mistakes
- Assuming Repeatable Read always fully prevents phantom reads across all databases
- Confusing Repeatable Read with Serializable
- Not explaining how the snapshot or locking mechanism achieves the guarantee
- Forgetting to mention that dirty reads are also excluded at this level
Best Answer (HR Friendly)
“Repeatable Read guarantees that if you read a row once in a transaction, reading it again later in that same transaction gives you the exact same value, even if someone else changes and commits that row in the meantime. It is stricter than Read Committed, which does not offer that guarantee, though it can still miss brand-new rows added by others depending on the database.”
Code Example
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT balance FROM Accounts WHERE account_id = 1; -- returns 500
-- Meanwhile, another transaction commits a deposit of 100
-- to account_id = 1 in this window
SELECT balance FROM Accounts WHERE account_id = 1; -- still returns 500
-- Repeatable Read guarantees the second read matches the first,
-- unaffected by the other transaction's commit.
COMMIT;Follow-up Questions
- How does Repeatable Read differ from Read Committed?
- Why does MySQL InnoDB prevent phantom reads at Repeatable Read while the SQL standard does not require it?
- What mechanism (locking vs MVCC snapshot) does your database use to implement Repeatable Read?
- When would you need Serializable instead of Repeatable Read?
MCQ Practice
1. What does Repeatable Read guarantee about a row read twice in the same transaction?
Repeatable Read ensures a row read once in a transaction returns the identical value on every subsequent read in that transaction.
2. Which anomaly can still occur under the SQL standard definition of Repeatable Read?
The SQL standard permits phantom reads at Repeatable Read, though some engines like MySQL InnoDB additionally block them.
3. Which database engine is known for blocking phantom reads at the Repeatable Read level using gap locks?
MySQL’s InnoDB storage engine extends Repeatable Read with gap locking, preventing phantom reads beyond the SQL standard’s minimum requirement.
Flash Cards
What is Repeatable Read? — An isolation level guaranteeing a row read once in a transaction returns the same value on later reads in that transaction.
Does Repeatable Read prevent dirty reads? — Yes, along with non-repeatable reads.
Does Repeatable Read always prevent phantom reads? — Not by the SQL standard, though MySQL InnoDB prevents them via gap locking.
Repeatable Read vs Read Committed? — Repeatable Read locks in consistent values across the whole transaction; Read Committed only guarantees consistency per statement.