Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ 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 )) ; _at_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_at_jlcomp.demon.co.uk> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com>
> Sent: 01 April 2020 14:38
> To: ORACLE-L
> Subject: Weird Situation (12.1.0.2 Exadata Cloud _at_ 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
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l




application/vnd.openxmlformats-officedocument.spreadsheetml.sheet attachment: locked_objects.xlsx



application/vnd.ms-excel attachment: locked_objects.csv