Export (0) Print
Expand All

Displaying Locking Information

SQL Server 2000

Microsoft® SQL Server™ 2000 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 lock a single row within a table.
KEY 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. For more information, see Troubleshooting Deadlocks.

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 if 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 Profiler can be used to monitor and record locking information. Additionally, Windows Performance Monitor can be used to monitor lock activity using the SQL Server Locks Object counter.

To view the current locks

Transact-SQL

Enterprise Manager

SQL-DMO

See Also

Locks Event Category

syslockinfo

SQL Server: Locks Object

sp_lock

Show:
© 2014 Microsoft