Database Isolation Levels: Why Phantom Read Doesn't Happen in MySQL
5 min read
The isolation level of a transaction determines whether a transaction is allowed to see the data being modified or read by other concurrent transactions.
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
READ UNCOMMITTED
The isolation level that allows access to even uncommitted data.
This is the lowest level of isolation and is generally not used.
If Transaction 1 modifies data but has not yet committed, Transaction 2 can access the uncommitted data.
--- (transaction 1)
UPDATE MEMBER SET name = 'Seonggil2' WHERE ID = 1 --- not committed
--- (transaction 2)
SELECT * FROM MEMBER WHERE ID = 1
--- | ID: 1 | NAME: Seonggil2 |
If Transaction 2 reads the data and Transaction 1 subsequently performs a ROLLBACK, it can cause data inconsistency (Dirty Read).
READ COMMITTED
Only committed data can be read.
READ COMMITTED can cause Non-Repeatable Read in addition to the Phantom Read issue found in REPEATABLE READ.
If Transaction 1 modifies data but has not yet committed:
Process
- Transaction 1 modifies data but has not yet committed.
- The table is updated, and the previous data is backed up in the Undo Log.
- Transaction 2 queries the data.
- Since only committed data can be read, it retrieves the previous data from the Undo Log.
--- (transaction 1)
UPDATE MEMBER SET name = 'Seonggil2' WHERE ID = 1 -- not committed
--- (transaction 2)
SELECT * FROM MEMBER WHERE ID = 1
--- | ID: 1 | NAME: 'Seonggil' |
However, Non-Repeatable Read can occur.
Non-Repeatable Read: When executing the same SELECT Query multiple times in a transaction, different results appear.
Process
- Transaction 1 modifies data but has not yet committed.
- The table is updated, and the previous data is backed up in the Undo Log.
- Transaction 2 queries the data using the modified value as a condition.
- Since the data is not yet committed, it does not appear in the result.
- Transaction 1 commits.
- When Transaction 2 queries the data again using the modified value, the data is now returned (Error Point).
--- (Transaction 1)
UPDATE MEMBER SET NAME = 'Seonggil2' WHERE ID = 1 --- not committed
--- (Transaction 2)
SELECT * FROM MEMBER WHERE NAME = 'Seonggil2' --- no Result
--- (Transaction 1)
commit;
--- (Transaction 2)
SELECT * FROM MEMBER WHERE NAME = 'Seonggil2' --- | ID: 1 | NAME: 'Seonggil2'|
In READ COMMITTED, performing repeated reads may yield different results depending on the commit status of other transactions.
This can cause issues when reading and modifying the same data within a single transaction.
REPEATABLE READ
An isolation level that prevents Non-Repeatable Read, allowing only committed data to be read while ensuring that only transactions with lower transaction numbers can read committed data.
Multiple versions of the same record may exist, a concept known as MVCC (Multi-Version Concurrency Control).
Each transaction has a unique, sequentially increasing transaction number, and backup records store both the old data and the transaction number that modified it.
Process
- Transaction 1 queries data.
- Transaction 2 modifies and commits the same data.
- Transaction 1 queries the data again.
--- Transaction 1
SELECT * FROM MEMBER WHERE ID = 1 --- | ID: 1 | NAME: 'Seonggil' |
--- Transaction 2
UPDATE MEMBER SET NAME = 'Seonggil2' WHERE ID = 1
commit;
--- Transaction 1
SELECT * FROM MEMBER WHERE ID = 1 --- | ID: 1 | NAME: 'Seonggil' |
Transaction 1 reads only data committed by transactions with a lower transaction number, and if a newer transaction has modified the data, it retrieves the previous version from the Undo Log.
Although REPEATABLE READ prevents Non-Repeatable Read, it does not block new records from being added, so Phantom Read can still occur.
However, thanks to MVCC, Phantom Read generally does not occur in simple queries, as newly inserted records from later transactions are ignored.
Process
- Transaction 1 retrieves a list of users.
- Transaction 2 inserts a new user.
- Transaction 1 retrieves the list again, but Transaction 2’s new user is not visible.
--- Transaction 1
SELECT * FROM MEMBER
--- |ID: 1|NAME: 'Sg02'|, |ID: 2|NAME: 'Seonggil'|
--- Transaction 2
INSERT INTO MEMBER VALUES (3, 'newUser')
commit;
--- Transaction 1
SELECT * FROM MEMBER
--- |ID: 1|NAME: 'Sg02'|, |ID: 2|NAME: 'Seonggil'| // return Same result
When Phantom Read Occurs in REPEATABLE READ
If a query uses locking, Phantom Read can occur in standard RDBMSs.
Process
- As before, Transaction 1 reads data first and applies a write lock using SELECT FOR UPDATE.
(Write Lock: SELECT FOR UPDATE, Read Lock: SELECT FOR SHARE)
Locks are released when the transaction is committed or rolled back.
- Transaction 2 attempts to insert a new record.
In most DBMSs, only the modified record is locked, so INSERT can be executed immediately without locks.
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE
--- | ID: 2 | NAME: 'seonggil'| (count: 1)
--- Transaction 2
INSERT INTO MEMBER VALUES (3, 'newMember')
commit;
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE
--- | ID: 2 | NAME: 'seonggil'|, | ID: 3| NAME: 'newMember'| (count: 2)
Since new records can be added, query results within the same transaction can change.
This occurs when another transaction inserts or deletes records.
Why MVCC Does Not Prevent Phantom Read
Because FOR UPDATE was used, Phantom Read cannot be prevented. Locked reads operate on the table, not the Undo Log. Locked reads select rows first, then apply locks. Locks cannot be applied to the Undo Log because it is append-only.
Why MySQL Prevents Phantom Read — Gap Lock
MySQL prevents this issue using Gap Lock.
When Transaction 1 reads data with FOR UPDATE, MySQL applies a Next Key Lock that locks the range id >= 2. Thus, Transaction 2 INSERT is blocked until Transaction 1 completes.
Process
- Transaction 1 queries with FOR UPDATE.
- Transaction 2 attempts to insert data.
- Transaction 2 waits until Transaction 1 completes (or Lock Timeout occurs).
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE --- (NEXT KEY LOCK ID >= 2)
--- | ID: 2 | NAME: 'seonggil'| (count: 1)
--- Transaction 2
INSERT INTO MEMBER VALUES (3, 'newMember') --- Waits until Transaction 1 unlock
commit;
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE
--- | ID: 2 | NAME: 'seonggil'| (count: 1)
SERIALIZABLE
The highest level of isolation, ensuring that transactions are executed sequentially.
Since transactions cannot interleave, data inconsistencies do not occur, but concurrent processing performance is severely reduced.
SELECT queries acquire a Shared Lock. INSERT, UPDATE, and DELETE queries acquire an Exclusive Lock (In MySQL, this is a Next Key Lock).
