--- title: "sp_lock (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/17/2017" ms.prod: sql ms.prod_service: "database-engine" ms.reviewer: "" ms.technology: system-objects ms.topic: "language-reference" f1_keywords: - "sp_lock_TSQL" - "sp_lock" dev_langs: - "TSQL" helpviewer_keywords: - "sp_lock" ms.assetid: 9eaa0ec2-2ad9-457c-ae48-8da92a03dcb0 author: stevestein ms.author: sstein --- # sp_lock (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2008-xxxx-xxxx-xxx-md](../../includes/tsql-appliesto-ss2008-xxxx-xxxx-xxx-md.md)] Reports information about locks. > [!IMPORTANT] > [!INCLUDE[ssNoteDepFutureAvoid](../../includes/ssnotedepfutureavoid-md.md)] To obtain information about locks in the [!INCLUDE[ssDEnoversion](../../includes/ssdenoversion-md.md)], use the [sys.dm_tran_locks](../../relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql.md) dynamic management view. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ``` sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ] [ ; ] ``` ## Arguments `[ @spid1 = ] 'session ID1'` Is a [!INCLUDE[ssDE](../../includes/ssde-md.md)] session ID number from **sys.dm_exec_sessions** for which the user wants locking information. *session ID1* is **int** with a default value of NULL. Execute **sp_who** to obtain process information about the session. If *session ID1* is not specified, information about all locks is displayed. `[ @spid2 = ] 'session ID2'` Is another [!INCLUDE[ssDE](../../includes/ssde-md.md)] session ID number from **sys.dm_exec_sessions** that might have a lock at the same time as *session ID1* and about which the user also wants information. *session ID2* is **int** with a default value of NULL. ## Return Code Values 0 (success) ## Result Sets 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](../../includes/ssde-md.md)]. |Column name|Data type|Description| |-----------------|---------------|-----------------| |**spid**|**smallint**|The [!INCLUDE[ssDE](../../includes/ssde-md.md)] 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](../../includes/ssnoversion-md.md)].

AU = Lock on an allocation unit. This information is incomplete in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].| |**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](../../includes/ssde-md.md)].

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 DbPrincipleId:\\.

MD: varies by resource type. For more information, see the description of the **resource_description** column in [sys.dm_tran_locks (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql.md).

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, is not 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 may eventually be updated. It is 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: - Using SET TRANSACTION ISOLATION LEVEL to specify the level of locking for a session. For syntax and restrictions, see [SET TRANSACTION ISOLATION LEVEL (Transact-SQL)](../../t-sql/statements/set-transaction-isolation-level-transact-sql.md). - Using locking table hints to specify the level of locking for an individual reference of a table in a FROM clause. For syntax and restrictions, see [Table Hints (Transact-SQL)](../../t-sql/queries/hints-transact-sql-table.md). All distributed transactions not associated with a session are orphaned transactions. The [!INCLUDE[ssDE](../../includes/ssde-md.md)] 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 (Full Recovery Model)](../../relational-databases/backup-restore/use-marked-transactions-to-recover-related-databases-consistently.md). ## Permissions Requires VIEW SERVER STATE permission. ## Examples ### A. Listing all locks The following example displays information about all locks currently held in an instance of the [!INCLUDE[ssDE](../../includes/ssde-md.md)]. ```sql USE master; GO EXEC sp_lock; GO ``` ### B. Listing a lock from a single-server process The following example displays information, including locks, about process ID `53`. ```sql USE master; GO EXEC sp_lock 53; GO ``` ## See Also [sys.dm_tran_locks (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql.md) [DB_NAME (Transact-SQL)](../../t-sql/functions/db-name-transact-sql.md) [KILL (Transact-SQL)](../../t-sql/language-elements/kill-transact-sql.md) [OBJECT_NAME (Transact-SQL)](../../t-sql/functions/object-name-transact-sql.md) [sp_who (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-who-transact-sql.md) [sys.database_files (Transact-SQL)](../../relational-databases/system-catalog-views/sys-database-files-transact-sql.md) [sys.dm_os_tasks (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-os-tasks-transact-sql.md) [sys.dm_os_threads (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-os-threads-transact-sql.md)