Appearance
Repeatable Read
In the Repeatable Read isolation level, a transaction may encounter the issue of phantom reads.
Phantom Read
A phantom read occurs when a transaction queries for a record that does not exist. However, if another transaction inserts this record, the first transaction can later read it, seemingly appearing out of nowhere.
Example
Let's prepare the students table with the following data:
sql
mysql> SELECT * FROM students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)Now, we open two MySQL client connections and execute Transaction A and Transaction B in sequence:
| Time | Transaction A | Transaction B |
|---|---|---|
| 1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
| 2 | BEGIN; | BEGIN; |
| 3 | SELECT * FROM students WHERE id = 99; -- empty | |
| 4 | INSERT INTO students (id, name) VALUES (99, 'Bob'); | |
| 5 | COMMIT; | |
| 6 | SELECT * FROM students WHERE id = 99; -- empty | |
| 7 | UPDATE students SET name = 'Alice' WHERE id = 99; -- 1 row affected | |
| 8 | SELECT * FROM students WHERE id = 99; -- Alice | |
| 9 | COMMIT; |
In step 3, Transaction B reads the record with id=99 and finds it empty, indicating that it does not exist. Then, in step 4, Transaction A inserts a new record with id=99 and commits the change. However, when Transaction B checks again in step 6, it still finds no record for id=99.
Next, in step 7, Transaction B attempts to update the non-existent record with id=99 and surprisingly succeeds. When Transaction B reads the record again in step 8, it now sees the record with the name 'Alice'.
This illustrates that phantom reads can occur, where a record that was thought to be non-existent can later be updated successfully, and upon further reads, it appears.