Testing MySQL isolation levels

Image for post
Image for post

ACID (Atomic-Consistency-Isolation-Durability) is a set of database transaction properties to guarantee validity even if errors, power failures, etc. (Wikipedia). This is what I learned when sitting at the university. :D

Specifically, the definition of Isolation is: Isolation determines how transaction integrity is visible to other users and systems. (Wikipedia)

In database systems, people defined 4 isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. There are some read phenomena such as Dirty Read, Non-repeatable Read, Phantom Read, and Write Skew. Here is the matrix between isolation levels and read phenomena:

Image for post
Image for post

I fascinated when the first time reading this. I asked myself, how those things happen in real-world situations, so I tried to write some tests to evaluate those “assumptions”.

The method is: I try to stimulate 2 concurrency transactions. Specifically, I open 2 terminal tabs, each tab is opened as one database connection. On the first connection, I will create a new transaction with keyword BEGIN without COMMIT or ROLLBACK to stimulate long-running queries. In the meantime, on terminal 2, I will run some queries for testing.

This test is running on MariaDB (version 10.3.13). For consistency in testing, I create a SQL script is a stored procedure that creates table and sample data. Before every test, we should close all database connection and re-run again CALL generate_schema();

You can download the script at https://gist.github.com/hqt/75f8ae64fb5b79d09aeea108e6123464

  1. Test UNCOMMITTED flag

This test will show that the first transaction can see some “dirty data” on the second transaction, although it hasn’t finished yet.

First terminal: we temporary set isolation level to READ UNCOMMITTED before beginning a transaction. Then we query to get sample data.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT year FROM foo WHERE id = 1; -- return 1993

Second terminal: We create a new transaction, then we update the same record without commit.

BEGIN;
UPDATE foo SET year = 2019 WHERE id = 1;

First terminal: In the same transaction, we try to get again the same record. But at this time, we get new data from the second transaction.

SELECT year FROM foo WHERE id = 1; -- return 2019
COMMIT;

In the real world, this behavior is dangerous because the second transaction can fail and rollback, or we only see the middle state of one transaction. This behavior easily leads to data inconsistency.

If we retry again all above steps but change isolation level from uncommitted to read committed, returned data in the last query still 1993.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. Test READ COMMITTED flag

This test will show that, at read committed isolation level, we have a problem: in the same transaction, a row is retrieved twice and the values differ between reads.

On the first terminal: As test 1, we set the isolation level and select some data.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT COUNT(*) FROM foo WHERE year <= 1995; -- total 3 rows
*************************** 1. row ***************************
count(*): 3
1 row in set (0.000 sec)

On the second terminal: We try to update one record to a new value. This update will affect the result of the previous query. (I always try to wrap in BEGIN/COMMIT because autocommit flag might be turned off on testing database).

BEGIN;
UPDATE foo SET year = 2019 WHERE id = 1;
COMMIT;

Again, on the first terminal, without close transaction, we run again the same query and see that in the same transaction, the same query, data might be different.

SELECT COUNT(*) FROM foo WHERE year <= 1995; -- return 2
COMMIT;
*************************** 1. row ***************************
count(*): 3
1 row in set (0.000 sec)

This problem will be fixed if we move up isolation from committed read to repeatable read.

3. Test REPEATABLE READ flag

On this test, I want to prove that if we use isolation level to REPEATABLE READ, we will have “phantom read” issues: on the same transaction, there are 2 different results for the same query, when we create (not update) new record on other transaction. But it seems “strange”. Let’s see.

On the first terminal, as last 2 tests:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM foo WHERE year <= 1995; -- return 3

On the second terminal, I try to create a new record:

BEGIN;
INSERT INTO `foo` (name, year) VALUES (“foo_foo”, 1995);
COMMIT;

On the first terminal, I try to run again the same query and expect the result will change from 3 to 4, as repeatable read cannot detect phantom read:

SELECT COUNT(*) FROM foo WHERE year <= 1995; -- return 3
*************************** 1. row ***************************
count(*): 3
1 row in set (0.000 sec)

Amazing!!! This is not what we expected. So, I try one more step, try to update inserted record and hopefully, it will fail:

UPDATE foo SET year = 2019 WHERE name = ‘foo_foo’;
COMMIT;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Looks like it doesn’t fail :( So I test again by the final query for confirmation:

SELECT * FROM foo WHERE name = ‘foo_foo’;
*************************** 1. row ***************************
id: 7
name: foo_foo
year: 2019
1 row in set (0.000 sec)

So based on 2 tests, I have some conclusions for MariaDB/MySQL at REPEATABLE isolation level:

  • When using just select statement, phantom read doesn’t happen as SQL standard mentioned.

I have searched and some people have the same result as me. I suggest reading this link (with comments at the end of the blog) because it is more detail and interesting. https://blog.pythian.com/understanding-mysql-isolation-levels-repeatable-read/

4. Test SERIALIZABLE flag

Finally, I try to check the last isolation level: Serializable flag. I don’t know what should I expect at this phrase? Maybe this isolation level will solve the above issue. Hopefully : )

As usual on the first terminal:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT COUNT(*) FROM foo WHERE year <= 1995; -- return 3 as usual

On the second terminal: I try to create a new record.

BEGIN;
INSERT INTO `foo` (name, year) VALUES (“foo_foo”, 1996); — waiting
COMMIT;

OMG. At this step, miracle things happened. We see that the second transaction is waiting. Or, in other words, the second transaction is locked and waiting for the first transaction finish.

So, on the first terminal, we COMMIT; and boom, the second transaction on the second terminal will automatically continue its work and done.

This is a huge step from repeatable read to serializable. : )

Conclusion

This will be the end of my blog. Experience in running is always better than only read and remember books. : ) Moreover, an important lesson I learned from this testing is: there are huge differences between theory on paper and real implementations, as on the “repeatable read” isolation case. So to make sure about everything before applying to the production environment, we should always at least take a try and running some samples/tests on real data.

There are more things that I want to do, and I will add to this blog topic in the future. Some of them are:

  • test those isolation levels on different database vendors such as Postgres, “real” MySQL,… different storage engine such as MyIASM (ahhh, I forgot, MyIASM has no transaction concept :P )

References:

Here are some useful references.

MySQL document, teaching all about isolation levels.

MySQL document, teaching all about types of locking. This document mentions about gap locking — the locking mechanism that handles in case of phantom read.

Chapter 7: Transaction, Design Data-Intensive Application: Discussing detail all types of isolation level with many illustrations. This is a must-read document.

https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321

Another nice reference.

A nice article explained how “write skew” happened in Serializable Snapshot Isolation.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store