Downtime Announcement: Please note the SAP Community Wiki will be unavailable due to a system upgrade on Thursday, September 24th between 6 and 7 AM CEST
Skip to end of metadata
Go to start of metadata

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>

  • No labels