Skip to content

Latest commit

 

History

History
115 lines (83 loc) · 10.5 KB

sp-lock-transact-sql.md

File metadata and controls

115 lines (83 loc) · 10.5 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_lock (Transact-SQL)
sp_lock reports information about locks.
markingmyname
maghan
randolphwest
07/16/2024
sql
system-objects
reference
sp_lock_TSQL
sp_lock
sp_lock
TSQL

sp_lock (Transact-SQL)

[!INCLUDE SQL Server]

Reports information about locks.

Important

[!INCLUDE ssNoteDepFutureAvoid] To obtain information about locks in the [!INCLUDE ssDEnoversion], use the sys.dm_tran_locks dynamic management view.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_lock
    [ [ @spid1 = ] spid1 ]
    [ , [ @spid2 = ] spid2 ]
[ ; ]

Arguments

[ @spid1 = ] spid1

A [!INCLUDE ssDE] session ID number from sys.dm_exec_sessions for which the user wants locking information. @spid1 is int, with a default of NULL. Execute sp_who to obtain process information about the session. If @spid1 isn't specified, information about all locks is displayed.

[ @spid2 = ] spid2

Another [!INCLUDE ssDE] session ID number from sys.dm_exec_sessions that might have a lock at the same time as @spid1 and about which the user also wants information. @spid2 is int, with a default of NULL.

Return code values

0 (success).

Result set

The sp_lock result set contains one row for each lock held by the sessions specified in the @spid1 and @spid2 parameters. If neither @spid1 nor @spid2 is specified, the result set reports the locks for all sessions currently active in the instance of the [!INCLUDE ssDE].

Column name Data type Description
spid smallint The [!INCLUDE ssDE] session ID number for the process requesting the lock.
dbid smallint The identification number of the database in which the lock is held. You can use the DB_NAME() function to identify the database.
ObjId int The identification number of the object on which the lock is held. You can use the OBJECT_NAME() function in the related database to identify the object. A value of 99 is a special case that indicates a lock on one of the system pages used to record the allocation of pages in a database.
IndId smallint The identification number of the index on which the lock is held.
Type nchar(4) The lock type:

RID = Lock on a single row in a table identified by a row identifier (RID).
KEY = Lock within an index that protects a range of keys in serializable transactions.
PAG = Lock on a data or index page.
EXT = Lock on an extent.
TAB = Lock on an entire table, including all data and indexes.
DB = Lock on a database.
FIL = Lock on a database file.
APP = Lock on an application-specified resource.
MD = Locks on metadata, or catalog information.
HBT = Lock on a heap or B-Tree (HoBT). This information is incomplete in [!INCLUDE ssNoVersion].
AU = Lock on an allocation unit. This information is incomplete in [!INCLUDE ssNoVersion].
Resource nchar(32) The value identifying the resource that is locked. The format of the value depends on the type of resource identified in the Type column:

Type Value: Resource Value
RID: An identifier in the format 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. fileid matches the file_id column in the sys.database_files catalog view.
KEY: A hexadecimal number used internally by the [!INCLUDE ssDE].
PAG: A number in the format fileid:pagenumber, where fileid identifies the file containing the page, and pagenumber identifies the page.
EXT: A number identifying the first page in the extent. The number is in the format fileid:pagenumber.
TAB: No information provided because the table is already identified in the ObjId column.
DB: No information provided because the database is already identified in the dbid column.
FIL: The identifier of the file, which matches the file_id column in the sys.database_files catalog view.
APP: An identifier unique to the application resource being locked. In the format DbPrincipalId:<first two to 16 characters of the resource string><hashed value>.
MD: varies by resource type. For more information, see the description of the resource_description column in sys.dm_tran_locks.
HBT: No information provided. Use the sys.dm_tran_locks dynamic management view instead.
AU: No information provided. Use the sys.dm_tran_locks dynamic management view instead.
Mode nvarchar(8) The lock mode requested. Can be:

NULL = No access is granted to the resource. Serves as a placeholder.
Sch-S = Schema stability. Ensures that a schema element, such as a table or index, isn't dropped while any session holds a schema stability lock on the schema element.
Sch-M = Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
S = Shared. The holding session is granted shared access to the resource.
U = Update. Indicates an update lock acquired on resources that might eventually be updated. It's used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
X = Exclusive. The holding session is granted exclusive access to the resource.
IS = Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
IU = Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
IX = Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
SIU = Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
SIX = Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
UIX = Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
BU = Bulk Update. Used by bulk operations.
RangeS_S = Shared Key-Range and Shared Resource lock. Indicates serializable range scan.
RangeS_U = Shared Key-Range and Update Resource lock. Indicates serializable update scan.
RangeI_N = Insert Key-Range and Null Resource lock. Used to test ranges before inserting a new key into an index.
RangeI_S = Key-Range Conversion lock. Created by an overlap of RangeI_N and S locks.
RangeI_U = Key-Range Conversion lock created by an overlap of RangeI_N and U locks.
RangeI_X = Key-Range Conversion lock created by an overlap of RangeI_N and X locks.
RangeX_S = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_S. locks.
RangeX_U = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_U locks.
RangeX_X = Exclusive Key-Range and Exclusive Resource lock. This is a conversion lock used when updating a key in a range.
Status nvarchar(5) The lock request status:

CNVRT: The lock is being converted from another mode, but the conversion is blocked by another process holding a lock with a conflicting mode.
GRANT: The lock was obtained.
WAIT: The lock is blocked by another process holding a lock with a conflicting mode.

Remarks

Users can control the locking of read operations by:

All distributed transactions not associated with a session are orphaned transactions. The [!INCLUDE ssDE] assigns all orphaned distributed transactions the SPID value of -2, which makes it easier for a user to identify blocking distributed transactions. For more information, see Use Marked Transactions to Recover Related Databases Consistently.

Permissions

Requires VIEW SERVER STATE permission.

Examples

A. List all locks

The following example displays information about all locks currently held in an instance of the [!INCLUDE ssDE].

USE master;
GO
EXEC sp_lock;
GO

B. List a lock from a single-server process

The following example displays information, including locks, about process ID 53.

USE master;
GO
EXEC sp_lock 53;
GO

Related content