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]]
sp_familylock 5 fid spid locktype table_id partitionid page dbname class context --- ---- ---------- --------- ----------- ---- ------ --------------- ----------------------- 5 5 Sh_intent 176003658 0 0 userdb Non cursor lock Sync-pt duration request 5 5 Sh_intent-blk 208003772 0 0 userdb Non cursor lock Sync-pt duration request 5 6 Sh_page 208003772 0 3972 userdb Non cursor lock Sync-pt duration request 5 7 Sh_page 208003772 0 3973 userdb Non cursor lock Sync-pt duration request 5 8 Sh_page 208003772 0 3973 userdb Non cursor lock Sync-pt duration request
sp_familylock spid locktype table_id partitionid page row… ---- ------------------ ----------- ------------- ----- ---- 0 Ex_partition 576002052 576004423 0 0 0 Sh_partition_intent 1417053053 1417053053 0 0
Table lock and fine-grained lock values for partitionid are 0. partitionid is populated only for partition-level locks.
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.
“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 SAP ASE 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.
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.
“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 holds 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.
See also kill, select in Reference Manual: Commands.
Any user can execute sp_familylock. Permission checks do not differ based on the granular permissions settings.
Values in event and extrainfo columns from the sysaudits table are:
| Information | Values |
|---|---|
Event | 38 |
Audit option | exec_procedure |
Command or access audited | Execution of a procedure |
Information in extrainfo |
|