Lock resource type in sqlserver: RID key PAG ext tab dB fil

Lock resource type in sqlserver: RID key PAG ext tab dB fil

When Detecting deadlocks, The sp_lock stored procedure is used, which contains a column: type, that is, the lock resource type.

Which resources can be locked by sqlserver?

The resources that can be locked include the RID key PAG ext tab dB fil.

The following is an explanation of the meaning of different types of resources. Only by understanding what they mean can we really understand blocking.

RID: The identifier in the format of fileid: pagenumber: RID, where fileid identifies the file containing the page, pagenumber identifies the page containing the row, and rid identifies the specific row on the page.

The fileid matches the file_id column in the SYS. databases_files directory view.

For example, when running sp_lock, a row of resource columns shows that the RID is 1: 6681: 26, and the Status column shows wait,

It means locking resources on the 6,681st rows on the 26th page of the first data file.

To view the data on this page, you can use DBCC page ()

 

Key: the internal hexadecimal number used by the database engine.

This value corresponds to SYS. Partions. hobt_id, indicating that the lock is on an index. You can find this index by querying the corresponding object_id and index_id in the SYS. partitions view.

 

PAG: number in the format of fileid: pagenumber, where fileid identifies the file containing the page, pagenumbe identification page

Ext: the number on the first page of the identification area. The format of this number is fileid: pagenumber

Tab: No information is provided, because the table is identified in the objid column in sp_lock.

DB: No information is provided, because the database is identified in the dbid column in sp_lock.

FIL: the object identifier, which matches the file_id column in the SYS. database_files directory view.

With the names of these resources, you can find the resource on which the blocking occurs