To get a report on the locks currently being held on Adaptive Server, use sp_lock:
sp_lock
fid spid loid locktype table_id page row dbname context --- ---- ---- ---------------- ---------- ----- --- -------- ---------------- 0 15 30 Ex_intent 208003772 0 0 sales Fam dur 0 15 30 Ex_page 208003772 2400 0 sales Fam dur, Ind pg 0 15 30 Ex_page 208003772 2404 0 sales Fam dur, Ind pg 0 15 30 Ex_page-blk 208003772 946 0 sales Fam dur 0 30 60 Ex_intent 208003772 0 0 sales Fam dur 0 30 60 Ex_page 208003772 997 0 sales Fam dur 0 30 60 Ex_page 208003772 2405 0 sales Fam dur, Ind pg 0 30 60 Ex_page 208003772 2406 0 sales Fam dur, Ind pg 0 35 70 Sh_intent 16003088 0 0 sales Fam dur 0 35 70 Sh_page 16003088 1096 0 sales Fam dur, Inf key 0 35 70 Sh_page 16003088 3102 0 sales Fam dur, Range 0 35 70 Sh_page 16003088 3113 0 sales Fam dur, Range 0 35 70 Sh_page 16003088 3365 0 sales Fam dur, Range 0 35 70 Sh_page 16003088 3604 0 sales Fam dur, Range 0 49 98 Sh_intent 464004684 0 0 master Fam dur 0 50 100 Ex_intent 176003658 0 0 stock Fam dur 0 50 100 Ex_row 176003658 36773 8 stock Fam dur 0 50 100 Ex_intent 208003772 0 0 stock Fam dur 0 50 100 Ex_row 208003772 70483 1 stock Fam dur 0 50 100 Ex_row 208003772 70483 2 stock Fam dur 0 50 100 Ex_row 208003772 70483 3 stock Fam dur 0 50 100 Ex_row 208003772 70483 5 stock Fam dur 0 50 100 Ex_row 208003772 70483 8 stock Fam dur 0 50 100 Ex_row 208003772 70483 9 stock Fam dur 32 13 64 Sh_page 240003886 17264 0 stock 32 16 64 Sh_page 240003886 4376 0 stock 32 17 64 Sh_page 240003886 7207 0 stock 32 18 64 Sh_page 240003886 12766 0 stock 32 18 64 Sh_page 240003886 12767 0 stock 32 18 64 Sh_page 240003886 12808 0 stock 32 19 64 Sh_page 240003886 22367 0 stock 32 32 64 Sh_intent 16003088 0 0 stock Fam dur 32 32 64 Sh_intent 48003202 0 0 stock Fam dur 32 32 64 Sh_intent 80003316 0 0 stock Fam dur 32 32 64 Sh_intent 112003430 0 0 stock Fam dur 32 32 64 Sh_intent 176003658 0 0 stock Fam dur 32 32 64 Sh_intent 208003772 0 0 stock Fam dur 32 32 64 Sh_intent 240003886 0 0 stock Fam dur
This example shows the lock status of serial processes and two parallel processes:
spid 15 hold an exclusive intent lock on a table, one data page lock, and two index page locks. A “blk” suffix indicates that this process is blocking another process that needs to acquire a lock; spid 15 is blocking another process. As soon as the blocking process completes, the other processes move forward.
spid 30 holds an exclusive intent lock on a table, one lock on a data page, and two locks on index pages.
spid 35 is performing a range query at isolation level 3. It holds range locks on several pages and an infinity key lock.
spid 49 is the task that ran sp_lock; it holds a shared intent lock on the spt_values table in master while it runs.
spid 50 holds intent locks on two tables, and several row locks.
fid 32 shows several spids holding locks: the parent process (spid 32) holds shared intent locks on 7 tables, while the worker processes hold shared page locks on one of the tables.
The lock type column indicates not only whether the lock is a shared lock (“Sh” prefix), an exclusive lock (“Ex” prefix), or an “Update” lock, but also whether it is held on a table (“table” or “intent”) or on a “page” or “row.”
A “demand” suffix indicates that the process will acquire an exclusive lock as soon as all current shared locks are released.
See the System Administration Guide for more information on demand locks.
The context column consists of one or more of the following values:
“Fam dur” means that the task will hold the lock until the query completes, that is, for the duration of the family of worker processes. Shared intent locks are an example of Fam dur locks.
For a parallel query, the coordinating process always acquires a shared intent table lock that is held for the duration of the parallel query. If the parallel query is part of a transaction, and earlier statements in the transaction performed data modifications, the coordinating process holds family duration locks on all of the changed data pages.
Worker processes can hold family duration locks when the query operates at isolation level 3.
“Ind pg” indicates locks on index pages (allpages-locked tables only).
“Inf key” indicates an infinity key lock, used on data-only-locked tables for some range queries at transaction isolation level 3.
“Range” indicates a range lock, used for some range queries at transaction isolation level 3.
To see lock information about a particular login, give the spid for the process:
sp_lock 30
fid spid loid locktype table_id page row dbname context --- ---- ---- ---------------- ---------- ----- --- -------- ---------------- 0 30 60 Ex_intent 208003772 0 0 sales Fam dur 0 30 60 Ex_page 208003772 997 0 sales Fam dur 0 30 60 Ex_page 208003772 2405 0 sales Fam dur, Ind pg 0 30 60 Ex_page 208003772 2406 0 sales Fam dur, Ind pg
If the spid you specify is also the fid for a family of processes, sp_who prints information for all of the processes.
You can also request information about locks on two spids:
sp_lock 30, 15
fid spid loid locktype table_id page row dbname context --- ---- ---- ---------------- ---------- ----- --- -------- ---------------- 0 15 30 Ex_intent 208003772 0 0 sales Fam dur 0 15 30 Ex_page 208003772 2400 0 sales Fam dur, Ind pg 0 15 30 Ex_page 208003772 2404 0 sales Fam dur, Ind pg 0 15 30 Ex_page-blk 208003772 946 0 sales Fam dur 0 30 60 Ex_intent 208003772 0 0 sales Fam dur 0 30 60 Ex_page 208003772 997 0 sales Fam dur 0 30 60 Ex_page 208003772 2405 0 sales Fam dur, Ind pg 0 30 60 Ex_page 208003772 2406 0 sales Fam dur, Ind pg
