Home -> Mailing Lists -> Oracle-L -> Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) - Blocking locks with no blocker

Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) - Blocking locks with no blocker

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 1 Apr 2020 15:28:02 +0000
Message-ID: <LNXP265MB156248B5F6E673527F2ACC7AA5C90_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM> 






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_at_gmail.com>
Sent: 01 April 2020 16:07


To: Jonathan Lewis


Cc: ORACLE-L


Subject: 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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto: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
Received on Wed Apr 01 2020 - 17:28:02 CEST












Original text of this message