RE: Are Enqueue locks implemented with a FIFO queuing mechanism
- From: Matt McClernon <mccmx@xxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Jun 2011 12:39:57 +0000
> Can you show us the content of v$lock at a moment that this is happening.
The problem re-occured in production but the dump of v$lock contained
7000 rows. If you really want to see the content let me know and I'll
email you the file as an attachment.
> All the evidence I have ever seen says that enqueues on the same resource are
> FIFO.
Interesting. That's been my understanding (assumption) for years.
However I logged this with Oracle support and one of their senior
performance specialists has stated that enqueues do not work in a FIFO
fashion at all. He is suggesting they are randomised. And this got me
thinking so I did some more digging and uncovered the following:
The CTIME value in v$lock appears to count up to 5 seconds for JI locks
and then timeout and reset to 0. However the CTIME for TM and TX locks
appears to count up continuosly and never resets which suggests that the
locking behaviour is different for different lock types. So this fact alone
makes me doubt Oracle's statement.
But then it crossed my mind that a lock holding session (i.e. the
blocker) probably wouldnt 'post' a waiter in the queue when it releases a
lock (like LGWR does after completing a log file sync). More probably
any sessoin waiting for a JI lock re-checks the availability of the
resource after every reset (timeout) of the wait (every 5 seconds in our JI
case). So
the waiter who times out soonest after the lock becomes available is the
one lucky enough to obtain the resource. Which goes a long way to
explain the random queueing behaviour we are seeing in production on JI
locks.
I've managed to dump the enqueue queues to a trace file with a level 3
enqueue dump and sent the output to Oracle support with the direct
question of whether the order in the output for a given resource
reflects the real order of the queue, and they have categorically said
no.
They said: "Enqueue resources are a linked list created at startup
time. An enqueue dump will be a dump of that linked list , not ordered"
We are getting right down into the low level implementation of locking
behaviour and its difficult to get a comprehensive answer from Oracle support.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
