"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

This is a side-effect of InnoDB's locking reads. Locking reads always read the most recently committed data, so they work as if you used READ COMMITTED isolation level. Therefore the view of the data in a given transaction (your T2 transaction above, for example), may view new data that wasn't committed at the time the transaction began.

InnoDB implements SERIALIZABLE by implicitly making every SELECT query a locking read, as if you had used FOR SHARE (the same clause was known as LOCK IN SHARE MODE before MySQL 8.0). Therefore all queries are locking reads, and always query in the read-committed fashion.

This is unfortunately in conflict with the documented claim that SERIALIZABLE is like REPEATABLE READ. It isn't, unless you use autocommit mode and therefore a SELECT is non-locking because it's known to be in a read-only transaction.

This is by design in InnoDB. No, there's no way to prevent it, without switching to a different storage engine or a different brand of database.

There is one other method to get a true serializable transaction: use explicit LOCK TABLES statements to implement pessimistic locking. This will ensure only one transaction at a time has access to the table, but it will hinder concurrency.

---

I don't know of a storage engine available for MySQL that supports transactions and prevents phantom reads. Certainly not any of the storage engines provided in a default installation of MySQL.

Of course a non-transactional storage engine like MyISAM or MEMORY doesn't have the phantom read problem, because there's no such thing as uncommitted data in a non-transactional read problem. But I don't suggest using these storage engines, because they don't support ACID properties.

Facebook makes a transactional storage engine called RocksDB, but I can't find documentation on its handling of transaction isolation levels.

Other storage engines shipped with MySQL do not support transactions at all.

I've used one other specific RDBMS that prevents phantom reads: InterBase, aka Firebird. But you probably won't like the solution.

In InterBase, in the example like the one you describe in your question, transaction T2 will block, waiting for T1 to resolve. If T1 rolls back, then T2 can read the rows. The result in T2 is the same as the state of data when T2 began, because the new row was rolled back by T1. But if T1 commits, then T2 cannot read the data, because it would make its view inconsistent. So T2 gets an error.

This is kind of awful. T2 has to wait for T1 to resolve, then eventually gets an error anyway. That's adding insult to injury!

But it does prevent phantom reads.

I think InnoDB was implemented the way it was because the alternative solution used in InterBase was not liked by most people.

Answer 2

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.