SQL Sever Isolation Levels

These isolation levels allow different types of behavior.

Isolation
level
Dirty
read
Nonrepeatable
read
Phantom
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Serializable No No No

Transactions must be run at an isolation level of repeatable read or higher to prevent lost updates that can occur when two transactions each retrieve the same row, and then later update the row based on the originally retrieved values. If the two transactions update rows using a single UPDATE statement and do not base the update on the previously retrieved values, lost updates cannot occur at the default isolation level of read committed.

msdn.microsoft.com

Controlling a Transaction’s Isolation Level
In a database, a record is referred to as dirty if it has changed (modified) since the last time its table (or view) was opened. When creating a transaction, you can give instructions to the database engine about how to commit, or whether to dismiss, a transaction with regards to a dirty record. To support this, you start with the following formula:
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]
You start with the SET TRANSACTION ISOLATION LEVEL expression and follow it with a value:
  • READ UNCOMMITTED: This value asks that the database engine read the records that are dirty but were not yet committed
  • READ COMMITTED: This value indicates that the dirty records should not be read
  • REPEATABLE READ: This value indicates that the current transaction should ignore dirty records from other transactions and the other transactions don’t have access to the records of this transaction
  • SNAPSHOT
  • SERIALIZABLE
Advertisements