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

I had intended the replies to go to Oracle-l as well - so copying them back in.

The query against gv$session join gv$lock shows that there are no sessions holding the TX locks that are being waited for - and the waits are mode 6. Could you just do a listing of

select * from gv$lock where type = 'TX' order by id1, id2, lmode;

Just in case there's a way in which a lock could be held for a session that doesn't exist.

Regards
Jonathan Lewis

         k.inst_id,
         ss.username,
         DECODE (request, 0, 'Holder: ', ' Waiter: ') || k.sid            sess,
         ss.sql_id,
         k.id1,
         k.id2,
         k.lmode,
         k.request,
         k.TYPE,
         SS.LAST_CALL_ET,
         SS.SECONDS_IN_WAIT,
         SS.SERIAL#,
         SS.MACHINE,
         SS.EVENT,
         ss.status,
         P.SPID,
         CASE
             WHEN request > 0
             THEN
                    CHR (BITAND (p1, -16777216) / 16777215)
                 || CHR (BITAND (p1, 16711680) / 65535)
             ELSE
                 NULL
         END                                                              "Name",
         CASE WHEN request > 0 THEN (BITAND (p1, 65535)) ELSE NULL END    "Mode"
    FROM GV$LOCK k, gv$session ss, gv$process p
   WHERE     (k.id1, k.id2, k.TYPE) IN (SELECT ll.id1, ll.id2, ll.TYPE
                                          FROM GV$LOCK ll
                                         WHERE request > 0)
         AND k.sid = ss.sid
         AND K.INST_ID = SS.INST_ID
         AND ss.paddr = p.addr
         AND SS.INST_ID = P.INST_ID

and v.sid = l.sid
and v.type != 'BACKGROUND'
and v.wait_time_micro/1000000 > 30
and v.wait_class != 'Idle'

On Wed, Apr 1, 2020 at 11:28 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>> wrote: 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

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><mailto:jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto: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

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


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