Traceflag 1202 causes the syslocks table to include blocked lock requests as well as granted lock requests.
This can be useful in diagnosing odd locking situations such as shared locks blocking shared locks.
bret-sun2% isql -Usa -P -w999
1> sp_lock
2> go
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users.
fid spid loid locktype
table_id page row dbname class context
----- ---- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------- --------- -------- ---- ------------- ---------------------------- -----------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
0 20 40 Sh_table-blk
977239131 0 0 tempdb Non Cursor Lock
0 29 58 Sh_intent
976003477 0 0 master Non Cursor Lock
(2 rows affected)
(return status = 0)
1> dbcc traceon(1202)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> sp_lock
2> go
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users.
fid spid loid locktype
table_id page row dbname class context
----- ---- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------- --------- -------- ---- ------------- ---------------------------- -----------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
0 20 40 Sh_table-blk
977239131 0 0 tempdb Non Cursor Lock
0 21 42 Ex_intent-demand-request
977239131 0 0 tempdb Non Cursor Lock
0 25 50 Sh_intent-demand-request
977239131 0 0 tempdb Non Cursor Lock
0 29 58 Sh_intent
976003477 0 0 master Non Cursor Lock
(4 rows affected)
(return status = 0)
1>