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 

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 

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 

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.




Other related posts: