Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 229

There are 5 isolation levels available in SQL Server. Each level dictates which of the concurrency effects
are allowed.

READ UNCOMMITTED: This level means that a transaction can read rows that have been
modified by other transactions but haven't been committed. This is the least restrictive level. It
allows dirty reads, non-repeatable reads, and phantom reads.

❑

❑ READ COMMITTED: This level means that a transaction cannot read rows that have been mod-

ified but not committed by another transaction. It allows non-repeatable reads, and phantom
reads. It does not allow dirty reads. This is the default isolation level. It usually provides the best
balance between the allowed concurrency effects and the overhead of required for providing this
level of isolation.

Note that READ COMMITTED has a new option with SQL Server 2005. In this version, Microsoft
introduced the READ_COMMITTED_SNAPSHOT database option. When this option is off,
which is the default, SQL Server uses shared locks to prevent other transactions from modifying
rows while the current transaction is executing a read operation. This is the traditional behavior
for READ COMMITTED.

❑

However, when this option is on, SQL Server uses row versioning so that each statement has a
consistent snapshot of the data as it existed when the statement began.

❑

REPEATABLE READ: This level means that a transaction cannot read rows that have been mod-
ified but not committed by another transaction. Also, no other transaction can change the rows
that have been read by the initial transaction until it completes. It allows phantom reads. It does
not allow dirty reads or non-repeatable reads.

❑

SNAPSHOT: This level means that when a transaction starts, any rows read will be a version
of the rows before the transaction started. Any changes that take place on those rows by other
transactions will not be available to the first transaction. It does not allow dirty reads, non-repeatable
reads or phantom reads. Note that the ALLOW_SNAPSHOT_ISOLATION database option must
be enabled before you can utilize the SNAPSHOT isolation level.

❑

❑ SERIALIZABLE: This level means that a transaction cannot read rows that have been modi-

fied but not committed by another transaction. Also, no other transaction can change the rows
that have been read by the initial transaction until it completes. Finally, other transactions can-
not insert new rows which might fall in the range of rows that the current transaction has read
until the current transaction is completed. It does not allow dirty reads, non-repeatable reads, or
phantom reads.

Locks
The discussion of isolation levels and concurrency lead to the topic of locks. As stated earlier, locking is
the mechanism that SQL Server employs to carry out the concurrency controls dictated by the isolation
level. This mechanism is very complex and resource intensive. Generally speaking, as the isolation level
increases in restrictiveness the amount of resources required also increases.

I'm going to attempt to describe that complexity in a simplified and (somewhat) concise manner. Not
everything will be described. For a complete description, see BOL.