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

本主題適用於:是SQL Server (從 2008 開始)否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

報告鎖定的相關資訊。Reports information about locks.

重要

未來的 Microsoft SQL Server 版本將移除這項功能。This feature will 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 Database EngineSQL Server Database Engine,使用sys.dm_tran_locks動態管理檢視。 To obtain information about locks in the SQL Server Database EngineSQL 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

[ @spid1 = ] 'session ID1'[ @spid1 = ] 'session ID1'
Database EngineDatabase Engine工作階段識別碼,從sys.dm_exec_sessions ,使用者想要鎖定資訊。Is a Database EngineDatabase Engine session ID number from sys.dm_exec_sessions for which the user wants locking information. session ID1int預設值是 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 = ] '工作階段 ID2'[ @spid2 = ] 'session ID2'
這是另一個 Database EngineDatabase Engine工作階段識別碼,從sys.dm_exec_sessions ,可能會有鎖定為同時session ID1且使用者也需要其相關資訊。Is another Database EngineDatabase 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. 工作階段 ID2int預設值是 NULL。session ID2 is int with a default value of NULL.

傳回碼值Return Code Values

0 (成功)0 (success)

結果集Result Sets

Sp_lock結果集包含一個資料列中指定的工作階段所持有的各個鎖定 @spid1@spid2 參數。The sp_lock result set contains one row for each lock held by the sessions specified in the @spid1 and @spid2 parameters. 如果沒有 @spid1@spid2 指定,結果集會報告鎖定的所有工作階段目前作用中的執行個體 Database EngineDatabase EngineIf neither @spid1 nor @spid2 is specified, the result set reports the locks for all sessions currently active in the instance of the Database EngineDatabase Engine.

資料行名稱Column name 資料類型Data type DescriptionDescription
spidspid smallintsmallint 要求鎖定之處理序的 Database EngineDatabase Engine 工作階段識別碼。The Database EngineDatabase 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 型樹狀目錄索引。HBT = Lock on a heap or B-Tree index. 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

RID:格式為 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 符合file_id中的資料行sys.database_files目錄檢視。fileid matches the file_id column in the sys.database_files catalog view.

KEY: Database EngineDatabase Engine 在內部使用的十六進位號碼。KEY: A hexadecimal number used internally by the Database EngineDatabase 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:用來識別範圍內第一頁的號碼。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.

DB: 無資訊,因為資料庫已經識別dbid資料行。DB: No information provided because the database is already identified in the dbid column.

FIL: 檔案的識別碼,以符合file_id中的資料行sys.database_files目錄檢視。FIL: The identifier of the file, which matches the file_id column in the sys.database_files catalog view.

APP:鎖定之應用程式資源的專屬識別碼。APP: An identifier unique to the application resource being locked. 格式 DbPrincipleId:<前兩個至 16 個字元的資源字串 ><雜湊值 >。In the format DbPrincipleId:<first two to 16 characters of the resource string><hashed value>.

MD:隨資源類型而不同。MD: varies by resource type. 如需詳細資訊,請參閱描述resource_description中的資料行sys.dm_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.dm_tran_locks動態管理檢視。Use the sys.dm_tran_locks dynamic management view instead.

AU:未提供任何資訊。AU: No information provided. 使用sys.dm_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:已取得鎖定。GRANT: The lock was obtained.

WAIT:持有模式衝突的鎖定之另一處理序會封鎖鎖定。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:

PermissionsPermissions

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

範例Examples

A.A. 列出所有鎖定Listing all locks

下列範例會顯示 Database EngineDatabase Engine 的執行個體目前保留之所有鎖定的相關資訊。The following example displays information about all locks currently held in an instance of the Database EngineDatabase Engine.

USE master;  
GO  
EXEC sp_lock;  
GO  

B.B. 列出單一伺服器處理序的鎖定Listing a lock from a single-server process

下列範例會顯示處理序識別碼 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.dm_os_tasks (TRANSACT-SQL ) sys.dm_os_tasks (Transact-SQL)
sys.dm_os_threads (TRANSACT-SQL )sys.dm_os_threads (Transact-SQL)