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.

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

mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
 -------------------------------- --------------------------------- 
| @@SESSION.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 is waiting 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?

CodePudding user response：

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.

---

Re your comment:

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.

CodePudding user response：

Yes, it's impossible to prevent non-repeatable read and phantom read with SERIALIZABLE on MySQL but you can prevent both non-repeatable read and phantom read with REPEATABLE READ which is the default isolation level on MySQL.

This table below shows which problems each isolation level on MySQL can prevent according to my experiments:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Not Prevented	Not Prevented	Not Prevented
READ COMMITTED	Prevented	Not Prevented	Not Prevented
SERIALIZABLE	Prevented	Not Prevented	Not Prevented
REPEATABLE READ	Parevented	Prevented	Prevented