Share via


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 CE

MD: metadata (in SQL Server 2005 Compact Edition 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:

  • The tableid is null.
  • The name of the table is not available; for example, if the table is not visible to this transaction, or if the table is an internal table.

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

See Also

Concepts

Locking (SQL Server Compact Edition)
Understanding Locking
Customizing Locking

Help and Information

Getting SQL Server Compact Edition Assistance