जगदीश खोलिया: Isolation levels in Sql Server

Monday, June 4, 2012

Isolation levels in Sql Server

An isolation levels mechanism is used to isolate a resource each transaction in a multi-user
environment. Isolation can be set by obtaining locks on objects.  The correct use of the isolation levels mechanism prevents applications from introducing errors that can occur from the following situations.

1. Lost Updates: This situation occurs when two transactions attempt to update the same data. Consider the following example:
Transaction A reads row 1.
Transaction B reads row 1.
Transaction A updates row 1.
Transaction B updates row 1, overlaying changes applied by Transaction A.

In the above situation, updates performed by Transaction A are lost.

This problem could be avoided if the second Transaction could not make changes until the first Transaction had finished.

2. Dirty Reads: This situation occurs when transactions read data that has not been committed. Consider the following example:
Transaction A inserts row 1 without committing.
Transaction B reads row 1.
Transaction A rolls back row 1.
Transaction B now has a row that physically does not exist.

This problem could be avoided if no one could read the changes until the first Transaction determined that the changes were final.

3. Nonrepeatable Reads: This situation occurs when a transaction reads the same query multiple times and results are not the same each time. Consider the following example:
Transaction A reads a row of data.
Transaction B modifies this row and commits.
Transaction A re-reads the same row and sets back different data values.  (When the record was read for the second time by Transaction A, it has changed).

This problem could be avoided if the Transaction A could read the row only after the Transaction B has finished writing it.

4. Phantom reads: This situation occurs when a row of data matches the first time but does not match subsequent times. Consider the following example:
Transaction A reads two rows based on a Query A where clause.
Transaction B inserts a new row that happens to fall under Transaction A Query A's where clause.
Transaction A runs Query A again and now gets back three rows.

Example2:

Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction's first read of the range of rows shows a row that no longer exists in the second or succeeding read, as a result of a deletion by a different transaction. Similarly, as the result of an insert by a different transaction, the transaction's second or succeeding read shows a row that did not exist in the original read.

The above four phenomenon demonstrate that there is a need to utilize a mechanism called ISOLATION LEVELS.

No comments: