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?
Comments
Post a Comment