LOCK_TRACE Output
An example of lock_trace output is shown here. The column headings above the example are added in this guide to describe the output.
Action Type Qual. Mode Timeout Key
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (inv,iiattribute,21)
UNLOCK: PAGE Key: (inv,iiattribute,21)
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (inv,iiindex,11)
UNLOCK: PAGE Key: (inv,iiindex,11)
LOCK: TABLE PHYS Mode: IS Timeout: 0 Key: (inv,parts)
LOCK: PAGE Mode: S Timeout: 0 Key: (inv,parts,0)
The lock_trace output is in the following format:
action level qualifiers Mode: Timeout: Key:
where:
Action
Is the action, which can be LOCK, UNLOCK, or CONVERT. For example, a lock was used (LOCK) or released (UNLOCK).
Type
Is the type of lock resource, which can be TABLE, PAGE, ROW, or VALUE. Other strings may appear, such as SV_PAGE or BM_DATABASE, which are internal cache control locks.
Qualifiers
Specify more information about the lock. The qualifier can be:
NOWT--Do not wait if the lock is unavailable.
PHYS--Lock can be released prior to end of transaction (physical lock).
Blank--Lock is held until the transaction commits or aborts (logical lock).
Other qualifiers that may appear have internal meaning only.
Mode
Is the lock mode. Values can be:
S = shared lock
U = update lock
X = exclusive lock
IS = intent share
IX = intent exclusive
N = null lock
SIX = shared intent exclusive
Timeout
Is the default timeout or the SET LOCKMODE TIMEOUT value.
Key
Describes the resource being locked. It consists of the database name, table name, partition and page number (shown as P.p where P is the physical partition number, and p is the page number), and (for row locking) the row number.
For VALUE level locks, the Key is database name, table name, and three numbers describing the value being locked. If the table is partitioned, the table name may be shown as an internal partition name, which looks like “iiXXX ppPPP-table name” where XXX is an internally assigned number, and PPP is the physical partition number. For example:
LOCK: TABLE PHYS Mode: IX Timeout: 0 Key: (emp,ii119 pp2-range_1)