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.
Any locks associated with the particular 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: 00000001 Tran_id: 000000000000001A R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT,NOINTERRUPT PID: 10781 SID:0000000E
Id: 00000002 Tran_id: 0000000000000019 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT,NOINTERRUPT PID: 10781 SID:00000009
Id: 00000003 Tran_id: 0000000000000018 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT,NOINTERRUPT PID:
10781 SID:0000000C
Id: 00000004 Tran_id: 0000334C3362D62B R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT PID:
10781 SID:00000007
Id: 00000005 Tran_id: 0000000000000016 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT,NOINTERRUPT PID:
10781 SID:00000007
Id: 00000006 Tran_id: 0000000000000015 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT,NOINTERRUPT PID:
10781 SID:0000000B
Id: 00000007 Tran_id: 0000000000000014 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT,NOINTERRUPT PID:
10781 SID:00000005
Id: 00000008 Tran_id: 0000000000000013 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/250) Status: NONPROTECT,NOINTERRUPT PID: 10781 SID:00000006
Id: 00000009 Tran_id: 0000000000000012 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (1,0/250) Status: NONPROTECT,NOINTERRUPT PID:
10781 SID:00000001
Id: 00000026 Rsb: 0000003C Gr: N Req: N State: GR PHYS(1)
KEY(AUDIT,LABEL_CACHE)
Id: 0000000A Tran_id: 0000000000000011 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (0,0/0) Status: NONPROTECT,PSHARED PID:10781
SID:00000001
Id: 0000000B Tran_id: 0000000000000010 R_llb: 00000000 R_cnt: 0
Wait: 00000000 Locks: (95,0/250) Status: NONPROTECT,NOINTERRUPT,
SHARED PID:10781 SID:00000001
Id: 0000001F Rsb: 00000034 Gr: IS Req: IS State: GR PHYS(1)
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. The possible values are:
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
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.