sp_familylock

Reports information about all the locks held by a family (coordinating process and its worker processes) executing a statement in parallel.

Syntax

sp_familylock [fpid1 [, fpid2]]

Parameters

Examples

Usage

There are additional considerations when using sp_familylock:
  • 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 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.

  • 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 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:
    • “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.

Permissions

Any user can execute sp_familylock. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_lock
sp_who