In pureScale environment, the MON_GET_APPL_LOCKWAIT table
function might return incorrect HLD_MEMBER.
This can be shown by the following example.
On member 0: insert some rows to table T1, commit
db2 "create table t1(c1 int)"
db2 "insert into t1 values(1)"
db2 "insert into t1 values(2)"
db2 "commit"
On member 1: delete some row from T1, without commit
db2 -m +c "delete from t1 where c1=2"
On member 2: query on T1 using RR isolation, which is blocked by
the uncommitted delete on member 1
db2 "select * from t1 with rr"
The following query using MON_GET_APPL_LOCKWAIT table function
reports HLD_MEMBER is '0' for the lock wait above. instead of
'1'.
db2 "SELECT TIMESTAMP(LOCK_WAIT_START_TIME, 0)
LOCK_WAIT_START_TM , CHAR(LOCK_NAME, 28) NAME ,
SUBSTR(LOCK_OBJECT_TYPE, 1, 10) OBJECT_TYPE , LOCK_MODE MODE ,
LOCK_CURRENT_MODE CUR_MODE , LOCK_MODE_REQUESTED MODE_REQ ,
LOCK_STATUS STATUS , LOCK_ESCALATION ESCAL , INT(LOCK_COUNT)
COUNT , REQ_MEMBER REQ_MBR , INT(REQ_APPLICATION_HANDLE)
R_AGENT_ID , HLD_MEMBER HLD_MBR , INT(HLD_APPLICATION_HANDLE)
H_AGENT_ID , SMALLINT(TBSP_ID) TBSP_ID , SMALLINT(TAB_FILE_ID)
TAB_ID FROM TABLE(MON_GET_APPL_LOCKWAIT(NULL, -2))"
LOCK_WAIT_START_TM NAME OBJECT_TYPE
MODE CUR_MODE MODE_REQ STATUS ESCAL COUNT REQ_MBR
R_AGENT_ID HLD_MBR H_AGENT_ID TBSP_ID TAB_ID
------------------- ---------------------------- -----------
---- -------- -------- ------ ----- ----------- -------
----------- ------- ----------- ------- ------
2020-03-10-15.35.09 FAFF0080000000000000FFFF54 TABLE -
- S W N 0 1 118514
0 - -6 -32768
1 record(s) selected. |
