Isolation Levels

Introduction:

This article provides an easy-to-understand view of what Isolation Levels really mean and when to use which level. Isolation Level is a setting that decides how data which is a part of an ongoing transaction is made visible to other transactions.

Before going into know the isolation type let us first have a look of what are the problems that might occur if isolation levels are not set properly.

1. Dirty Read

This occurs when a second transaction selects a row which is being modified by another transaction which is not committed. Since the data is not committed there is a possibility that we might end up reading data which is not the actual one that should reflect.

2. Non-repeatable read

This occurs when a second transaction reads a particular row many times within a single transaction and each time end up with a different value. Take a case for a scenario you read a data and do some manipulation by that time there is another transaction that has modified the same row and after your manipulations you read the row again but find to be changed. This case is called as non-repeatable read.

3. Phantom rows

This occurs when you have already read a range of values during which time they have again changed. The rows that have changed are called as Phantom rows.

Now having know what would each problem cause. Let us look at ways of avoiding or minimizing them with the isolation level settings.

Isolation levels

* Read Uncommitted

This is as good (or bad) as not having any isolation. All data which is uncommitted is readable from any connection. This should not be used unless you have a very good reason to do so.

* Read Committed

This prevents dirty reads. This does not prevent phantoms or non-repeatable reads. This is the default. Although it is the default, it does not mean that this isolation level is ideal for all transactions. One of the regular tendencies amongst techies is to simply use default values without giving it a second thought! I cannot imagine the number of phantom and non-repeatable reads that must be occurring in the world because someone simply used the default value. It is a scary thought to say the least.

This level is obviously more restrictive than the Read Uncommitted level.

* Repeatable read

This prevents dirty reads as well as non-repeatable reads. It does not prevent phantom rows. This is more restrictive than Read Committed level. When I say restrictive what does it mean? It means that the chances of other transactions having to wait for this one to finish are INCREASED. Another way of saying this is Repeatable Read reduces concurrency compared to Read Committed level.

* Serializable

This is the most restrictive of the options. This should never be used as the default level. If you use this one as the default, it will most probably create a single user system!

So now lets look at what isolation level can be used for what problem as given in the table below.

How to set isolation levels:

The syntax to set isolation levels is as given below

SET TRANSACTION ISOLATION LEVEL
{READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

Hope this article gave a understanding of the isolation levels, different problems due to incorrect isolation levels and how to solve them.