|
|
|
Permission required: Installation owner.
The lockstat utility displays locking status information for your Ingres installation. It allows you to examine the state of the Lock Database by providing a summary listing and a snapshot of the installation's locking activity.
This tool is useful for finding lock contention and concurrency problems. It will help you identify locking bottlenecks so that you can correct the problem by setting lockmode appropriately or by remodeling the application if it is a fundamental design or program flow problem.
Note: The forms-based ipm utility incorporates the lockstat functions.
The lockstat command has the following format:
lockstat [-help | -summary | -statistics | -lists | -user_lists |
-special_lists | -resources]
Displays all reports. This is the default.
Displays command options online.
Displays locking system quotas.
Displays locking system quotas and locking system summary.
Displays locks by lock list (user and special)
Displays locks by lock list (user)
Displays locks by lock list (special, that is, "NONPROTECT")
Displays locks by resource
|
|
|
The key items to examine in lockstat output are the waiting statistics, including the following:
Note: The lockstat command gives detailed statistics on all locking activity in the installation, so if there is much activity, the quantity of output will be considerable.
|
|
|
The Locking System Quotas portion of the sample output is a summary listing of locking quotas for the installation. All values are cumulative from the time ingstart was run for this iteration of the system.
Here is sample output from this section:
===========Fri Apr 25 13:34:06 Locking System Quotas=========
Total Locks 65000 Total Resources 65000
Locks per transaction 500
Lock hash table 12983 Locks in use 129
Resource hash table 12983 Resources in use 127
Total lock lists 1480 Lock lists in use 57
Fields are as follows:
Maximum number of locks in the installation
Maximum number of lockable resources in the installation
Maximum number of locks that may be acquired by a transaction
Number of hash buckets in the locking system hash table
Number of locks currently in use in the installation
Number of hash buckets in the resource hash table
Total number of countable resources in use
Maximum number of lock lists available
Number of lock lists currently in use
|
|
|
The Locking System Summary portion of the sample output is a summary listing of locking activity for the installation. All values are cumulative from the time ingstart was run for this iteration of the system.
Here is sample output from this section:
===========Fri Nov 13 13:34:06 Locking System Summary========
Create lock list 49395 Release lock list 49327
Request lock 586295 Re-request lock 457810
Convert lock 68157 Release lock 410846
Escalate 10 Lock wait 27489
Convert wait 2 Convert Deadlock 1
Deadlock Wakeups 2218 Max dlk queue len 10
Deadlock Search 1947 Deadlock 134
Cancel 135 Convert Search 2
Allocate CB 1161478 Deallocate CB 1160861
LBK Highwater 3 LLB Highwater 87
SBK Highwater 5 LKB Highwater 428
RBK Highwater 5 RSB Highwater 420
Max Local dlk srch 3 Dlk locks examined 2361
Max rsrc chain len 5 Max lock chain len 5
Callback Wakeups 0 Callbacks Invoked 0
Callbacks Ignored 0
Fields are as follows:
Number of times a lock list was created for server, session, or transaction
Number of times a release of a lock list occurred for a server, session, or transaction
Number of new lock requests that the locking system processed
Number of times an implicit lock conversion request was issued on a resource that the lock list already had locked. Implicit lock conversion requests can occur when a request is made on a page for update that was previously requested for read.
Number of times an explicit lock conversion request is made to change a lock mode on a physical lock from one mode to another. These types of requests occur as a result of a physical lock being converted during an existing transaction to lower or higher modes.
Number of times a specific logical lock is released, as opposed to a full, partial, or physical lock release.
Number of times a partial release occurred to allow lock escalation from page to table level
Number of times a new lock request had to wait to be granted
Number of times an existing lock waited for conversion to a different lock mode
Number of times a request for conversion turned into a deadlock
Number of times the interval-based deadlock detection thread was awakened
Maximum number of waiting lock lists examined by the deadlock detection thread
Number of times a deadlock search was initiated
Number of times that deadlock existed
Number of times a lock request was canceled due to a time-out or interrupt
Number of times a convert deadlock search was initiated. The searches are performed when converting a lock from one mode to another.
Number of locking control block allocations
Number of locking control block deallocations
Maximum number of lock list blocks allocated
Maximum number of lock lists allocated
Maximum number of lock blocks allocated
Maximum number of locks allocated
Maximum number of resource blocks allocated
Maximum number of resources allocated
Maximum number of locks examined to resolve
Number of locks examined by the deadlock detection thread
Maximum length of a resource hash chain
Maximum length of a lock hash chain
The remaining fields are relevant only when the installation has been configured to run with the Distributed Multi-Cache Management (DMCM) protocol:
Number of times the DMCM callback thread was awakened
Number of times callback functions were invoked to resolve a blocking cache lock
Number of blocking cache locks which had already been released by the time the callback function was invoked
|
|
|
The "Locks by lock list" portion of the lockstat utility prints out 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 to set them off.
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:
Internal lock list identifier (lock list block)
Transaction identifier associated with this lock list. This value correlates to a transaction identifier in the logstat utility output.
Related lock list identifier, if not a transaction lock list
Number of related lock list identifiers that this lock list must assure are released before this lock list can be released
Internal resource block identifier of the lock that is currently blocked
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
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
Process ID of the lock list owner
Session ID of the lock list owner
The values indented under individual lock lists are lock block values. These are described in the following table:
Internal Lock block identifier
Internal Resource block identifier
Granted lock mode
Requested lock mode
Current state of lock (GR = granted, WT = waiting)
Information used to identify the resource being locked.
|
|
|
The "Locks by resource" portion of lockstat groups the individual locks by resource block and shows any contention that can lead to query performance problems.
Here is sample output from this section:
---------------------------Locks by resource-----------------------
Id: 00000020 Gr: IS Conv: IS Cbacks 0 Value: <0000000000000002>
KEY(SV_PAGE,DB=00000001,TABLE=[1,0],PAGE=3)
Id: 0000000A Llb: 0000000B Gr: IS Req: IS State: GR PHYS(1)
Id: 00000021 Gr: IS Conv: IS Cbacks 0 Value: <0000000000000000>
KEY(SV_PAGE,DB=00000001,TABLE=[1,0],PAGE=30)
Id: 0000000B Llb: 0000000B Gr: IS Req: IS State: GR PHYS(1)
Id: 00000022 Gr: IS Conv: IS Cbacks 0 Value: <0000000000000000>
KEY(SV_PAGE,DB=00000001,TABLE=[1,0],PAGE=2)
Id: 0000000C Llb: 0000000B Gr: IS Req: IS State: GR PHYS(1)
Id: 00000023 Gr: IS Conv: IS Cbacks 0 Value: <0000000000000000>
KEY(SV_PAGE,DB=00000001,TABLE=[1,0],PAGE=23)
Id: 0000000D Llb: 0000000B Gr: IS Req: IS State: GR PHYS(1)
Id: 00000024 Gr: IS Conv: IS Cbacks 0 Value: <0000000000000000>
KEY(SV_PAGE,DB=00000001,TABLE=[1,0],PAGE=1)
Id: 0000000E Llb: 0000000B Gr: IS Req: IS State: GR PHYS(1)
Id: 00000025 Gr: IS Conv: IS Cbacks 0 Value: <0000000000000000>
KEY(SV_PAGE,DB=00000001,TABLE=[1,0],PAGE=33)
Id: 0000000F Llb: 0000000B Gr: IS Req: IS State: GR PHYS(1)
Fields are as follows:
Internal Resource block identifier
Granted mode of the resource
This field is relevant only when the installation has been configured to run with the Distributed Multi-Cache Management (DMCM) protocol. It describes the number of resource locks that contain DMCM callback information.
Conversion mode requested on the resource
Lock value associated with the resource
Byte string identifying the resource
The indented portions of the resource blocks show the individual lock blocks that are contending for the resource. These lock blocks are described in the following table:
Internal Lock block identifier
Lock list identifier on which this lock resides
Granted mode of the lock
Requested lock mode
Current state of the lock: GR is granted; WT is waiting