sp_lock (Transact-SQL)sp_lock (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

报告有关锁的信息。Reports information about locks.

重要

此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 若要获取有关 SQL Server 数据库引擎SQL Server Database Engine 中的锁的信息,请使用_tran_locks动态管理视图。To obtain information about locks in the SQL Server 数据库引擎SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ]  
[ ; ]  

参数Arguments

@no__t 是用户要锁定信息的sys.databases _exec_sessions中的 @no__t 1 会话 ID 号。[ @spid1 = ] 'session ID1' Is a 数据库引擎Database Engine session ID number from sys.dm_exec_sessions for which the user wants locking information. SESSION ID1的值为int ,默认值为 NULL。session ID1 is int with a default value of NULL. 执行sp_who以获取有关会话的进程信息。Execute sp_who to obtain process information about the session. 如果未指定SESSION ID1 ,则显示有关所有锁的信息。If session ID1 is not specified, information about all locks is displayed.

[ @spid2 = ] 'session ID2' 是 ID1 _exec_sessions中的另一个 @no__t 的会话 ID 号,该 ID 可能与会话同时具有锁,并且用户还需要信息。[ @spid2 = ] 'session ID2' Is another 数据库引擎Database Engine 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的值为int ,默认值为 NULL。session ID2 is int with a default value of NULL.

返回代码值Return Code Values

0(成功)0 (success)

结果集Result Sets

Sp_lock结果集对于由 @spid1@no__t 4spid2参数中指定的会话所持有的每个锁,都包含一行。The sp_lock result set contains one row for each lock held by the sessions specified in the @spid1 and @spid2 parameters. 如果既没有指定 @spid1 @no__t 也没有指定3spid2 ,则结果集会报告 @no__t 的实例中当前处于活动状态的所有会话的锁。If neither @spid1 nor @spid2 is specified, the result set reports the locks for all sessions currently active in the instance of the 数据库引擎Database Engine.

列名Column name 数据类型Data type 描述Description
spidspid smallintsmallint 请求锁的进程的数据库引擎Database Engine会话 ID 号。The 数据库引擎Database Engine session ID number for the process requesting the lock.
dbiddbid smallintsmallint 保留锁的数据库的标识号。The identification number of the database in which the lock is held. 可以使用 DB_NAME() 函数来标识数据库。You can use the DB_NAME() function to identify the database.
ObjIdObjId intint 持有锁的对象的标识号。The identification number of the object on which the lock is held. 可以在相关数据库中使用 OBJECT_NAME() 函数来标识对象。You can use the OBJECT_NAME() function in the related database to identify the object. 值为 99 时是一种特殊情况,表示用于记录数据库中页分配的其中一个系统页的锁。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.
IndIdIndId smallintsmallint 持有锁的索引的标识号。The identification number of the index on which the lock is held.
类型Type nchar(4)nchar(4) 锁的类型:The lock type:

RID = 表中单个行的锁,由行标识符 (RID) 标识。RID = Lock on a single row in a table identified by a row identifier (RID).

KEY = 索引内保护可串行事务中一系列键的锁。KEY = Lock within an index that protects a range of keys in serializable transactions.

PAG = 数据页或索引页的锁。PAG = Lock on a data or index page.

EXT = 对某区的锁。EXT = Lock on an extent.

TAB = 整个表(包括所有数据和索引)的锁。TAB = Lock on an entire table, including all data and indexes.

DB = 数据库的锁。DB = Lock on a database.

FIL = 数据库文件的锁。FIL = Lock on a database file.

APP = 指定的应用程序资源的锁。APP = Lock on an application-specified resource.

MD = 元数据或目录信息的锁。MD = Locks on metadata, or catalog information.

HBT = 堆或 B 树(HoBT)上的锁。HBT = Lock on a heap or B-Tree (HoBT). SQL ServerSQL Server 中此信息不完整。This information is incomplete in SQL ServerSQL Server.

AU = 分配单元的锁。AU = Lock on an allocation unit. SQL ServerSQL Server 中此信息不完整。This information is incomplete in SQL ServerSQL Server.
资源Resource nchar(32)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

去掉格式为 fileid: pagenumber: rid 的标识符,其中 fileid 标识包含页的文件,pagenumber 标识包含该行的页,rid 标识该页上的特定行。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 匹配database_files目录视图中的file_id列。fileid matches the file_id column in the sys.database_files catalog view.

按键@No__t 在内部使用的十六进制数。KEY: A hexadecimal number used internally by the 数据库引擎Database Engine.

PAG格式为 fileid: pagenumber 的数字,其中 fileid 标识包含该页的文件,pagenumber 标识该页。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. 该数字的格式为 fileid:pagenumber。The number is in the format fileid:pagenumber.

"选项卡由于表已在ObjId列中标识,因此未提供任何信息。TAB: No information provided because the table is already identified in the ObjId column.

数据库由于已在dbid列中标识了数据库,因此未提供任何信息。DB: No information provided because the database is already identified in the dbid column.

