Repeatable Read vs Snapshot Isolation

Here’s a question: when you choose Repeatable Read isolation level in MySQL, does it actually give you Snapshot Isolation semantics instead? How different are they and why does it matter?

Theory

ANSI Standard Isolation Levels

PostgreSQL, MySQL, SAP, and many other databases implement the four common isolation levels which are defined in SQL-92 based on anomaly protection:

Anomaly A1 is “dirty reads” (T1 sees the data T2 wrote before it commits, and indeed T1 might even see it if T2 is rolled back).

Anomaly A2 is “inconsistent reads” (T1 reads a row, T2 does something to it and commits, and T1 reads it again but gets a different value).

Anomaly A3 is “phantoms” (T1 reads some rows via a select predicate, T2 changes the set of rows matching that predicate, e.g. by adding a new row, and commits, and T1 reads again but this time the select results are slightly different due to the actions of T2, so they still aren’t perfectly isolated).

Theoretical Relation to Locking

A bit of an oversimplification, but in traditional theory, these levels were implemented by different locking regimes for phantom prevention, read sets, and write sets, e.g. none, short (only during the operation), or long (hold till the end of the transaction):

Note that the phantom prevention locks can take many forms, e.g. predicate locks, precision locks, gap locks, range locks, index locks, etc, but the basic goal is to make sure the matched set of a predicate does not vary, e.g. between repeated SELECTs in a transaction.

But in Reality

Modern databases don’t necessarily provide exactly these isolation semantics or implement them using the above simple locking regimes.

MVCC systems that can read at a specific timestamp are such a convenient tool and intuitive concept, they are widely used to achieve read set isolation and phantom prevention with less locking, higher performance, and higher concurrency. The practice is now quite prevalent.

Thus, PostgreSQL uses snapshot isolation internally, even in its default isolation level of Read Committed. MySQL also acquires a read snapshot as of the first select in a transaction, and uses the same snapshot during subsequent selects, in its default isolation level of Repeatable Read.

A careful review of the literature shows that Snapshot Isolation (SI) is very similar to Repeatable Read (RR), but they aren’t exactly the same. They are both stronger than Read Committed but weaker than Serializable. The best summary of their difference is that SI allows write skew but blocks phantoms, whereas RR blocks write skew but allows phantoms.

Let’s Experiment

MySQL

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

PostgreSQL

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

The Actual Write Skew Test

Terminal 1: Andy is checking whether it’s safe for him to take some time off:

BEGIN;
SELECT name FROM doctor WHERE oncall=TRUE AND name<>'Andy';

Terminal 2: Brad, the other doctor, has the same plan:

BEGIN;
SELECT name FROM doctor WHERE oncall=TRUE AND name<>'Brad';
UPDATE doctor SET oncall=FALSE WHERE name='Brad';
COMMIT;

Back at terminal 1: Andy thinks “quick let’s get in before anything changes”:

UPDATE doctor SET oncall=FALSE WHERE name='Andy';
SELECT name FROM doctor WHERE oncall=TRUE;
COMMIT;

Epilogue

Andy was bitten by the infamous write skew anomaly, which is not allowed under RR, but is allowed under SI. By the way, this happens in both MySQL RR and PostgreSQL RR. Simply put, Andy and Brad were operating from the same snapshotted read view, against which their writes were individually valid, but the two transactions overlap in a non-serializable way, and the combination of overlapping writes lead to an unacceptable outcome–there is nobody oncall.

Minor Plot Twist

It’s not exactly an unexpected plot twist, but it turns out RR in MySQL is weirder than you think. It is not really SI either, if you consider the lost updates anomaly, which should not be allowed in either RR or SI.

Lost Update Test

Terminal 1 (seeking to deposit $20):

BEGIN;
SELECT cash FROM account WHERE id=1;

While this is going on, in terminal 2 (seeking to deposit $30):

BEGIN;
SELECT cash FROM account WHERE id=1;
UPDATE account SET cash=130 WHERE id=1;
COMMIT;

Back to terminal 1, which saw a balance of $100:

UPDATE account SET cash=120 WHERE id=1;
COMMIT;

This is bad, it means the $30 deposit was overwritten. From the user’s perspective, either the total should be $150, or one of the transactions should have failed. This is a thing that can happen to you in RR isolation level on MySQL.

Some Advice re Lost Update

Although Serializable isolation level would prevented this problem in MySQL, if you are doing this type of transaction pattern, consider making the database aware of your intent by using SELECT ... FOR UPDATE.

Summary

People don’t always define terms in the same way (even when they think they do), and practice often diverges from theory. This is especially so in the world of database isolation levels.