Re: Are Enqueue locks implemented with a FIFO queuing mechanism

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 27 Apr 2011 21:32:49 +0100
Message-ID: <116FD319C65F4393BC30ECD5C0F2FE12_at_Primary>

Can you show us the content of v$lock at a moment that this is happening.

select

    sid, type, id1, id2, lmode, request, ctime, block from v$lock order by

    sid, type
;

All the evidence I have ever seen says that enqueues on the same resource are FIFO.
The only **apparent** exception is when a holder attempts to convert and has to wait for another holder.

E.g.

    Session 1 starts to hold TM mode 3
    Session 2 starts to hold TM mode 3
    Session 1 attempts to convert to TM mode 5

This could give the impression that session 1 is waiting out of order. I don't think it should happen with JI resources though.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

• Original Message ----- From: "Matt McClernon" <mccmx_at_hotmail.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, April 27, 2011 1:07 PM Subject: Are Enqueue locks implemented with a FIFO queuing mechanism

Oracle 11gR1 EE on RHEL 4 (x86_64)

Are Oracle enqueue locks obtained in a 'first in, first out' fashion..? I'm specifically interested in 'enqueue - JI' locks - i.e. the exclusive lock obtained to serialzie a materialzized view refresh.

We had a situation recently where a session was waiting to obtain an 'enqeue - JI' resource and was being blocked by sessions which were established after the first session requested access to the lock in question. This behaviour seems unusual (buggy..?) because I expected a fifo type implementation with enqueue resources in Oracle.

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1209 / Virus Database: 1500/3599 - Release Date: 04/26/11

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

Received on Wed Apr 27 2011 - 15:32:49 CDT