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