Senator Guerra Souty original series calendar,replica hublot blue steel peach pointer collocation of rolex replica Rome digital scale, track type minute replica watches scale shows that the classical model is swiss replica watches incomparable, wearing elegant dress highlights.
mr-ponna.com

 

YOU ARE HERE: HOME Questions What is the significance of isolation levels in SQL server and Why those are needed



Why Isolation levels required in SQL Server?

View(s): 12931

What is the significance of isolation levels in SQL server and Why those are needed?

Answer 1)

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:

  1. Transaction A reads row 1.
  2. Transaction B reads row 1.
  3. Transaction A updates row 1.
  4. 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:

  1. Transaction A inserts row 1 without committing.
  2. Transaction B reads row 1.
  3. Transaction A rolls back row 1.
  4. 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:

  1. Transaction A reads a row of data.
  2. Transaction B modifies this row and commits.
  3. 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. Phantoms: This situation occurs when a row of data matches the first time but does not match subsequent times. Consider the following example:

  1. Transaction A reads two rows based on a Query A where clause.
  2. Transaction B inserts a new row that happens to fall under Transaction A Query A's where clause.
  3. 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.


  Asked in:  Broadridge   Expertise Level:  Intermediate
  Last updated on Wednesday, 30 May 2012
4/5 stars (9 vote(s))

Register Login Ask Us Write to Us Help