Non-repeatable Read

Hook: Interviewers love this because it checks whether you understand that a transaction can be reading a moving target, not a frozen picture.

Question: What is a non-repeatable read in SQL?

Answer: A non-repeatable read happens when I read the same row twice inside one transaction and get different values the second time because another transaction committed an update or delete in between. It usually appears at weaker isolation levels such as READ COMMITTED, where each statement can see a fresh view of the data. The fix is usually stronger isolation like REPEATABLE READ or SERIALIZABLE, or locking the row when I truly need it to stay stable.

Interview-Ready Answer: A non-repeatable read is when I query the same row twice in one transaction and the result changes because another transaction committed an update or delete in between. In practice, this is allowed by READ COMMITTED, where each statement can see a new snapshot. If I need the value to stay stable, I use REPEATABLE READ or SERIALIZABLE, or I take a lock so no one can change that row mid-transaction.

What it is

1. A transaction reads a row, for example an account balance of 100.
2. Another transaction updates that same row and commits, changing it to 80.
3. The first transaction reads the same row again before it ends.
4. It now sees 80, not 100. That is a non-repeatable read: the same read is not repeatable inside one transaction.

How it works under the hood

1. READ COMMITTED often gives each statement its own snapshot of committed data. That means the first SELECT and the second SELECT may see different committed versions.
2. Many databases use MVCC (multi-version concurrency control), which means the database keeps older row versions for a while. A read may see one version now and a newer version later.
3. With REPEATABLE READ, the transaction usually keeps one snapshot for its whole life, so later reads in the same transaction keep seeing the same committed version.
4. With locking reads, such as SELECT ... FOR UPDATE, the database can block conflicting writers so the row does not change while you depend on it.
5. If the row is deleted, the second read may return no row at all. That is still a non-repeatable read because the earlier result did not stay repeatable.

Why interviewers care

They want to know whether you can explain inconsistency bugs that only happen under concurrency. A lot of real production issues are not about bad SQL syntax; they are about two valid transactions stepping on each other.

When to use stronger isolation

1. Use READ COMMITTED when each statement can safely stand alone and you care more about throughput.
2. Use REPEATABLE READ when a business decision depends on a stable value, such as a balance, seat count, or stock level.
3. Use SERIALIZABLE when correctness matters more than concurrency and you want the database to behave as if transactions ran one at a time.

Performance and trade-offs

• Stronger isolation usually means more waiting, more blocking, or more version storage.
• Reads are still fast in the common case, but hot rows can cause lock contention.
• In a busy checkout system, even 50-100 ms of extra waiting on one hot row can cascade into timeouts and retries.
• Defaults differ by database: PostgreSQL defaults to READ COMMITTED, MySQL InnoDB defaults to REPEATABLE READ, and SQL Server defaults to READ COMMITTED unless snapshot options are enabled.

Gotchas

• Not the same as dirty read: the other transaction has already committed.
• Not the same as phantom read: the row itself changes, not the set of matching rows.
• Repeatable read is not magic everywhere: databases implement it differently, and some engines still allow other anomalies like write skew.

Memory in one line: If you need the same answer twice, take a photocopy at the start; otherwise you are reading a whiteboard someone can erase between glances.

Real-World Example: Imagine a checkout service that verifies a customer's wallet balance before placing an order. The service reads the balance at the start of the transaction, then reads it again after calculating tax and discounts.

1. At first, the balance is 150, so the order looks safe.
2. While the transaction is still open, a separate top-up or refund job updates the same wallet row and commits.
3. The checkout transaction reads the balance again and now sees 120 or 180.
4. The service makes a bad decision: it may reject a valid order, accept one it should not, or log conflicting values for the same request.

The bug shows up as flaky behavior: support sees users getting inconsistent totals, logs show balance_before and balance_after disagreeing for the same request id, and retries make the problem look random. The fix is usually to keep the critical row stable with stronger isolation or a row lock while the decision is being made.

Follow-up & Tricky Questions:

• How is a non-repeatable read different from a dirty read? A dirty read sees data that has not been committed yet; a non-repeatable read sees committed data that changes between two reads in the same transaction.
• Which isolation levels prevent non-repeatable reads? REPEATABLE READ and SERIALIZABLE do, while READ COMMITTED allows them.
• Can a single SELECT have a non-repeatable read? No, because the anomaly needs the same row to be read at least twice inside one transaction.
• Does REPEATABLE READ block writers? Not always. In MVCC databases, readers often do not block writers, but the transaction keeps seeing the same snapshot.
• How do you fix it without changing isolation globally? Lock the row with a locking read such as FOR UPDATE, or re-check the version before writing.
• Is a deleted row part of this anomaly? Yes. If the row disappears between reads, the result changed, so it is still non-repeatable.

• Tricky: If two reads return the same value, was there no anomaly? Correct only for that row and that moment. Another row or a later read may still change, and you may still have a concurrency bug elsewhere.
• Tricky: Does READ COMMITTED always produce non-repeatable reads? No, it only allows them. You need the right timing and a concurrent committed update.
• Tricky: Are phantom reads and non-repeatable reads the same thing? No. Non-repeatable read is about one row changing; phantom read is about the set of rows matching a predicate changing.

Common Mistakes:

• Mistake: Saying it is a dirty read. Correction: Non-repeatable reads involve committed changes, not uncommitted ones.
• Mistake: Confusing it with phantom reads. Correction: Phantom reads are about new matching rows appearing; non-repeatable reads are about the same row changing.
• Mistake: Thinking autocommit queries can have this anomaly. Correction: Autocommit usually makes each statement its own transaction, so you need at least one open transaction and two reads.
• Mistake: Believing stronger isolation means no concurrency at all. Correction: Good databases still allow concurrency; they just control what each transaction is allowed to observe.

Memory Hook: One row, two looks, two different truths — if the page changes between peeks, you did not get a repeatable read.

Cheat Sheet:

• Same row read twice, different result = non-repeatable read.
• Usually allowed at READ COMMITTED.
• Prevent with REPEATABLE READ, SERIALIZABLE, or row locks.
• Different from dirty read and phantom read.
• Common in balances, inventory, and checkout flows.

Practice Tasks:

• Set up two SQL sessions and reproduce a changing balance inside one transaction.
• Run the same test under a stronger isolation level and observe that the second read stays stable.
• Modify the example to use FOR UPDATE and explain why the concurrent update now waits.