SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 82

Serializable: This serializes access to data and prevents all of the side effects by holding locks
for the duration of the transaction and effectively locking rows that don't even exist yet through
key range locks. This is the most isolated level and the most damaging to high concurrency.

❑

Snapshot: The snapshot isolation level is the only optimistic isolation level available and uses
row versioning rather than locking. It prevents all of the previously mentioned side effects just
like serializable, but it does allow for an update conflict to occur that wouldn't if transactions
were run serially. This conflict occurs when data to be changed inside a snapshot transaction
is changed concurrently by another transaction. Detection occurs automatically and causes a
rollback of the snapshot transaction to prevent a lost update. Snapshot and row versioning will
be covered in more depth after looking at locking in more detail.

❑

Table 4-1 summarizes the isolation levels and possible anomalies.

Table 4-1: Isolation Levels and Possible Anomalies

Isolation Dirty Non-Repeatable Phantom Update Concurrency

level Read Read Read Conflict Model

Read Un-Committed Yes Yes Yes No Pessimistic

Read Committed

1 - Locking No Yes Yes No Pessimistic

2 - Snapshot No Yes Yes No Optimistic

Repeatable Read No No Yes No Pessimistic

Snapshot No No No Yes Optimistic

Serializable No No No No Pessimistic

To use a transaction isolation level other than the default read-committed, run the SET TRANSACTION
ISOLATION LEVEL T-SQL command. For example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

Locks and Locking
Locks are necessary for concurrency in whatever model you choose (assuming you need to read and
write to the database). SQL Server handles locks automatically for you. There are three basic levels of
granularity: row, page, and table. Which one SQL Server uses depends on the isolation level of the
transaction. Each lock acquired takes a certain amount of system resources, so there comes a point
at which it would be more efficient to use a single larger lock rather than many of locks of a smaller
granularity. When this occurs SQL Server will escalate the locks to a table lock.

The process is called lock escalation and it doesn't happen very frequently. When a single statement
within a transaction holds more than 5000 (roughly) locks on a single object, SQL Server will try to