"Non-repeatable read" and "phantom read" occur with 'SERIALIZABLE' isolation level (MySQL)

Question

I experimented if non-repeatable read and phantom read occur or not with SERIALIZABLE on MySQL but against my expectation, both non-repeatable read and phantom read actually occurred with SERIALIZABLE.

These below are the definitions of non-repeatable read and phantom read:

• Non-repeatable read(fuzzy read) is the read of committed updated data during a transaction.

• Phantom read is the read of committed inserted or deleted data during a transaction.

For my experiment, I set SERIALIZABLE globally and sessionly as shown below:

mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
+--------------------------------+---------------------------------+
| @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation |
+--------------------------------+---------------------------------+
| SERIALIZABLE                   | SERIALIZABLE                    |
+--------------------------------+---------------------------------+

And, autocommit is enabled by default as shown below:

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

And, InnoDB is set by default as shown below:

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| person     | InnoDB |
+------------+--------+

And, I used "person" table with "id" and "name" as shown below:

id	name
1	John
2	David

Fisrt, for non-repeatable read, I did these steps below with MySQL queries. *I used MySQL version 8.0.30 and 2 command prompts:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	UPDATE person SET name = 'Tom' WHERE id = 2;		T1 updates "David" to "Tom".
Step 4		SELECT * FROM person WHERE id = 2;	T2 cannot read "person" table where "id" is 2. T2 needs to wait for T1 to commit.
Step 5	COMMIT;	Waiting...	T1 commits.
Step 6		SELECT * FROM person WHERE id = 2; 2 Tom	Now, T2 can read "person" table where "id" is 2 but T2 reads "Tom" instead of "David". *Non-repeatable read occurs!!
Step 7		COMMIT;	T2 commits.

Second, for phantom read, I did these steps below with MySQL queries. *I used MySQL version 8.0.30 and 2 command prompts:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	INSERT INTO person VALUES (3, 'Tom');		T1 inserts the row with 3 and Tom to "person" table.
Step 4		SELECT * FROM person;	T2 cannot read "person" table. T2 is waiting for T1 to commit.
Step 5	COMMIT;	Waiting...	T1 commits.
Step 6		SELECT * FROM person; 1 John 2 David 3 Tom	Now, T2 can read "person" table but T2 reads 3 rows instead of 2 rows. *Phantom read occurs!!
Step 7		COMMIT;	T2 commits.

So, is it impossible to prevent non-repeatable read and phantom read with SERIALIZABLE on MySQL?

Answer 1

No, it's possible to prevent both non-repeatable read and phantom read with SERIALIZABLE on MySQL. In addition, you can also prevent both non-repeatable read and phantom read with REPEATABLE READ which is the default isolation level on MySQL.

The table below shows which problems occur in each isolation level on MySQL according to my experiments. *Yes means Occurs, No means Doesn't occur:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	No
SERIALIZABLE	No	No	No

Actually, your definitions below of non-repeatable read and phantom read are wrong and your experiment steps are also wrong:

• Non-repeatable read(fuzzy read) is the read of committed updated data during a transaction.

• Phantom read is the read of committed inserted or deleted data during a transaction.

These below are the correct definitions of non-repeatable read and phantom read:

• Non-repeatable read(fuzzy read) is that a transaction reads the same row at least twice but the same row's data is different between the 1st and 2nd reads because other transactions update the same row's data and commit at the same time(concurrently).

• Phantom read is that a transaction reads the same table at least twice but the number of the same table's rows is different between the 1st and 2nd reads because other transactions insert or delete rows and commit at the same time(concurrently).

And these below are the experiments of non-repeatable read and phantom read with the correct steps. Actually in the experiments below with SERIALIZABLE, non-repeatable read and phantom read don't occur so if you want to see that they occur, see What is the difference between Non-Repeatable Read and Phantom Read? which has my answer with the experiments with READ COMMITTED to occur them.

For my experiment, I set SERIALIZABLE as shown below:

mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
+--------------------------------+---------------------------------+
| @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation |
+--------------------------------+---------------------------------+
| SERIALIZABLE                   | SERIALIZABLE                    |
+--------------------------------+---------------------------------+

And, I used person table with id and name as shown below.

person table:

id	name
1	John
2	David

Fisrt, for non-repeatable read, I did these correct steps below with MySQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT * FROM person WHERE id = 2; 2 David		T1 reads David.
Step 4		UPDATE person SET name = 'Tom' WHERE id = 2;	T2 needs to wait for T1 to commit to update David to Tom. *Non-repeatable read cannot occur!!
Step 5	COMMIT;	Waiting...	T1 commits.
Step 6		UPDATE person SET name = 'Tom' WHERE id = 2;	Now, T2 can update David to Tom.
Step 7		COMMIT;	T2 commits.

Second, for phantom read, I did these correct steps below with MySQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT * FROM person; 1 John 2 David		T2 read person table.
Step 4		INSERT INTO person VALUES (3, 'Tom');	T2 needs to wait for T1 to commit to insert the row with 3 and Tom to person table. *Phantom read cannot occur!!
Step 5	COMMIT;	Waiting...	T1 commits.
Step 6		INSERT INTO person VALUES (3, 'Tom');	Now, T2 can insert the row with 3 and Tom to person table.
Step 7		COMMIT;	T2 commits.