Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) - Blocking locks with no blocker
From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 1 Apr 2020 09:38:55 -0400
Message-ID: <CAP79kiRu+8PbFCEfm8fy9VwwJaMsAyg6LBiLHoBzthKDOTXQrg_at_mail.gmail.com>
We've got a situation where we have sessions experiencing "enq: TX - row lock contention" with no blocking session.
Date: Wed, 1 Apr 2020 09:38:55 -0400
Message-ID: <CAP79kiRu+8PbFCEfm8fy9VwwJaMsAyg6LBiLHoBzthKDOTXQrg_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 01 2020 - 15:38:55 CEST