Displaying Locking Information
You can keep track of information about locks and lock notification requests by using the sys.lock_information view. Sys.lock_information is a virtual table that contains a collection of lock information.
The following table describes the information returned in the result set.
Column Name |
Data Type |
Description |
|---|---|---|
request_spid |
int |
Session that requested the lock. |
resource_type |
nvarchar(10) |
Resource that is being locked: DB: database. TAB: entire table, including all data and indexes. PAG (data): data page. PAG (idx): index page. PAG (lv): data page that contains long values (ntext, image). PAG: page that is used internally by SQL Server Compact 4.0. MD: metadata (in SQL Server Compact 4.0 MD is associated only with Sch-X and Sch-S locks). |
resource_description |
nvarchar(32) |
More identifying information about the resource. Content depends on the lock resource type. PAG: page number (PageId). RID: row identifier of the locked row within the table. The row is identified by a PagId:RowId combination, where RowId is the row identifier on the page. |
request_mode |
nvarchar(5) |
Lock mode that is being requested or that has been granted. S, X, U (and IS, IX, and IU) are the most common locks placed on objects. SCH-S and SCH-X refer to schema modification. |
resource_table |
nvarchar(128) |
Name of the table (if available) that the lock refers to. This is NULL if:
|
resource_table_id |
int |
Internal ID of the table. This is NULL if the resource_type is DB or PAG (lv). |
request_status |
nvarchar(5) |
Status of the lock: GRANT or WAIT |