sp_lock

Description

Reports information about processes that currently hold locks.

Syntax

sp_lock [spid1 [, spid2]]

Parameters

spid1

is the Adaptive Server process ID number from the master.dbo.sysprocesses table. Run sp_who to get the spid of the locking process.

spid2

is another Adaptive Server process ID number to check for locks.

Examples

Example 1

This example 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
 

Example 2

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

Usage

Permissions

Any user can execute sp_lock.

See also

Commands kill, select

System procedures sp_familylock, sp_who