Understanding database isolation levels
As developers for us different levels of Isolation in traditional RDMS are bit difficult to understand. It took me a while to go through online links to understand subtle differences and their implication between different isolation levels in general and then specifically for MySQL.
I have shared some of the important links I found on internet, I am summarising my understanding of some important pieces with demo example .
Isolation Levels : Degree of locking ( OR MVCC levels ) on database records for the concurrent data access. higher the level, more the locking and better degree of isolation. Generally there are four levels of isolation levels Serializable, Repeatable reads, Read committed & Read uncommitted each one with reduced level of isolation and locking( OR MVCC ) overhead.
Serializable : Highest level of Isolation on the offer, all concurrent transactions executes as if each one is executed one at a time i.e. serially
Repeatable read : A notch below Serializable , but offers almost same level of Isolation as Serializable with the exception of fantom reads for some DBs ( not for MySQL ). Since this is level is essentially lockless, in some special cases when same row is being updated concurrently isolation can not be achieved ( as we will see later with demo case)
Read committed : Data which is committed by other transaction can be visible in current transaction .
Read uncommitted : Data which is not even committed by other transaction can be visible in current transaction. This is what we term as Dirty read.
Phantom read : This was bit confusing for me, while your reads within transaction are repeatable as long as you use equality match but not guaranteed when reads are over a range of rows (BETWEEN , > ,< etc ) .
Subtle diff between dirty read and phantom read is, while dirty read refers to update on a row by another transaction, phantom read refers to insert or delete of rows by another transaction. If phantom reads can happen or not depends on, if for an isolation level DB chooses to lock all rows in a range query. MySQL for example takes range/gap locks in Serializale and Repeatable read. So in MySQL phantom reads are possible in Read committed or Read uncommited isolation levels.
Armed with this theoretical knowledge lets get into action and explore isolation levels on MySQL, Lets go bottom up
Read uncommitted :
At the start both concurrent transactions T1 & T2 have read same data
Now let T1 debit Marc’s balance by 100, while T1 will now see new balance 100 as expected T2 also sees uncommitted balance of 100 , this is what we call dirty read
Read committed :
As we see uncommitted data is not visible now in T2
Post commit in T1 we now see data is visible on T2
As we see dirty reads are not possible, however in a same transaction you may have non repeatable reads .
Repeatable read :
In above scenario lets check what T2 read sees after T1 has committed transaction .
At the start of both transactions T1 and T2 both sees 200
Post T1 commits debit of 100
As we see T2 still sees 200, so repeatable reads achieved . Let’s try to debit 100 from T2 now and see where we end up .
Now we have ran into the issue, from point of view of T2 this should have been 100, so this is where Serializable comes in
Serializable :
At the start everything looks as it is, T1 is able to read Marc’s balance as 200 and so does T2 . Catch however here is in repeatable read DB is creating and reading from multiple version of same data and in Serializale DB has acquired a wr lock on the row
Lets try update in T1 now
As expected due to wr lock update execution is in waiting
At the same time T2 is able to read Marc’s balance, just fine. To really test isolation lets try an update from T2 now
Transaction is aborted as DB detects deadlock ( both T1 & T2 are waiting to release each others lock ) .
In conclusion :
If you are using mysql or any other database, understanding isolation level supported by database is critical. You should use just the right one for your application and if needed tune it within an application for a special case. Using higher level of isolation when not needed will increase locking and impact performance. At the same time using lower isolation level than required can give you serious nightmares in debugging data corruption issues.
