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

BEGIN TRANSACTION
SELECT * FROM people
WHERE personid = 'B95212DB-D246-DC11-9225-000E7B82B6DD'

Obviously, your settings, system, and results probably won't be identical to mine. However, it is still
helpful to work through the example, as the general principles will work the same way.

You'll need to replace the value for personid with a value that exists in your version of the table.

This starts a transaction without closing it so you can look into what locks it has open. The select state-
ment will require a shared lock. However, under the default isolation level (read committed), shared
locks are only held for the duration of the statement , and as the statement completes quickly it doesn't
make for a good demo. The script changes the isolation level to repeatable read, which holds locks for the
duration of the transaction to make it easier to see what's going on. Open locks can be viewed by querying
the sys.dm_tran_locks DMV.

SELECT request_session_id AS Session,
resource_database_id AS DBID,
Resource_Type,
resource_description AS Resource,
request_type AS Type,
request_mode AS Mode,
request_status AS Status
FROM sys.dm_tran_locks

Session DBID Resource_Type Resource Type Mode Status
------- ---- ------------- -------------- ----- ----- -------
58 7 KEY (2f024a673f11) LOCK S GRANT

58 7 PAGE 1:24892 LOCK IS GRANT

58 7 OBJECT LOCK IS GRANT

From the results you can see that there is a shared lock (S) on the row that's being selected. More accu-
rately, it is on the clustered index key of the selected row, so you know there is a clustered index on the
table. There is also an intent shared lock (IS) at the page and table level.

If you start a different query window and run an update statement to set the dob column to zero:

UPDATE people SET dob = 0

sys.dm_tran_locks will now look something like this:

Session DBID Resource_Type Resource Type Mode Status
------- ---- ------------- -------------- ----- ----- ------
53 7 KEY (410248acfd12) LOCK S GRANT

53 7 PAGE 1:16960 LOCK IS GRANT

53 7 OBJECT LOCK IS GRANT

54 7 PAGE 1:16960 LOCK U GRANT

54 7 PAGE 1:16960 LOCK X CONVERT

54 7 OBJECT LOCK IX GRANT

The new query window connected as session 54, and you can see that an update (U) lock has been granted
on the same page that session 53 has an IS lock on. U lock and IS locks don't conflict, so the U lock was