Informix Tools - Lock Wait
- Name
- Synopsis
- Description
- Options
- Examples
- Sample Output
- Lock Conflict Example I
- Lock Conflict Example II
- Returncodes
- Diagnostics
- Requires
- Author
- Download
lockwt - Analyze Lock Wait Situations in Informix Dynamic Server
lockwt [-r sec]
The program searches thru a pair of sysmaster tables to determine lock situations.
Lock situations normally result from application design problems (restricted isolation levels, transaction logic, ...).
Normally lock situations are difficult to trace when doing performance analysis on production systems,
because they are not permanent and change frequently.
The output of the program reports each user session holding a lock and the session(s) waiting for this
lock to be released.
- -r #sec
This is the number of seconds that specifies the interval.
lockwt will repeat it's search for locks in the given interval.
lockwt -r 10
------------------------------------------------------------------------------
(0) (1) (2) (3) (4) (5) (6) (7) (8) (9)
WAIT SID :PID PROCNAME USERNAME LKTYPE DATABASE:TABLENAME LKOBJ
-------------------------------------------------------------------------------
0 - 13900:12303 p_sim_replac abcadm X host :acthdr row
1 W 53600:23613 requestd abcadm host :acthdr
Colno Purpose
--------------------------------------------------------------------------------
(0) Sequence number
(1) Waiting or not waiting, possible values are:
"-" this session is the holder of the lock and is always listed first
"W" this session(s) are waiting for the above session
(2) Session id of this session in the database server
(3) Process id of the unix-process, remote connections have pid -1
(4) Process name of the unix process. if it is a remote connection
(pid = -1) than no process name will be available
(5) Unix user name of this session
(6) Type of lock, possible values are:
"X" Exclusive Lock
"S" Shared Lock
"U" Update Lock
For additional lock types execute the following sql-statement:
-> select txt from sysmaster:flags_text where tabname = "syslcktab"
(7) Database name
(8) Table name the lock is on. if it is an index lock and the index is detached
from the table (has it's own partition number) than the name of that index
is shown here.
(9) Type of object locked, possible values are:
"table" this is a table lock
"idx" this is an index key lock
"page" this is a page lock
"row" this is a row lock
"???" this means unknown lockobject
------------------------------------------------------------------------------- WAIT SID :PID PROCNAME USERNAME LKTYPE DATABASE:TABLENAME LKOBJ ------------------------------------------------------------------------------- 0 - 13900:12303 p_sim_replac abcadm X host :acthdr row 1 W 53600:23613 requestd abcadm host :acthdr In this example session 13900 (process "p_sim_replac") is holding a lock on a specific row in table "acthdr". Session 53600 is waiting for this lock to be released. Analyze what session 13900 is doing with "onstat -g ses 13900 [-r]".
------------------------------------------------------------------------------- WAIT SID :PID PROCNAME USERNAME LKTYPE DATABASE:TABLENAME LKOBJ ------------------------------------------------------------------------------- 0 W 3894: -1 (remote) eplussv1 X host :salhdr idx 1 W 17048: 3140 tomnoif abcadm host :salhdr 0 - 63296: -1 (remote) eplussv1 X host :sallin_primary_i idx 1 W 3894: -1 (remote) eplussv1 host :sallin_primary_i This example is a little bit more complex. session 17048 is waiting for session 3894 to release the lock on table salhdr. But take a look at the second pair of locks. session 3894 is waiting for session 63296. This is a typical escalating lock situation, because session 3894 is holding a lock another session is waiting for, but session 3894 is also waiting for a lock to be released. Analyze what session 63296 is doing with "onstat -g ses 63296 [-r]".
lock_wt will deliver the following returncodes:
- 0=RET_OK
- 1=RET_ERR
Program finished successfully
Program encountered an error
Debugging can be turned on by setting the environment variable DEBUG_LOCKWT before starting the program. The whole debug information can be saved to a file via i/o-redirection.
Example: -------- export DEBUG_LOCKWT=1 lockwt -r 10 > lockwt.out 2>&1 &
- C-Compiler plus Informix-ClientSDK to compile lockwt
- Informix-ClientSDK or -Connect to execute lockwt