What was the query you ran to generate the output ?
Can you run a query that joins v$session for the waiting sessions with v$lock
on SID.
Order by sid, type
Break on sid skip 1
Looking at the view dba_blockers it identifies blockers by BLOCK = 1.
I don't have a RAC with me to check but ALL held locks (v$lock) in RAC are
flagged with BLOCK = 2 (as "potential blockers") and MAY NOT go to 1 when they
are blocking cross instance. So the problem may be the definition of
dba_blockers - but I'd have to run up a couple of RAC instances to check - and
you've got at least 3 handy to do a quick test on.
Regards
Jonathan Lewis
________________________________________
From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
Sent: 01 April 2020 16:07
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: Weird Situation (12.1.0.2 Exadata Cloud @ Customer) - Blocking
locks with no blocker
Right now I've got 60 sessions waiting on that enqueue lock.
Running this SQL (redacted a bit):
UPDATE <table> SET REVERSED_ON = SYSTIMESTAMP, REVERSING_ID = :B1 WHERE
SOME_ID IN (SELECT * FROM TABLE(:B2 )) ;
@jonathan - I've attached an Excel sheet with locks, sessions and locking-mode.
We do have some invoicing jobs running when are working on the same partitions
that we're trying to update. But I'm still curious why
blocker/blocking_session is null? If you could help me understand that part,
I'd be most appreciative. I'm beginning to 'feel' like maybe this is expected
type of locking but I want to understand the 'why'.
Also included is a csv of the same if you don't want to crack open the excel
file.
Chris
On Wed, Apr 1, 2020 at 10:30 AM Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:
Since you're looking at gv$ does that mean you're running RAC ?
TX - Row lock contention should be reporting mode 6 I think, but could you
check that in case you're waiting for mode 4.
When a session is waiting, are there other sessions also waiting for the same
TX enqueue ?
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Chris Taylor
<christopherdtaylor1994@xxxxxxxxx<mailto:christopherdtaylor1994@xxxxxxxxx>>
Sent: 01 April 2020 14:38
To: ORACLE-L
Subject: Weird Situation (12.1.0.2 Exadata Cloud @ Customer) - Blocking locks
with no blocker
We've got a situation where we have sessions experiencing "enq: TX - row lock
contention" with no blocking session.
GV$SESSION.BLOCKING_SESSION is null
DBA_WAITERS is empty
DBA_BLOCKERS is empty
I've gotten around this by joining gv$locked_object to gv$session where
session.wait_class='Idle' and wait_time_micro/1000000 > 120 (seconds).
Some of the locks are for sessions with thousands of wait seconds waiting on
sqlnet.
*BUT* the issue is, why isn't oracle able to find the blocking sessions? How
can I dump/trace the blocking session manually?
In Grid Control we see stuff like: "lock deadlock retry" in the wait events for
the sessions waiting on "enq: TX - row lock".
In the session trace files, we see stuff like "unable to determine final
blocker" .
Any thoughts?
Chris
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
