Reports the object names and IDs of processes that currently hold locks.
sp_lock [spid1[, spid2]] | [@verbose = int]
is the Adaptive Server process ID number from the master.dbo.sysprocesses table. Run sp_who to get the spid of the locking process.
is another Adaptive Server process ID number to check for locks.
displays a concatenated name of the table names instead of a spid, such as test..testa, following by the spid.
int can be any
number, as sp_lock only check to see
whether the value of @verbose is null
or not.
Shows the lock status of serial processes with spids 7, 18, and 23 and two families of processes. The family with fid 1 has the coordinating processes with spid 1 and worker processes with spids 8, 9, and 10. The family with fid 11 has the coordinating processes with spid 11 and worker processes with spids 12, 13, and 14:
sp_lock
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users. fid spid locktype table_id page dbname class context --- ---- ------------- ---------- ----- ------- -------------- ------- 0 7 Sh_intent 480004741 0 master Non Cursor Lock NULL 0 18 Ex_intent 16003088 0 pubtune Non Cursor Lock NULL 0 18 Ex_page 16003088 587 pubtune Non Cursor Lock NULL 0 18 Ex_page 16003088 590 pubtune Non Cursor Lock NULL 0 18 Ex_page 16003088 1114 pubtune Non Cursor Lock NULL 0 18 Ex_page 16003088 1140 pubtune Non Cursor Lock NULL 0 18 Ex_page 16003088 1283 pubtune Non Cursor Lock NULL 0 18 Ex_page 16003088 1362 pubtune Non Cursor Lock NULL 0 18 Ex_page 16003088 1398 pubtune Non Cursor Lock NULL 0 18 Ex_page-blk 16003088 634 pubtune Non Cursor Lock NULL 0 18 Update_page 16003088 1114 pubtune Non Cursor Lock NULL 0 18 Update_page-blk 16003088 634 pubtune Non Cursor Lock NULL 0 23 Sh_intent 16003088 0 pubtune Non Cursor Lock NULL 0 23 Sh_intent 176003658 0 pubtune Non Cursor Lock NULL 0 23 Ex_intent 208003772 0 pubtune Non Cursor Lock NULL 1 1 Sh_intent 176003658 0 tpcd Non Cursor Lock Sync-pt duration request 1 1 Sh_intent-blk 208003772 0 tpcd Non Cursor Lock Sync-pt duration request 1 8 Sh_page 176003658 41571 tpcd Non Cursor Lock NULL 1 9 Sh_page 176003658 41571 tpcd Non Cursor Lock NULL 1 10 Sh_page 176003658 41571 tpcd Non Cursor Lock NULL 11 11 Sh_intent 176003658 0 tpcd Non Cursor Lock Sync-pt duration request 11 12 Sh_page 176003658 41571 tpcd Non Cursor Lock NULL 11 13 Sh_page 176003658 41571 tpcd Non Cursor Lock NULL 11 14 Sh_page 176003658 41571 tpcd Non Cursor Lock NULL
Displays information about the locks currently held by spid 7.
sp_lock 7
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users. fid spid locktype table_id page dbname class context --- ---- --------- --------- ---- ------ ---------------- ---------- 0 7 Sh_intent 480004741 0 master Non Cursor Lock NULL
First, queries the pubs2 database about the ID of its running processes that currently hold locks (1056003762), then queries the pubs2 database using the @verbose option, which returns the object name (master..spt_values) in addition to the process ID:
1> use pubs2 2> go 1> sp_lock 2> go
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users. fid spid loid locktype table_id page row dbname class context --- ---- ---- -------- -------- ---- --- ------ ------ ---------- 0 15 30 Sh_intent 1056003762 0 0 master Non Cursor Lock (1 row affected) (return status = 0)
1> sp_lock @verbose=0 2> go
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users. fid spid loid locktype page row objectName id class context --- ---- ---- -------- ---- --- ------------------ -------- ------ ------- 0 15 30 Sh_intent 0 0 master..spt_values 1056003762 Non Cursor Lock (1 row affected) (return status = 0)
sp_lock with no parameters reports information on all processes that currently hold locks.
The only user control over locking is through the use of the holdlock keyword in the select statement.
Use the object_name system function to derive a table’s name from its ID number.
sp_lock in versions of the Cluster Edition earlier than 15.0.3 displayed information about only the locks associated with the instance on which you issued the stored procedure. sp_lock on Cluster Edition version 15.0.3 and later displays information about all locks in the cluster.
sp_lock output is ordered by fid and then spid.
The loid column identifies unique lock owner ID of the blocking transaction. Even loid values indicate that a local transaction owns the lock. Odd values indicate that an external transaction owns the lock.
The locktype column indicates whether the lock is a shared lock (“Sh” prefix), an exclusive lock (“Ex” prefix) or an update lock, and whether the lock is held on a table (“table” or “intent”) or on a page (“page”).
A “blk” suffix in the “locktype” column indicates that this process is blocking another process that needs to acquire a lock. As soon as this process completes, the other process(es) moves forward. A “demand” suffix in the “locktype” column indicates that the process is attempting to acquire an exclusive lock. See the Performance and Tuning Guide for more information about lock types.
The class column indicates whether a lock is associated with a cursor. It displays one of the following:
“Non Cursor Lock” indicates that the lock is not associated with a cursor.
“Cursor Id number” indicates that the lock is associated with the cursor ID number for that Adaptive Server process ID.
A cursor name indicates that the lock is associated with the cursor cursor_name that is owned by the current user executing sp_lock.
The fid column identifies the family (including the coordinating process and its worker processes) to which a lock belongs. Values for fid are:
A zero value indicates that the task represented by the spid is executed serially. It is not participating in parallel execution.
A nonzero value indicates that the task (spid) holding the lock is a member of a family of processes (identified by fid) executing a statement in parallel. If the value is equal to the spid, it indicates that the task is the coordinating process in a family executing a query in parallel.
The context column identifies the context of the lock. Worker processes in the same family have the same context value. Legal values for “context” are as follows:
“NULL” means that the task holding this lock is either a query executing serially, or is a query executing in parallel in transaction isolation level 1.
“Sync-pt duration request” means that the task holding the lock will hold the lock until the query is complete.
A lock’s context may be “Sync-pt duration request” if the lock is a table lock held as part of a parallel query, if the lock is held by a worker process at transaction isolation level 3, or if the lock is held by a worker process in a parallel query and must be held for the duration of the transaction.
“Ind pg” indicates locks on index pages (allpages-locked tables only)
“Inf key” indicates an infinity key lock (for certain range queries at transaction isolation level 3 on data-only-locked tables)
“Range” indicates a range lock (for range queries at transaction isolation level 3 on data-only-locked tables)
These new values may appear in combination with “Fam dur” (which replaces “Sync pt duration”) and with each other, as applicable.
The row column displays the row number for row-level locks.
sp_lock output also displays the following lock types:
“Sh_row” indicates shared row locks
“Update_row” indicates update row locks
“Ex_row” indicates exclusive row locks
Any user can execute sp_lock. Permission checks do not differ based on the granular permissions settings.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_familylock, sp_who
