Displaying Locking Information
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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