The Lock Waits report contains information about each lock wait currently in progress. Details include information about the lock holder and requestor and characteristics of the lock held and the lock requested.
call monreport.lockwait;
call monreport.lockwait();
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - current lock waits
--------------------------------------------------------------------------------
Database: SAMPLE
Generated: 08/28/2009 07:16:26
================================================================================
Part 1 - Summary of current lock waits
--------------------------------------------------------------------------------
REQ_APPLICATION LOCK_MODE HLD_APPLICATION LOCK_ LOCK_OBJECT_TYPE
# HANDLE REQUESTED _HANDLE MODE
---- --------------- --------- --------------- ----- ----------------------
1 26 U 21 U ROW
2 25 U 21 U ROW
3 24 U 21 U ROW
4 23 U 21 U ROW
5 22 U 21 U ROW
6 27 U 21 U ROW
================================================================================
...
390 record(s) selected.
Return Status = 0
================================================================================
Part 2: Details for each current lock wait
lock wait #:1
--------------------------------------------------------------------------------
-- Lock details --
LOCK_NAME = 04000500040000000000000052
LOCK_WAIT_START_TIME = 2009-08-28-07.15.31.013802
LOCK_OBJECT_TYPE = ROW
TABSCHEMA = TRIPATHY
TABNAME = INVENTORY
ROWID = 4
LOCK_STATUS = W
LOCK_ATTRIBUTES = 0000000000000000
ESCALATION = N
-- Requestor and holder application details --
Attributes Requestor Holder
------------------- ----------------------------- ----------------------------
APPLICATION_HANDLE 26 21
APPLICATION_ID *LOCAL.tripathy.090828111531 *LOCAL.tripathy.090828111435
APPLICATION_NAME java java
SESSION_AUTHID TRIPATHY TRIPATHY
MEMBER 0 0
LOCK_MODE - U
LOCK_MODE_REQUESTED U -
-- Lock holder current agents --
AGENT_TID = 41
REQUEST_TYPE = FETCH
EVENT_STATE = IDLE
EVENT_OBJECT = REQUEST
EVENT_TYPE = WAIT
ACTIVITY_ID =
UOW_ID =
-- Lock holder current activities --
ACTIVITY_ID = 1
UOW_ID = 1
LOCAL_START_TIME = 2009-08-28-07.14.31.079757
ACTIVITY_TYPE = READ_DML
ACTIVITY_STATE = IDLE
STMT_TEXT =
select * from inventory for update
-- Lock requestor waiting agent and activity --
AGENT_TID = 39
REQUEST_TYPE = FETCH
ACTIVITY_ID = 1
UOW_ID = 1
LOCAL_START_TIME = 2009-08-28-07.15.31.012935
ACTIVITY_TYPE = READ_DML
ACTIVITY_STATE = EXECUTING
STMT_TEXT =
select * from inventory for update