Transaction Isolation Levels in SQL - Repeatable Read

Check out part 1 of the series on SQL Transaction isolation levels.

After a theoretical introduction, it's time to take a closer look at how isolation levels work in the real world. We begin our trip to the world of SQL examples by trying out REPEATABLE READ with all its benefits and flaws.

SQL Standard

The name is pretty self-explanatory - we expect REPEATABLE READ to present the same state of the data during the time of the transaction. If we ask for some data once, we should expect to have the same results for that query, unless we change it ourselves.

In MySQL, REPEATABLE READ is a default transaction isolation level.

Examples

Our data set consists of just one table, and for the sake of clarity, all the queries are simplified in a way that you don't need to know the structure of the data. I'm using pseudocode like <condition1> to indicate that some inserted or selected row fulfills some condition.

Non-locking reads

In the simplest example we start one transaction and change the data in another session:

A: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
A: BEGIN;
A: SELECT * FROM table WHERE <condition1>; -> 3 results
                B: INSERT INTO table VALUES ... (<matching condition1>);
A: SELECT * FROM table WHERE <condition1>; -> 3 results
A: COMMIT;
A: SELECT * FROM table WHERE <condition1>; -> 4 results

As you can see, while the transaction is in progress, any changes done outside do not seem to affect the data selected with the exact same query, as we would expect by reading REPEATABLE READ name.

Locking reads

What would happen if the other database session tried to change the data, that the transaction wants to change as well? To indicate that transaction in A wants to do that, we use FOR UPDATE at the end of the query, which puts a lock on the result rows. Once we do this, the changes in session B should not get applied:

A: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
A: BEGIN;
A: SELECT * FROM table WHERE <condition1> FOR UPDATE; -> 3 results
                B: UPDATE table SET ... WHERE <condition1>; -> (wait!)
A: COMMIT; -> (release wait in B, updated 1)

Again, everything works like a charm, as the second session stops and hangs, waiting for the transaction in A to finish. Once it does, the B is released and the changes are applied.

Races between database sessions

What happens when two sessions try to change the same value?

A: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
A: BEGIN;
A: SELECT * FROM table WHERE <condition1> FOR UPDATE; -> 3 results
A: UPDATE table SET prop='AAA' WHERE id=X; -> 1 changed
                B: UPDATE table SET prop='BBB' WHERE id=X; -> 1 changed
A: COMMIT; -> (release wait in B)

What happens after COMMIT is that it first applies the changes from the transaction being submitted, then the lock in session B is released and its change is applied. The result is that the row X has prop with a value of BBB even though from the perspective of session A we just(!) committed our change.

Causing deadlocks with races

What happens if we change the row in a way, that the second session's changes cannot be applied anymore? That is when during the transaction we change the rows in a way that they don't match WHERE clause in the query in session B? Well, the result is a bit dramatic, but at the same time probably the only logical way to proceed:

A: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
A: BEGIN;
A: SELECT * FROM table WHERE <condition1> FOR UPDATE; -> 3 results
                B: UPDATE table SET ... WHERE <condition1>; -> 1 changed
A: UPDATE table SET <don't pass condition1> WHERE <condition1>;
        -> 1 changed
        -> release wait in B, cause "Deadlock found, ... try restarting transaction"
A: COMMIT;

As you can see, we released the wait in B again, but this time we caused that query to fail. B is told to try to restart the operation, as the data has changed and it's not safe/possible to apply that old query. Database engine could probably applied that change and say that there are no changes to be made, but the way it has been done seems better, as blindly applying the changes could have some unexpected side effects. It's better to be defensive here.

Quirky updates in MySQL

One last thing that you should be aware of is that while reads are indeed REPEATABLE here, you need to be aware of doing any updates. As it turns out, if the underlying condition is fulfilled by more rows, it will be applied there, even though the SELECT did not reveal that:

A: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
A: BEGIN;
A: SELECT * FROM table WHERE <condition1>; -> 3 results
                B: INSERT INTO table VALUES ... (<matching condition1>);
A: SELECT * FROM table WHERE <condition1>; -> 3 results
A: UPDATE table WHERE <condition1>; -> 4(!) changed
A: SELECT * FROM table WHERE <condition1>; -> 4(!) results
A: COMMIT;

Fortunately, once you make the UPDATE, the following SELECT queries return all rows, including the one surprisingly changed before.

Summary

Even though the REPEATABLE READ seems simple enough, you can still hit some interesting points trying to use it on a daily basis. It's important to understand how it works, and that sometimes you need to support retrying transaction if several are operating on the same subset of data. Make sure you understand it well, especially if you are using MySQL where REPEATABLE READ is a default transaction isolation level.