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