Skip to main content

Transaction ISOLATION levels

This post is dedicated to another important topic related to DB, the 'Transaction Isolation Levels'. There are four different types of transaction isolation levels. While most of the databases support all these four, but some like PostGRES do not support all. Isolation levels controls how much of the uncommitted data from a particular transaction is visible to other transactions. Before delving more into various isolation levels, lets try to understand various scenarios of inconsistencies that occur when two or more transactions operate on the same data at the same time.

Dirty Read: This is the case when one transaction reads the data which contains uncommitted data from other transaction. If the other transaction rolls back the operation, the first transaction still has that invalid data which leads to inconsistencies.

Non-repeatable Read: This occurs when a same query inside same transaction leads to different results if executed repeatedly. In such situations, one transaction continuously retrieves the data while a different transaction modifies that data.

Phantom Read: This is similar to non-repeatable reads with only exception being that the transaction repeatedly performing the data retrieval operation finds new rows because the other transaction has done that insertion.

As is evident from the various inconsistencies mentioned above, interleaving transactions can create havoc if not managed properly. To help with data, the database engines come with various preventive measures which are known as ISOLATION LEVELs. All these isolation levels along with their approach to handle the inconsistencies mentioned above are discussed below.

READ_UNCOMMITTED: This is the very basic isolation level with the best performance at the cost of several issues. As the name suggests, it allows the uncommitted data from other transactions to be read.

READ_COMMITTED: This doesn't allow reading of uncommitted data from other transactions. But it still has non-repeatable read and phantom read issues.

REPEATABLE_READ: Using this isolation level makes sure that same query inside the same transaction always results in the same data if executed repeatedly. In this way, it eliminates both the dirty reads and non-repeatable reads.

SERIALIZABLE: It is the most restrictive level of the all four and works by applying locks for both reading and writing, basically by making sure the transactions do not work in parallel, but instead in a serialized way. Using this isolation level should be avoided as it takes a toll in the performance of the application.

The table below summarises the relation between isolation levels and their effects on various DB read scenarios.
Dirty Read
Non-repeatable Read
Phantom Read
READ_UNCOMMITTED
O
O
O
READ_COMMITTED
X
O
O
REPEATABLE_READ
X
X
O
SERIALIZABLE
X
X
X


Comments

Post a Comment

Popular posts from this blog

Working in India

The day I started working in SRA India(Indian arm of Japan's Software Research Associates, Inc ), I never thought that I world become an onsite team member in just one and half years. Because, the branch was very small & it was very illogical for a novice like me to think of an onsite tour that time. But the fact was that they would make you do your work in almost Japanese style. The very first day I started coding in SRA India, I was told that the Japanese were simply put - perfectionists. This simple word had a very large inner meaning. The code that you wrote should be totally bug free, robust, modifiable without introducing regression etc. The first project I was assigned to, it took a hell lot days to prepare only the detailed design(Java Doc) for a very tiny function, every molecular level detail was described on that. But somehow I made myself adjusted to such work environment. My performance was good in my batch. And in one day, one of my managers told me of an onsite a...
Post a Comment
Read more

Java collection series - miscellaneous

Java Vector is a legacy class. And it is significantly faster in comparison to a list obtained through Collections.synchronizedList(). Vector has loads of legacy operations and hence the manipulations in Vector needs to be done through the List interface, otherwise you won't be able to replace the implementation at a later time. Arrays.asList() is better choice if the list is of fixed size and any kind of size mutation of the collection results in UnsupportedOperationException. The underlying array is updated whenever the list is updated (or vice-versa), but the array reference isn't retained. Collections.nCopies() is another convenient mini-implementation which can be useful in two ways - initialize a newly created list with n null values (need not be only null values) -  new ArrayList (Collections.nCopies(1000, (Type)null)  grow an existing list -  lovablePets.addAll(Collections.nCopies(69, "fruit bat")) Collections.singleton()/Collectio...
Post a Comment
Read more

DB transaction ACID properties

DB transaction is a combination of different operations. If not performed in a proper manner, different transactions working on the same data at the same time may leave the data in corrupted state, effecting the application. In this article, I am going to illustrate DB transaction ACID properties through an example of money transfer application between two different accounts A and B. To begin with, lets suppose that accounts A and B both have initial balance of $100. ACID stands for Atomicity , Consistency , Isolation and Durability . Let's try to understand these one by one. Atomicity : This is the property that mandates that if a transaction is started, either all the operations which are part of the transaction need to be completed by end of the transaction completion as a single unit of work or none of the operations needs to be completed. It is maintained by transaction management component. If a debit of $10 is made from account A, then the corresponding credit of $10 al...
Post a Comment
Read more