MySQL’s READ COMMITTED and REPEATABLE READ isolation levels aren’t just about what data you see; they fundamentally change how transactions interact and can lead to vastly different performance characteristics and concurrency behaviors.
Let’s see REPEATABLE READ in action. Imagine a simple accounts table:
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL(10, 2)
);
INSERT INTO accounts (id, balance) VALUES (1, 100.00);
Now, consider two concurrent transactions, T1 and T2, both running under REPEATABLE READ.
Transaction T1:
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;SELECT balance FROM accounts WHERE id = 1;(Let’s say this returns 100.00)- Some time passes…
SELECT balance FROM accounts WHERE id = 1;(This will still return 100.00, even if T2 committed changes in between)COMMIT;
Transaction T2:
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;UPDATE accounts SET balance = balance - 10.00 WHERE id = 1;COMMIT;
In REPEATABLE READ, T1 will see the initial balance of 100.00 in both of its SELECT statements, even though T2 updated the balance to 90.00 and committed. This is because REPEATABLE READ uses Multi-Version Concurrency Control (MVCC) to provide a consistent snapshot of the data as it existed when the transaction began.
The problem REPEATABLE READ solves is ensuring that within a single transaction, multiple reads of the same row will always return the same data. This is crucial for many business logic operations where you might read a value, perform calculations, and then write back based on that original value. Without REPEATABLE READ, a subsequent read might see a change made by another transaction, invalidating your calculations.
Internally, MySQL (specifically InnoDB, the default storage engine) achieves this using a combination of undo logs and transaction IDs. When a transaction reads a row, it gets a consistent view based on the transaction’s start time. If another transaction modifies that row, InnoDB creates a new version of the row and records the change in its undo log. Older versions of the row are kept in the undo log until all active transactions that might need them have completed. This is why T1 in our example still saw 100.00 – it was reading an older, uncommitted version of the row that was available to it based on its transaction’s snapshot.
The exact levers you control are the TRANSACTION ISOLATION LEVEL setting, which can be set globally or per session. The common levels are:
READ UNCOMMITTED: The weakest. Reads can see uncommitted changes from other transactions.READ COMMITTED: Reads only see data that has been committed by other transactions. This is the default for many other databases but not MySQL.REPEATABLE READ: The default for MySQL. Reads within a transaction see a consistent snapshot from the transaction’s start.SERIALIZABLE: The strongest. Transactions are executed as if they were run one after another, preventing almost all concurrency issues but severely impacting performance.
When you set @@SESSION.TRANSACTION_ISOLATION = 'REPEATABLE READ';, you’re telling InnoDB to manage row versions such that any SELECT statement within that session’s current transaction, when reading the same row multiple times, will always retrieve the same data. This is accomplished by associating each row version with a transaction ID and a rollback pointer (to undo logs). When a transaction reads a row, it checks the transaction IDs of available row versions; it will only see versions committed by transactions that finished before its own transaction started, or versions created by its own transaction.
What most people don’t realize is how REPEATABLE READ handles phantom reads. While it prevents non-repeatable reads (reading the same row multiple times and getting different values) and dirty reads (reading uncommitted data), it can still be susceptible to phantom reads if you’re not careful, unless you’re using index scans. In REPEATABLE READ, MySQL uses gap locks on indexes to prevent new rows from being inserted into the scanned range during a transaction. If a SELECT statement uses a full table scan without an index, it might not acquire these gap locks, and a phantom row could appear in a subsequent SELECT. However, with index scans, REPEATABLE READ effectively prevents phantom reads because the gap locks on the index ranges prevent insertions.
The next concept you’ll likely grapple with is how to effectively manage locking and deadlocks when aiming for higher concurrency.