In this lesson, you will learn how the Repeatable Read isolation level ensures that a reporting query remains consistent even when other transactions are busy modifying data.
The Problem: Shifting Snapshots
Imagine you are running a complex financial report that calculates the total balance across ten different accounts. If you use a lower isolation level, a separate transaction might update Account A before you read it, and then update Account B after you read it. Your final report would reflect a "mixed" state of the database that never actually existed at a single point in time.
The Solution: Internal Consistency
Repeatable Read solves this by ensuring that if you read a row once, you are guaranteed that the row will not change for the duration of your transaction. While the database doesn't "lock" the entire table (which would block parallel writes), it creates a stable view for your query.
In most modern systems, this is achieved through Multi-Version Concurrency Control (MVCC). When a writer updates a row, the database doesn't overwrite the original data immediately; it keeps the "old" version available for any transaction that started before the update.
Consider this workflow:
-- Transaction A starts
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- Transaction A reads Account balance
SELECT balance FROM accounts WHERE id = 1; -- Returns 100
-- Transaction B (Parallel) starts
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;
-- Transaction A reads again
SELECT balance FROM accounts WHERE id = 1; -- Still returns 100
COMMIT;
Even though Transaction B successfully updated the balance to 200, Transaction A is shielded from this change. It continues to see the data exactly as it existed when the transaction first began. By providing this stable "snapshot," the database allows your reports to run against a consistent state of truth without forcing other users to wait for your query to finish.
Key Takeaway: Repeatable Read grants your transaction a consistent, frozen view of the data, allowing complex reports to run accurately without blocking simultaneous write operations.