As we have already discussed in the article, isolation specifies that each transaction should be executed independently of other transactions. The level specifies how much isolation can be provided to each transaction.
Read Uncommitted
It is the lowest level of isolation in a transaction. At this level, data changed during a transaction, which is essentially uncommitted data, will be directly visible to other transactions.
It can lead to a Dirty Read problem because the data visible during the transaction can either be committed or rollbacked in case of failure.
Read Committed
In the read-committed transactions, data will only be visible to read after the transaction has been committed.
This level may seem okay at first but it has a problem of Non-repeatable Read for other transactions. During a course of a transaction, a non-repeatable read occurs when a row is fetched twice and gives different results each time. The reason is simple. It is because, at the first read, the other transaction didn’t commit the row updates, and on the second read, the update has been committed. This problem can be resolved at the next level by acquiring locks in each transaction.
Non-Repeatable Read
We know that a non-repeatable read occurs when two transactions are executed at the same time. And, one transaction commits a result which the other transaction has already read and on the second read, it gets the committed result.
The problem can be resolved when any transaction has to acquire a read lock if it’s only reading data, and write lock if it needs to write data. In the article Read Lock vs Write Lock, the locks have already been discussed in detail.
Let’s assume two transactions A and B. Transaction A wants to update a row. It has to acquire a write lock which prevents other transactions to acquire a read/write lock. Then transaction B also wants to update some other row but it first needs to query the row that will be changed by transaction A. In order to do that, transaction B has to acquire a read lock which it cannot acquire as transaction A has already acquired a write lock on it.
In short, the locks prevent the problem of non-repeatable read. Hence, this isolation level is called a Non-repeatable read.
However, at this level, we have a new problem known as Phantom Read. A phantom read occurs when we execute a query two times during a transaction and each time we get a different number of rows. It’s because some rows have either been inserted or deleted. In non-repeatable read, we get the same number of rows but the rows have been updated because of update query.
Serializable
It is the highest level of isolation in transactions. Instead of holding the lock on a row, it holds a lock on multiple rows satisfying a WHERE condition. Here, we are holding the range lock.
When we hold a lock on the range then we can avoid the phantom read problem because the newly inserted rows satisfying the condition have to wait for releasing the lock and the same goes for deletion.
Top comments (0)