Isolation Ievels

Isolation Levels

• The consistency of the database is maintained with the help of isolation property(one of the property from ACID properties) of transaction.

• The transaction should take place in a system in such a way that it is the only transaction that is accessing the resources in a database system at particular instance.

As we know, concurrent execution of transaction over shared database creates various problems. Following are three commonly occurring anomalies

(1) Dirty read: This occurs when we read the uncommitted data. We should not read the uncommitted data because it causes many errors in the database.

For example - Let there are two transactions T1 and T2.

Step 1: Before T1 and T2 perform any operation Salary = 1000 Rs.

Step 2: T2 Writes the salary as Salary =1200 Rs.

Step 3: T1 Reads immediately the Salary as 1200 Rs.

Step 4: T2 rolls back and now Salary =1000 and commits.

Step 5: Now if T1 reads the Salary then it will get the value as 1000

That means the read operation at step 3 is a dirty read operation because T1 read the data before T2 commits the transaction

(2) Non repeatable read: This problem occurs when a particular transaction sees two different values for the same row within its lifetime.

For example - Let, there are two transactions T1 and T2

Step 1: At time t1, the transaction T1 reads the Salary = 1000 Rs.

Step 2: At time t2 the transaction T2 reads the same salary = 1000 Rs and updates it to 1200 Rs.

Step 3: Then at time t3, the transaction T2 gets committed.

Step 4: Now when the transaction T1 reads the same salary at time t4, it gets different value(as Rs.1200) than what it had read(Rs.1000) at time t1. Now, transaction T1 cannot repeat its reading operation.

Thus inconsistent values are obtained.

(3) Phantom read: It is a special case of non repeatable read. This is a problem in which one of the transaction makes the changes in the database system and due to these changes another transaction can not read the data item which it has read just recently.

For example - Let, there are two transactions T1 and T2

Step 1: At time t1, the transaction T1 reads the value of Salary = 1000 Rs.

Step 2: At time t2, the transaction T2 reads the value of the same salary as 1000Rs.

Step 3: At time t3, the transaction T1 deletes the variable salary.

Step 4: Now at time t4, when T2 again reads the salary it gets error. Now transaction T2 cannot identify the reason why it is not getting the salary value which is read just few time back.

Hence isolation levels are defined so that any two transactions can execute concurrently and integrity of data can be maintained.

• There are four levels of transaction isolation defined by SQL -

   • Serializable:

      • This is the Highest isolation level.

      • Serializable execution is defined to be an execution of operations in whichconcurrently executing transactions appears to be serially executing.

   • Repeatable Read:

      • This is the most restrictive isolation level.

      • The transaction holds read locks on all rows it references..

      • It holds write locks on all rows it inserts, updates, or deletes.

      • Since other transaction cannot read, update or delete these rows, it avoids non repeatable read.

   • Read Committed:

     • This isolation level allows only committed data to be read.

     • Thus it does not allows dirty read (i.e. one transaction reading of data immediately after written by another transaction).

     • The transaction hold a read or write lock on the current row, and thus prevent other rows from reading, updating or deleting it.

• Read Uncommitted:        

     • It is lowest isolation level.

     • In this level, one transaction may read not yet committed changes made by other transaction.

     • This level allows dirty reads.

In this level transactions are not isolated from each other.

To summerize, the above isolation levels -

• SERIALIZABLE - dirty reads, non-repeatable reads and phantoms not allowed; all schedules must be serializable

• REPEATABLE READ - dirty reads, non-repeatable reads not allowed, but phantoms allowed

• READ COMMITTED - dirty reads not allowed, but non-repeatable reads and phantoms allowed