FIL文件的标识符,与database_files目录视图中的file_id列相匹配。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. 格式为 DbPrincipleId @no__t: > <hashed > 值,格式为。In the format DbPrincipleId:<first two to 16 characters of the resource string><hashed value>.

MD:随资源类型而变化。MD: varies by resource type. 有关详细信息,请参阅resource_description _tran_locks (transact-sql)中的说明。For more information, see the description of the resource_description column in sys.dm_tran_locks (Transact-SQL).

HBT未提供任何信息。HBT: No information provided. 改用sys.databases _tran_locks动态管理视图。Use the sys.dm_tran_locks dynamic management view instead.

AU未提供任何信息。AU: No information provided. 改用sys.databases _tran_locks动态管理视图。Use the sys.dm_tran_locks dynamic management view instead.
模式Mode nvarchar(8)nvarchar(8) 所请求的锁模式。The lock mode requested. 可以是:Can be:

NULL = 不授予对资源的访问权限。NULL = No access is granted to the resource. 用作占位符。Serves as a placeholder.

Sch-S = 架构稳定性。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 = 架构修改。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 = 共享。S = Shared. 授予持有锁的会话对资源的共享访问权限。The holding session is granted shared access to the resource.

U = 更新。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 = 排他。X = Exclusive. 授予持有锁的会话对资源的独占访问权限。The holding session is granted exclusive access to the resource.

IS = 意向共享。IS = Intent Shared. 指示有意将 S 锁放置在锁层次结构中的某个从属资源上。Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.

IU = 意向更新。IU = Intent Update. 指示有意将 U 锁放置在锁层次结构中的某个从属资源上。Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.

IX = 意向排他。IX = Intent Exclusive. 指示有意将 X 锁放置在锁层次结构中的某个从属资源上。Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.

SIU = 共享意向更新。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 = 共享意向排他。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 = 更新意向排他。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 = 大容量更新。BU = Bulk Update. 用于大容量操作。Used by bulk operations.

RangeS_S = 共享键范围和共享资源锁。RangeS_S = Shared Key-Range and Shared Resource lock. 指示可串行范围扫描。Indicates serializable range scan.

RangeS_U = 共享键范围和更新资源锁。RangeS_U = Shared Key-Range and Update Resource lock. 指示可串行更新扫描。Indicates serializable update scan.

RangeI_N = 插入键范围和 Null 资源锁。RangeI_N = Insert Key-Range and Null Resource lock. 用于在将新键插入索引前测试范围。Used to test ranges before inserting a new key into an index.

RangeI_S = 键范围转换锁。RangeI_S = Key-Range Conversion lock. 由 RangeI_N 和 S 锁的重叠创建。Created by an overlap of RangeI_N and S locks.

RangeI_U = 由 RangeI_N 和 U 锁的重叠创建的键范围转换锁。RangeI_U = Key-Range Conversion lock created by an overlap of RangeI_N and U locks.

RangeI_X = 由 RangeI_N 和 X 锁的重叠创建的键范围转换锁。RangeI_X = Key-Range Conversion lock created by an overlap of RangeI_N and X locks.

RangeX_S = 由 RangeI_N 和 RangeS_S 锁的重叠创建的键范围转换锁RangeX_S = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_S. 住.locks.

RangeX_U = 由 RangeI_N 和 RangeS_U 锁的重叠创建的键范围转换锁。RangeX_U = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_U locks.

RangeX_X = 排他键范围和排他资源锁。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)nvarchar(5) 锁的请求状态:The lock request status:

CNVRT:锁定正在从另一种模式转换,但该转换被另一个具有冲突模式的锁定的进程阻止。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. 数据库引擎Database Engine为所有孤立的分布式事务赋予 SPID 值 -2,这使得用户能够更容易标识阻塞的分布式事务。The 数据库引擎Database Engine 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).

权限Permissions

需要 VIEW SERVER STATE 权限。Requires VIEW SERVER STATE permission.

示例Examples

A.A. 列出所有锁Listing all locks

以下示例显示数据库引擎Database Engine实例当前持有的所有锁的信息。The following example displays information about all locks currently held in an instance of the 数据库引擎Database Engine.

USE master;  
GO  
EXEC sp_lock;  
GO  

B.B. 列出单服务器进程的锁Listing a lock from a single-server process

以下示例显示进程 ID 53 的信息(其中包括锁信息)。The following example displays information, including locks, about process ID 53.

USE master;  
GO  
EXEC sp_lock 53;  
GO  

请参阅See Also

sys.dm_tran_locks (Transact-SQL) sys.dm_tran_locks (Transact-SQL)
DB_NAME (Transact-SQL) DB_NAME (Transact-SQL)
KILL (Transact-SQL) KILL (Transact-SQL)
OBJECT_NAME (Transact-SQL) OBJECT_NAME (Transact-SQL)
sp_who (Transact-SQL) sp_who (Transact-SQL)
sys.database_files (Transact-SQL) sys.database_files (Transact-SQL)
sys.databases _os_tasks (transact-sql) sys.dm_os_tasks (Transact-SQL)
sys.dm_os_threads (Transact-SQL)sys.dm_os_threads (Transact-SQL)