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

Chris,

I think I may have missed the same point from (at least one) of the previous outputs you sent me. I've just checked the flat file you sent me joining gv$lock and gv$sessions - and that's the one which excluded BACKGROUND and therefore made it look as if you waiters without holders.

As far as you're concerned - do you still have any blocking locks with no blocker?

Regards
Jonathan Lewis

INST_ID ADDR                    KADDR                   SID     TYPE    ID1             ID2             LMODE   REQUEST CTIME   BLOCK   CON_ID
1       0000002AFF0ABF18        0000002AFF0ABF98        6956    TX      2555927         56779000        6       0       12960   1       0
3       0000002AFF40C238        0000002AFF40C2B8        4871    TX      92864542        2439787         6       0       29356   1       0
3       0000002AFEC73E18        0000002AFEC73E98        3499    TX      93782036        2876653         6       0       39862   1       0

Regards
Jonathan Lewis

Also have this query which the output is a bit nicer:

  SELECT /*+ RULE */

         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

On Wed, Apr 1, 2020 at 11:56 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:

But then I realised that I'd forgotten to change the name of the table owner and table

Regards
Jonathan Lewis

Here's the output for this modified version of what you asked for. Wasn't sure what you'd like to see from gv$session

select l.*, v.inst_id, v.sid, v.serial#,v.program, v.event, v.wait_time_micro/1000000 as wait_time_secs, v.wait_class from gv$lock l, gv$session v
where v.inst_id = l.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>><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<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: 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>>><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<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><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<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


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


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