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 |
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?
2
Answers
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.
This table below shows which problems occur in each isolation level on MySQL according to my experiments. *
Yesmeans Occurs,Nomeans Doesn't occur:Actually, your definitions below of non-repeatable read and phantom read are wrong and your experiment steps are also wrong:
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:
And, I used
persontable withidandnameas shown below.persontable:Fisrt, for non-repeatable read, I did these correct steps below with MySQL queries:
BEGIN;BEGIN;SELECT * FROM person WHERE id = 2;2 David
David.UPDATE person SET name = 'Tom' WHERE id = 2;DavidtoTom.*Non-repeatable read cannot occur!!
COMMIT;UPDATE person SET name = 'Tom' WHERE id = 2;DavidtoTom.COMMIT;Second, for phantom read, I did these correct steps below with MySQL queries:
BEGIN;BEGIN;SELECT * FROM person;1 John
2 David
persontable.INSERT INTO person VALUES (3, 'Tom');3andTomtopersontable.*Phantom read cannot occur!!
COMMIT;INSERT INTO person VALUES (3, 'Tom');3andTomtopersontable.COMMIT;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
SELECTquery a locking read, as if you had usedFOR SHARE(the same clause was known asLOCK IN SHARE MODEbefore 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
SELECTis 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 TABLESstatements 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.