Blocking session details missing from ASH in enq TM locking scenario (Doc ID 2999652.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

 Blocking session details missing from ASH in enq TM locking scenario

Please see an example:

-- ---------------------------------------
-- Session 1
-- ---------------------------------------

select sys_context('userenv', 'sid') from dual;

create table t(n1 number);

insert into t values(1);

insert into t values(2);

commit;

-- Do not commit this tx
insert into t values(3);

-- ---------------------------------------
-- Session 2
-- ---------------------------------------

select sys_context('userenv', 'sid') from dual;

-- This will hang due to the open tx in session 1
lock table t in exclusive mode;

-- ---------------------------------------
-- Session 3
-- ---------------------------------------

select sys_context('userenv', 'sid') from dual;

insert into t values(3);

-- ---------------------------------------
-- Session 4
-- ---------------------------------------

set lines 200 pages 999

col event for a28
col sql_opname for a20
col enq_type for a10
col req_mode for a8

select
session_id,
sql_opname,
event,
chr(to_char(bitand(p1, -16777216))/16777215)
||chr(to_char(bitand(p1, 16711680))/65535) enq_type,
to_char(bitand(p1, 65535)) req_mode,
seq#,
blocking_session,
blocking_inst_id,
blocking_session_status,
count(*) samples
from
v$active_session_history
where
sample_time between systimestamp-(10/86400) and systimestamp
and session_id in (select sid from v$session where username = 'TEST')
group by
session_id,
sql_opname,
event,
chr(to_char(bitand(p1, -16777216))/16777215)
||chr(to_char(bitand(p1, 16711680))/65535),
to_char(bitand(p1, 65535)),
seq#,
blocking_session,
blocking_inst_id,
blocking_session_status
/

/*

Example output
~~~~~~~~~~~~~~

In the output below, sid 925 (session 2 / lock table) is blocked by the
insert in session 1 (not shown) and ASH shows all blocking session details
correctly.

However, sid 901 (session 3 / insert) is also blocked, but ASH does not show
any blocking session setails and BLOCKING_SESSION_STATUS is set to GLOBAL.

- Why are the blocking session details not shown in ASH for this enq TM locking
scenario?

- Why is BLOCKING_SESSION_STATUS for sid 901 set to GLOBAL even though all the
locks are taken and requested on the local instance?

SESSION_ID SQL_OPNAME EVENT ENQ_TYPE REQ_MODE SEQ# BLOCKING_SESSION BLOCKING_INST_ID BLOCKING_SE SAMPLES
---------- -------------------- ---------------------------- ---------- -------- ---------- ---------------- ---------------- ----------- ----------
925 LOCK TABLE enq: TM - contention TM 6 123 541 1 VALID 10
901 INSERT enq: TM - contention TM 3 111 GLOBAL 10 <<<<<< no blocking session details!

*/

select * from v$lock
where sid in (select sid from v$session where username = 'TEST')
and type = 'TM'
/

/*

Example output
~~~~~~~~~~~~~~

Contrary to ASH, v$lock fully shows the locking and blocking constellation.

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00007FF31D6A02F8 00007FF31D6A0320 541 TM 270132 0 3 0 990 1 4 <<<<< session 1: lock holder (mode 3)
00007FF31D6A02F8 00007FF31D6A0320 901 TM 270132 0 0 3 915 0 4 <<<<< session 3: insert, requesting mode 3
00007FF31D6A02F8 00007FF31D6A0320 925 TM 270132 0 0 6 969 0 4 <<<<< session 2: lock table, requesting mode 6

*/

Changes

 NA

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.