जगदीश खोलिया: Isolation levels significance in sql server

Wednesday, May 16, 2012

Isolation levels significance 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. 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.

Example:
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.

No comments: