1. Program Usage
2. When should lockwt be used ?
3. Sample Output Description
4. Lockconflict Example I
5. Lockconflict Example II
Usage: lockwt [-r sec]
-r number of seconds before
output should be refreshed
2. When should lockwt be used ?
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.
Use the "-r" option to repeat the lock-search in a certain time-interval
(like "onstat -r").
-------------------------------------------------------------------------------
(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]".