RE: Are Enqueue locks implemented with a FIFO queuing mechanism

> 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.

                                               --
http://www.freelists.org/webpage/oracle-l Received on Tue Jun 28 2011 - 07:39:57 CDT