2022-09-22

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

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 with MySQL queries as shown below. *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 with MySQL queries as shown below. *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?



No comments:

Post a Comment