Introduction

In order to deep learn MySQL Transaction Isolation. In this blog, I’ll show how to re-produce phantom read issue.

What I will do

• Create a table in DB
• Start 2 MySQL transactions
• In transaction A, the result of SQL will be affected by transaction B

Environment

Database: MySQL
Engine: is InnoDB
Test table:

1
2

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);

How to get transactin’s isolation

1

SELECT @@transaction_isolation;

Reproduce phantom read

Session 1 > Set isolation as REPEATABLE READ

Session 2 > Insert one line.

Session 1 > Failed to select the line which is inserted in session 2

Session 1 > Try to update the line which is inserted in session 2. expect failure, but it is succussed and we can select it in this transaction.

Summary

Phamtom read will happen in REPEATABLE READ isolcation level.