Lockstat Command Output – Locks by Lock List
The “Locks by lock list” portion of the lockstat utility prints the lock information sorted by lock list. The first line item reports the lock list identifier. Any locks associated with the specified lock list are listed following the lock list description and indented.
Most lock lists represent transaction units and hold the locks owned by their transactions. Some lock lists are used to hold special server or cache locks required for processing; these lock lists are owned and managed by the DBMS Server or recovery process rather than by user transactions.
Here is sample output from this section:
---------------------------Locks by lock list---------------------
Id: 00F50010 Tran_id: 0000590A0283AE84 R_llb: 00F60000 R_cnt: 0 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (2,0/2000)
PID:17532 SID:00007F7121416700 Stash: (R:6, L:0) Status:
Id: 00009BEA Rsb: 00006688 Gr: S Req: S State: GR PHYS(1) Attr: PHYSICAL,IS_RSBS KEY(MVCC,DB=2944d7d,TABLE=[327,0])
Id: 00009BE9 Rsb: 00006687 Gr: S Req: S State: GR PHYS(1) Attr: PHYSICAL,IS_RSBS KEY(TABLE,DB=2944d7d,TABLE=[327,0])
Id: 00F60000 Tran_id: 0000000000000024 R_llb: 00000000 R_cnt: 1 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (1,0/1000)
PID:17532 SID:00007F7121416700 Stash: (R:7, L:0) Status: NONPROTECT,NOINTERRUPT
Id: 00009C27 Rsb: 000066C5 Gr: IX Req: IX State: GR PHYS(1) Attr: PHYSICAL,IS_RSBS KEY(DATABASE,wiz)
Id: 00F70000 Tran_id: 0000000000000023 R_llb: 00000000 R_cnt: 0 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (0,0/1000)
PID:17532 SID:00007F71214025C0 Stash: (R:0, L:0) Status: NONPROTECT,EWAIT,ESET
(Waiting for event type 00000001.00000000, event value 00000001 -- CROSS_PROCESS)
Id: 00F80000 Tran_id: 0000000000000022 R_llb: 00000000 R_cnt: 0 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (1,0/1000)
PID:17532 SID:00007F71213F9D00 Stash: (R:7, L:0) Status: NONPROTECT,EWAIT,ESET,MULTITHREAD
(Event Thread waiting in process 17532 -- CROSS_PROCESS)
Id: 00009C28 Rsb: 000066C6 Gr: X Req: X State: GR PHYS(1) Attr: PHYSICAL,IS_RSBS KEY(EV_CONNECT,SERVER=447c)
Id: 00F90000 Tran_id: 0000590A0283AE73 R_llb: 00000000 R_cnt: 0 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (0,0/1000)
PID:17532 SID:00007F71213F6B80 Stash: (R:0, L:0) Status: NONPROTECT
Id: 00FA0000 Tran_id: 0000000000000020 R_llb: 00000000 R_cnt: 0 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (0,0/1000)
PID:17532 SID:00007F71214025C0 Stash: (R:0, L:0) Status: NONPROTECT,NOINTERRUPT
Id: 00FB0000 Tran_id: 0000590A0283AE70 R_llb: 00000000 R_cnt: 0 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (0,0/1000)
PID:17532 SID:00007F71213FF800 Stash: (R:0, L:0) Status: NONPROTECT,EWAIT,ESET
(Waiting for 8K write-behind flush event in cache 01040000 -- CROSS_PROCESS)
Id: 00FC0000 Tran_id: 0000590A0283AE6F R_llb: 00000000 R_cnt: 0 S_llb: 00000000 S_cnt: 0 Wait: 00000000 Locks: (0,0/1000)
PID:17532 SID:00007F71213FCA40 Stash: (R:0, L:0) Status: NONPROTECT,EWAIT,ESET
(Waiting for 2K write-behind flush event in cache 01040000 -- CROSS_PROCESS)
Fields are as follows:
Id
Internal lock list identifier (lock list block)
Tran_id
Transaction identifier associated with this lock list. This value correlates to a transaction identifier in the logstat utility output.
R_llb
Related lock list identifier, if not a transaction lock list
R_cnt
Number of related lock list identifiers that this lock list must assure are released before this lock list can be released
Wait
Internal resource block identifier of the lock that is currently blocked
Locks
Made up of three values: total number of locks currently on the list, number of logical locks on the list currently, and total number of locks allowed to be on this list
STATUS
Indicates the current state of the lock list. This is a bitmask with possible bits set including the following:
WAIT—waiting for lock
NONPROTECT—can be released without going through recovery (system lock lists)
ORPHAN—lock list remaining without transaction
EWAIT—waiting for system event
RECOVER—lock list taken over by the recovery process
MASTER—lock list owned by the recovery process
ESET—lock list set on wait queue for event
EDONE—event that lock list is waiting for is done
NOINTERRUPT—lock requests on this list are non-interruptible
PID
Process ID of the lock list owner
SID
Session ID of the lock list owner
Stash
Indicates the stash of resource blocks (R) and floating lock blocks (L) for the lock list. Control blocks in the stash do not reflect active locks or resources, but are counted as In Use because they are reserved for the use of that lock list. (To get the number of blocks across all stashes, add the Rs and Ls.)
The values indented under individual lock lists are lock block values:
Id
Internal Lock block identifier
Rsb
Internal Resource block identifier
Gr
Granted lock mode
Req
Requested lock mode
State
Current state of lock (GR = granted, WT = waiting)
KEY
Information used to identify the resource being locked.
• When checking contention on data pages, the key will contain PAGE, the database ID, the table reltid and reltidx, and the page number.
• ROW is a special type of lock used to reserve space for deleted rows in these core catalogs only: iirelation, iirel_idx, and iiattribute.