Home » SQL & PL/SQL » SQL & PL/SQL » Is DMBS_LOCK.REQUEST First-In First-Out? (DB Version 9.2.0.8.0, OS Windows Server 2003)
Is DMBS_LOCK.REQUEST First-In First-Out? [message #285772] Wed, 05 December 2007 10:04 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I am attempting to set up a queuing system for certain types of request so only one of a certain set of requests will run at a time.

My thought here was just set up a lock, the process needs to hold the lock to run pretty simple solution to implement...but is it fair? Does Oracle's implementation of DMBS_LOCK.REQUEST use a FIFO type queue? Or is it free for all? Or does it maybe depend on the OS?

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #285773 is a reply to message #285772] Wed, 05 December 2007 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What have you learned from your own benchmark tests?
What happens if/when somebody here says it works one way & you latter learn that it works another?

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #285777 is a reply to message #285773] Wed, 05 December 2007 10:23 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
anacedent wrote on Wed, 05 December 2007 10:09

What have you learned from your own benchmark tests?


I have not implemented yet, I am trying to make a decision as to whether I should go with a simple and easy to implement solution where only a lock is involved or a more complex solution in which I build a queue table.

I do not have experience with Oracle's locking mechanism and some quick Google searches on the subject were unfortunately less than helpful since locks are commonly used in developing queue and FIFO is a popular search term with regard to queuing. In other words I got a whole bunch of results which talked about queue and dbms_lock, but could not find anything on whether the locking mechanism itself uses queuing of any kind.

Quote:

What happens if/when somebody here says it works one way & you latter learn that it works another?


I do not really understand what you are implying by the question, I was hoping someone here might have done what I am about to do and that perhaps I could benefit from any knowledge they may have gained in their experience. I am not expecting anyone here to be infallible, but any experience users out there have had would certainly be helpful in making a decision on which direction I want to go, if no one knows one way or another that is OK to I just thought checking to see if someone else had knowledge I did not yet possess would be a good idea.

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #285783 is a reply to message #285772] Wed, 05 December 2007 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

Does Oracle's implementation of DMBS_LOCK.REQUEST use a FIFO type queue?

Yes.

Regards
Michel

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #285784 is a reply to message #285783] Wed, 05 December 2007 10:47 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Thank you

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #287589 is a reply to message #285772] Wed, 12 December 2007 17:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Let us be clear what we mean so I am not confused. I think the suggestion that DBMS_LOCK uses FIFO is misleading (at least I am messed up by it) so let me clarify my understanding and you guys can beat me up on it.

The most common scenario when using user defined locks aka. DBMS_LOCK package, is to create a serialized resource. This means the following:

You create a lock with some name to represent the "thing" to searialize on, using DBMS_LOCK.ALLOCATE_UNIQUE.

You aquire an exclusive mode lock using DBMS_LOCK.REQUEST, thus only one transaction at a time can hold the lock on your named resource.

When you request a lock, you choose to not wait for the lock if you cannot get it right away. This means you will imediately recieve a timeout error if you cannot get the lock.

Under this most common scenario of using DBMS_LOCK, (99% rule I would say), there is no such thing as FIFO. There is no list of people waiting to acquire a lock on your named resource, and the guy who currenly owns it is by definition the first in (and only one in).


Your use of FIFO makes me think maybe you are trying to queue up users by making them wait to acquire a common resource. Using DBMS_LOCK to create a group of users waiting to acquire a specific lock and thus to serialize their activity, works just fine. I have done this myself.

I also have never seen anything in documentation though, that says this group of waiters will be serviced in FIFO order. Michel, is there any way to verify this? Just curious. I have seen hints to this affect in ASKTOMHOME, but Tom has never said so.

In any event, if you don't want to use DBMS_LOCK, you can revert to a new version of an old trick.

update aonerowtable set anumbercolumn = anumbercolumn + 1 returning anumbercolumn into l_your_variable;
do your worst;
commit;

Everyone executing this same code at the same time will queue on the row. I would just go with DBMS_LOCK myself.

This is an interesting question you ask. May I ask, why do you care about this little detail? What kind of application are you creating that FIFO via a lock queue would be so important.

Kevin

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #287649 is a reply to message #287589] Thu, 13 December 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A small test with 4 sessions executing the same code (I only put the code for the first session):
-- Session 1
SQL> declare 
  2    sid   pls_integer;
  3    dummy pls_integer;
  4  begin
  5    select sys_context('USERENV','SID') into sid from dual;
  6    dbms_output.put_line (
  7      to_char(systimestamp,'HH24:MI:SS.FF3')||': SID '||sid||' requests lock');
  8    dummy := dbms_lock.request (42);
  9    dbms_output.put_line (
 10      to_char(systimestamp,'HH24:MI:SS.FF3')||': SID '||sid||' gets lock - waiting 10 seconds');
 11    dbms_lock.sleep (10);
 12    dbms_output.put_line (
 13      to_char(systimestamp,'HH24:MI:SS.FF3')||': SID '||sid||' releases lock');
 14    dummy := dbms_lock.release (42);
 15  end;
 16  /
08:00:43.503: SID 149 requests lock
08:00:43.503: SID 149 gets lock - waiting 10 seconds
08:00:53.503: SID 149 releases lock

PL/SQL procedure successfully completed.

-- Session 2
08:00:45.034: SID 150 requests lock
08:00:53.503: SID 150 gets lock - waiting 10 seconds
08:01:03.503: SID 150 releases lock

-- Session 3
08:00:46.565: SID 159 requests lock
08:01:03.503: SID 159 gets lock - waiting 10 seconds
08:01:13.504: SID 159 releases lock

-- Session 4
08:00:47.675: SID 146 requests lock
08:01:13.504: SID 146 gets lock - waiting 10 seconds
08:01:23.504: SID 146 releases lock

Regards
Michel

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #287749 is a reply to message #285772] Thu, 13 December 2007 06:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
thanks, I understand your code. It is real good.

But in truth I don't think it proves FIFO for lock waiters. This could easily be a case of "well most of the time lock delivery behaves like FIFO when there are waiters for a lock". If I needed an absolute guarantee of FIFO, I would be looking for a statement from Oracle Corp somewhere either in documentation or in a tar.

This certainly does lend support to the idea though.

Let me see if I can find that internals book I had.

Thanks again Michel, nice code.

Kevin

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #287761 is a reply to message #287749] Thu, 13 December 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
dbms_lock works with the same mechanism as all Oracle locks (TM, TX... and so on). It is just an API to open these lock mechanism.
From doc:
Quote:

The DBMS_LOCK package provides an interface to Oracle Lock Management services


Now Metalink note n°22908.1, "What are Latches and What Causes Latch Contention", in its section 2, "Latches vs Enqueues", states that for enqueues it uses the OS lock manager with queues in FIFO mode.

Regards
Michel

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #287768 is a reply to message #285772] Thu, 13 December 2007 07:07 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Kevin,
To your question of why I would need to ensure FIFO, the answer is that I don't, the information was more for myself to know how the locking mechanism would respond in high volume situations, so that if for example I got a call from a user asking why some process, that is going to be effected by this lock, is taking so long to complete I can give them (if it is FIFO) or not give them (if it isn't FIFO) a reasonable answer on how much longer it will be before they are at the front of the line.

So it is not a mission critical requirement just a piece of information that could be useful in determining queue times.

Andrew

Report message to a moderator

Re: Is DMBS_LOCK.REQUEST First-In First-Out? [message #287777 is a reply to message #285772] Thu, 13 December 2007 07:29 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, I yield.

Thanks, this was a good one.

Kevin

Report message to a moderator

Previous Topic: How to find the right partition or its existence to a value
Next Topic: Calculating from multiple tables
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Dec 14 17:18:19 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.