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

granted. As previously discussed, update locks are used to read the data before changing it, so the next
lock is a conversion to an exclusive (x) lock on that same data page. Refreshing the sys.dm_tran_locks
view will show that nothing is changing so have a look at sys.dm_os_waiting_tasks :

SELECT session_id,wait_duration_ms,wait_type,
blocking_session_id,resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id = 54

session_id wait_duration_ms wait_type blocking_session_id
---------- ---------------- --------- -------------------
54 5750212 LCK_M_X 53

This confirms that the update statement is waiting on an exclusive lock (LCK_M_X) which is blocked by
session 53.

Issuing a COMMIT TRANSACTION statement from session 53 clears the shared and intent shared locks and
allows session 54 to get an exclusive lock and complete the update operation.

RowVersioning
Row versioning is the SQL Server 2005 mechanism employed to enable the new snapshot based isolation
levels. It allows for an optimistic concurrency approach based on versioning as opposed to the pessimistic
approach with locking that was previously the only option. SQL Server 2005 also brings a new flavor of
read-committed: the read-committed snapshot. The snapshot and the read-committed snapshot have
been introduced to provide increased concurrency for OLTP environments by preventing writers from
blocking readers and readers from blocking writers.

Row versioning is also used to support the Trigger, Multiple Active Result Sets (MARS) and Online
Indexing features in SQL Server 2005.

To use Snapshot isolation in a database, you must first set an option in the database to enable it. Enable
it by using the ALTER DATABASE command:

ALTER DATABASE people
SET ALLOW_SNAPSHOT_ISOLATION ON

This doesn't mean that it will now start working as the default. After enabling it you still have to set it at
the session level by using:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

The read-committed snapshot isolation level is enabled in a similar way but actually replaces the behavior
of the default read-committed isolation and doesn't need to be explicitly set. This means no applications
changes are required.

ALTER DATABASE people
SET READ_COMMITTED_SNAPSHOT ON

Once enabled, all subsequent transactions running at the read-committed isolation level will default
to snapshot behavior and use versioning for reads rather than locking. Readers will see the previously