Reports information about all the locks held by a family (coordinating process and its worker processes) executing a statement in parallel.
sp_familylock [fpid1 [, fpid2]]
is the family identifier for a family of worker processes from the master.dbo.sysprocesses table. Run sp_who or sp_lock to get the spid of the parent process.
is the Adaptive Server process ID number for another lock.
Displays information about the locks held by all members of the family with an fid of 5:
sp_familylock 5
fid spid locktype table_id page dbname class context --- ---- ---------- -------- ---- ------ ------------- -------- 5 5 Sh_intent 176003658 0 userdb Non cursor lock Sync-pt duration request 5 5 Sh_intent-blk 208003772 0 userdb Non cursor lock Sync-pt duration request 5 6 Sh_page 208003772 3972 userdb Non cursor lock Sync-pt duration request 5 7 Sh_page 208003772 3973 userdb Non cursor lock Sync-pt duration request 5 8 Sh_page 208003772 3973 userdb Non cursor lock Sync-pt duration request
sp_familylock with no parameter reports information on all processes belonging to families that currently hold locks. The report is identical to the output from sp_lock; however, sp_familylock allows you to generate reports based on the family ID, rather than the process ID. It is useful for detecting family deadlocks.
Use the object_name system function to derive a table’s name from its ID number.
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”).
The “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. The “demand” suffix indicates that the process is attempting to acquire an exclusive lock.
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 as follows:
A zero value indicates that the task represented by the spid is executed in serial. 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. Values for “context” are as follows:
“NULL” means that the task holding this lock is either executing a query in serial or is a query being executed in parallel in transaction isolation level 1.
“FAM_DUR” means that the task holding the lock will hold the lock until the query is complete.
A lock’s context may be “FAM_DUR” 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.
Any user can execute sp_familylock.
