MySQL InnoDB Isolation Levels
Posted March 18, 2015 - 7 min read
Topics:
mysql
database
transactions
MySQL is a popular relational database which provides many engines for storing application data.
One of the most known MySQL engines are: InnoDB and MyISAM.
The main difference between these two engines, is that InnoDB support transactions while MyISAM does not.
Transactions are a key feature for every relational database which ensures the consistency of the application data and allows a set of operations to be run as one atomic operation.
Behind transactions, there are many concepts to explore more than it looks like at the first sight.
Within this post we are going to discuss transaction isolation as one of the main transaction concepts.
ACID again
First, let’s recall what does it mean ACID for a RDBMS.
InnoDB complies with ACID requirements. This means that InnoDB meets all the following requirements:
Atomic. Requires that each transaction be “all or nothing”: if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged.
Consistent. The consistency property ensures that any transaction will bring the database from one valid state to another.
Isolated.
Session 1 cannot interfere with Session B. This means all the data we update in Session 1 is not visible in transaction B until Session 1 actually commits the data. Providing isolation is the main goal of concurrency control. Depending on the concurrency control method, the effects of an incomplete transaction might not even be visible to another transaction.
– Wikipedia
- Durable.
The durability property ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter).
– Wikipedia
This post is about the third requirement: ISOLATION.
Suppose I am an editor of a blog and at a given time, I am updating a blog post. Other blog editors at the same time may be editing the same blog post.
The isolation requirement does ensure that my updates do not get lost because of the updates from other editors and vice versa.
Unfortunately this requirement may not be always guaranteed.
As the data is shared between multiple connections, conflicts may occur during a write operation and our database could reach an inconsistent state.
It is like a version control system, GIT for example, managing a code repository.
InnoDB and isolation
Let’s imagine we have 2 sessions running concurrently at the database level, Session 1 and Session 2 and a table my_table with a column named my_column.
Here is our table:
+----+-----------+
| id | my_column |
+----+-----------+
| 1 | 8 |
| 2 | 8 |
+----+-----------+
And our sessions:
Session 1: start transaction
Session 2: start transaction
Session 1: update my_table set my_column=my_column+10;
Session 2: select my_column from my_table;
What would be the value of my_column when Session 2 selects it?
Would it be 8? 18? Or Session 2 would stuck waiting for Session 1 to finish its work? Or maybe something else?
Well, it depends! It depends on what we need and what our database (in our case MySQL) does allow us to do.
In other words, it’s called the transaction isolation level.
Actually InnoDB supports 4 levels of isolation, giving us some level of control about how our isolation level should be strong.
One more thing I need to tell you is that the higher the level is required to be, the more you lost in performance, and the more our sessions will be isolated.
Enough theory! :)
Read Uncommitted (level 1)
This level allows Session 2 to see all uncommitted changes from Session 1.
In other words, this is called “dirty reads”.
Yes, as you can assume, there is no isolation at all.
Therefore, we should be aware that we have all sort of problems which are covered by the rest of levels.
In each same query from Session 2 the value of my_column may be different depending on what is going in Session 1.
This called non-repeatable reads.
Also, you should be aware that rows inserted from Session 1 may disappear when Session 1 rolls back the transaction.
This is called phantom rows.
Example:
Session 1: start transaction;
Session 2: set session transaction isolation level read uncommitted;
Session 2: start transaction;
Session 1: select * from my_table; // my_column = 8
Session 2: select * from my_table; // my_column = 8
Session 1: update my_table set my_column = my_column + 10; // my_column = 18
Session 2: select * from my_table; // my_column = 18
Session 1: rollback;
Session 2: select * from my_table; // my_column = 8
Session 2: commit;
Read Committed (level 2)
This level takes care about dirty reads.
So it does not allow seeing changes from Session 1 until they are committed or rolled back.
But we still have non-repeatable reads and phantom rows which will be addressed by the next level.
Example:
Session 1: start transaction;
Session 2: set session transaction isolation level read committed;
Session 2: start transaction;
Session 1: select * from my_table; // my_column = 8
Session 2: select * from my_table; // my_column = 8
Session 1: update my_table set my_column = my_column + 10; // my_column = 18
Session 2: select * from my_table; // my_column = 8
Session 1: commit
Session 2: select * from my_table; // my_column = 18
Repeatable Read (level 3)
Using this level, we make one step further, after level 2, and make no changes from Session 1 visible to Session 2.
No dirty reads, no phantom rows, and repeatable reads.
Example:
Session 1: start transaction;
Session 2: set session transaction isolation level repeatable read;
Session 2: start transaction;
Session 1: select * from my_table; // my_column = 8
Session 2: select * from my_table; // my_column = 8
Session 1: update my_table set val = val + 10;// my_column = 18
Session 2: select * from my_table;// my_column = 8
Session 1: commit
Session 2: select * from my_table;// my_column = 8, repeatable
Session 2: commit;// my_column = 8
Session 2: select * from my_table;// my_column = 18
Please note that all changes from Session 1 become visible to Session 2 only after Session 2 commits the transaction.
Serializable (level 4)
This is the strongest level. The last one.
The idea behind it is that all records that are updated from Session 1 becomes locked and not available to other sessions (Session 2) until Session 1 commit/roll back its transaction.
So we are strictly sure that no other transactions may read/write the rows we are updating until our work is finished.
All the requirements from previous level are still satisfied.
Please be careful when using this level because as I as said, it costs a huge performance degradation because of the locks that are being used.
Example:
Session 2: set session transaction isolation level serializable;
Session 2: start transaction;
Session 1: select * from my_table; // my_column = 8
Session 1: update my_table set val = val + 10;// my_column = 18
Session 2: select * from my_table; // no output, waiting
Session 1: commit;// unlocked
Session 2: select * from test;// my_column = 8 repeatable
Session 2: commit;
Session 2: select * from test;// my_column = 18
Conclusion
You are now aware about different isolation levels that come into play when dealing with transactions.
I would say that you should be more than happy using the default isolation level that comes with InnoDB engine which is repeatable reads.
You should play with the rest of levels only when there is a strong reason for that.