Displaying Locking Information

Microsoft® SQL Server™ provides a report of the active locks when the sp_lock system stored procedure is executed.

Here is the result set:

spid dbid ObjId IndId Type Resource Mode Status
1 1 0 0 DB   S GRANT
6 1 0 0 DB   S GRANT
7 1 0 0 DB   S GRANT
8 1 0 0 DB   S GRANT
8 1 1396200024 0 RID 1:1225:2 X GRANT
8 1 1396200024 0 PAG 1:1225 IX GRANT
8 1 1396200024 2 PAG 1:1240 IX GRANT
8 1 21575115 0 TAB   IS GRANT
8 1 1396200024 2 KEY (03000100cb04) X GRANT
8 1 1396200024 0 TAB   IX GRANT

Type Column

The Type column shows the type of the resource currently locked.

Resource type Description
RID Row identifier. Used to individually lock a single row within a table.
KEY Key; a row lock within an index. Used to protect key ranges in serializable transactions.
PAG Data or index page.
EXT Contiguous group of eight data pages or index pages.
TAB Entire table, including all data and indexes.
DB Database.

Resource Column

The Resource column provides information about the resource being locked.

Resource type Description
RID Row identifier of the locked row within the table. The row is identified by a fileid:page:rid combination, where rid is the row identifier on the page.
KEY Hexadecimal number used internally by SQL Server.
PAG Page number. The page is identified by a fileid:page combination, where fileid is the fileid in the sysfiles table, and page is the logical page number within that file.
EXT First page number in the extent being locked. The page is identified by a fileid:page combination.
TAB No information is provided because the ObjId column already contains the object ID of the table.
DB No information is provided because the dbid column already contains the database ID of the database.

In the result set from sp_lock, the RID resource type being locked has a resource description of 1:1225:2. This indicates that row identifier 2, on page number 1225 on fileid 1 has a lock applied to it.

Mode Column

The Mode column describes the type of lock being applied to the resource. The types of locks include any multigranular lock.

Status Column

The Status column shows whether the lock has been obtained (GRANT), is blocking on another process (WAIT), or is being converted to another lock (CNVT). A lock being converted to another lock is held in one mode but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to WAIT.

Other Tools for Monitoring Locking Activity

Using sp_lock to display locking information may not always be feasible when many locks are held and released faster than sp_lock can display them. In this case, SQL Server Profiler can be used to monitor and record locking information. Additionally, Windows NT Performance Monitor can be used to monitor lock activity using the SQL Server Locks Object counter.

To view the current locks

         

See Also
Locks Event Category syslockinfo
SQL Server: Locks Object  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.