SQL Server lock Detection

Sometimes the system is running old feeling inefficient, and sometimes SQL has time-out error, but the concurrency is not high. Troubleshoot locating SQL for execution efficiency issues

--open a transaction to keep the lockBEGIN TRAN--UpdateUpdate TableaSetColumn1= 1 whereIdx= 1--List Lock InformationEXECSp_lock@ @spid--committing or rolling back a transactionCOMMIT/ROLLBACK TRAN

View the lock information by executing the sp_lock stored procedure (similar to the following)

spid	dbid	ObjId	Indid	Type	Resource	Mode	Status
52	8	0	0	Db		S	GRANT
52	1	1.12E+09	0	TAB		Is	GRANT
52	8	9.18E+08	1	Pag	1:425,705	Ix	GRANT
52	8	9.18E+08	0	TAB		Ix	GRANT
52	8	9.18E+08	1	KEY	-1E+10	X	GRANT

With dbid, ObjId can find the lock record associated with your updated table
If Indid is 0, the lock is on the table, otherwise on the corresponding index
Through the Type column, you can determine whether the row/table is locked, or something else, and you can see what lock it is through Mode.
In status, you can also see whether the lock has been added or waiting for other resources to be released (in order to acquire the right to lock)

SPID dbid ObjId indid Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------- -------- ------
1 1115151018 0 TAB is GRANT

The types of locks (tyep column values, RIDs, and KEY words, which represent locks on the row) are as follows:
RID = The lock of a single row in the table, identified by a row identifier (RID).
Key = The lock within the index that protects a series of keys in a serial transaction.
PAG = a lock on a data page or index page.
EXT = lock on a zone.
TAB = The lock for the entire table, including all data and indexes.
db = The lock for the database.
FIL = The lock of the database file.
App = The lock for the specified application resource.
MD = lock for metadata or directory information.
HBT = lock for heap or B-tree index. This information is incomplete in SQL Server 2005.
AU = The lock of the allocation unit. This information is incomplete in SQL Server 2005.

The result of the display does not know which object it is, you can use the following statement to view
Select * from sysdatabases where dbid=[dbid]--Database
Select * from sysobjects where Id=[objid]--Tables or other objects
Select * from sysindexes where Id=[indid]--Index
--here [] the value of the corresponding column in the table

SQL Server lock Detection