Thanks for the question.

Asked: October 28, 2001 - 7:11 pm UTC

Last updated: October 15, 2010 - 9:01 am UTC

Version: 8.0.6

Viewed 10K+ times! This question is

You Asked

Tom:

I have a table that contains rows representing work to be "retried". If the status column contains -1 that means the row has to be resubmitted to a process that takes about 2 minutes to complete.

This has to be working 24x7.

Currently, I have 10 different machines each running the same process that is looking for rows with status = -1, and submitting them for "retry" processing. This is the main cursor SQL in each of these processes:

select * from items where status = -1 for update;

When one of the processes receives rows back from this query, it processes the first one of the row set. We're using "for update" in order to keep some other process from grabbing and processing the same row. But, suppose there are 5 rows with status = -1. This SQL locks all 5, and none of the other process instances can get to them. Remember, each retry processing take 2 minutes to complete, so, the result is all these rows are locked up for 2 minutes at a time. Basically, right now, we are getting no payoff from having 10 machines doing this work. Only one row ends up being worked on at a time anyway.

I was thinking about doing something like this in each thread instead (this is pseudo code):

open cursor:
SELECT item_id from items where status = -1;
got_lock = false;
for each rec in cursor
select * from items where item_id = rec.item_id for update nowait;
if (resource not already locked) then
got_lock = true;
exit loop;
end loop;

if (got_lock) then
process_the_retry_that_takes_2_minutes (rec.item_id);
commit;
end if;

This basically looks for any of the rows that are not already locked. Being in a locked state means a thread is already handling it.

Is this the best way to handle the general problem of a queued up set of rows waiting to be processed by several threads? We have the several threads only for the purpose of redundancy and performance, and the volume of rows to be processed is high.

A followup question... the 3rd party API apparently (as far as we can tell now) has no way for us to indicate "nowait" on the cursor. Do you have an idea of how to do this without using "nowait"? I'd still like to also know the right way to do it, without this handicap.

Thank you.
Tommy

and Tom said...

Well, the RIGHT way is to use the queueing software built into the database
(Advanced Queues -- AQ). It solves this problem completely

</code> http://docs.oracle.com/cd/A81042_01/DOC/appdev.816/a76938/toc.htm <code>

Short of that, your approach will work:

create procedure get_record( p_rec out items%rowtype )
is
pragma exception_init( resource_busy, -54 );
begin
for x in ( select rowid rid from items where status = -1 )
loop
begin
select * into p_rec from items where rowid = x.rid for update nowait;
exit;
exception
when resource_busy then
null;
end;
end loop;
end;

Without NOWAIT, I don't see a way to do this.

Rating

(18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

suggestion ...

A reader, October 28, 2001 - 8:23 pm UTC

select * from items where status = -1
and rownum=1
for update;

can this not just lock one row at a time?

October 28, 2001 - 9:01 pm UTC

yes it will lock just one row at a time -- but if 5 sessions execute it at the same time, they'll ALL try to lock the same row and 4 out of 5 will block. You'd be right back where you started.

The procedure below is the second best way to do it (AQ being the first)

Looks good to me...

Padders, October 29, 2001 - 10:13 am UTC

...but in your example it appears you have omitted to declare the resource_busy exception.

October 29, 2001 - 10:18 am UTC

You are right! good eye. we need to declare an exception in there.

x

Tom Best, October 29, 2001 - 10:40 am UTC

I'll look into AQ. And thanks for confirming my non-AQ solution.

AQ solution

DK, January 25, 2002 - 10:07 am UTC

Dear Tom,
How exactly to code AQ solution. It is surprisingly not in your book too.
Regards,
DK

AQ with example please

A reader, May 13, 2002 - 7:52 pm UTC

Can u please give an Example- solution for the above problem using AQ. As i see it is one of the most underutilized feature of oracle as it is difficult to find good sample programs with use cases.Apart from me I think even the Oracle Corp doc writers dont know it so well. I may be wrong.
Thanx

May 13, 2002 - 8:25 pm UTC

Look in the application developers set of docs. There is an ENTIRE MANUAL on nothing but AQ!!!

search for dbms_aq -- there are some examples here.

Example please ...

Pasko, May 14, 2002 - 6:35 am UTC

Hi Tom

Thanks for the above solution of using a LOOP and NOWAIT...

I searched your site for dbms_aq but didn't find any examples related to this

Oracle Docs require a whole lot of time and that's why we drop in at your site...

An example would be greatly appreciated
I'm a Great fan of Learn-By-Example ...

Regards

Pascal

May 16, 2002 - 9:23 am UTC

I'm a great fan of reading the documentation. You know, if I show you a snippet of code -- you'll learn 1/10th of 1% of what you should know -- and you'll try to apply that snippet of code to every problem.

Be smart -- read the docs. Knowledge takes time, experience. How do you think I do what I do (hint hint hint: I read the docs)

Since you are so hesitant to do it I'll make it easier for you:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76938/adq08qap.htm#83406


Chapter 8 A Sample Application Using AQ

For the entire book:

http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76938/toc.htm <code>

this hole concept of "i don't want to take the time to learn" scares me. You are missing 99% of the picture.

I'm a fan of learn by example as well -- after reading the documentation to get the foundation (and then lo and behold -- I'll discover there are actually examples in the documentation!)

We Want an Example ;We Want an Example ;We Want an Example ;

A reader, May 14, 2002 - 7:02 pm UTC

Yes please give us an Example of AQ for the above solution.
Why people shy away from giving examples.Are your concepts of AQ still not clear.
(Well I am just provoking u to reply with an example,just kidding, Atleast this will give us an Example from the GREAT TOM at any cost.).
Come on Every body repeat after me.
We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;We Want an Example ;

May 16, 2002 - 12:46 pm UTC

no.

I could just cut and paste this in:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76938/toc.htm <code>

but i won't.

RE: We want an example

Mark A. Williams, May 16, 2002 - 10:19 am UTC

<soapbox>

You want an example?

Here's an example:

Tom answers a question.
He provides links to the documentation.
The documentation contains examples (plus alot more).
Someone who can't type "you" screams "we want an example".

Now, the question is, of what is this an example?

Do you want to learn to fish, or do you want Tom to just send you a fish everyday?

- Mark

</soapbox>

An Example related to the Question above...

A reader, May 17, 2002 - 3:44 am UTC

Hi Tom

I read the whole Documentation and i could not find an example related to a direct approach where i could solve the question above, without changing my Application Design Totally ...i mean switch to Objects and all..

Plus, it seems even with ADQ..we still have to put some locking mechanisms if we want our Queue to be accessed by only one Subscriber at a time ...
(refer to the BooksOnline Example)

I don't want to totally change my Application Design...i was looking for an example where i could use Oracle's ADQ
to solve this Locking issue without using oracle Objects/ or Pro C/C++ code..

May 17, 2002 - 8:18 am UTC

since you are just "a reader" -- i have no idea to what question you might be refering.

You do not have to switch to objects, the message payload can be a LONG RAW (whatever you want).

If you want the queue to be accessed by one subscriber at a time -- you by definition do not need, or even want a queue, you apparently want a plain old database table.

Time and Again

A reader, May 17, 2002 - 6:24 pm UTC

The Question in Question is obviously the the above Question "Multiple Processes processing rows" and the Non AQ solution given.All the readers are asking again and again is that "HOW to implement the same situation Using AQ". Simple.
The Evasive solution so far has been the link to the Doc.
Thanx Tom.

May 18, 2002 - 9:57 am UTC

Well, no it is NOT OBVIOUSLY that. "a reader" (sort of evasive there, hiding behind "a reader") has been begging for "a custom example just for them so they don't have to waste time learning themselves".

You would

a) create a queue.
b) use dbms_aq.enqueue to queue a message.
c) have consumer(s) running that use dbms_aq.dequeue to dequeue and process messages.

Hey -- there is your example.

(and you know what -- the booksonline example I keep trying to get you to read not evasively, as if I'm doing something wrong here is exactly the implementation of the example you keep begging for.

It is the example! It is the example! Lets see what that example does:

a) it shows how to create the user with the ability to create and manage the queue. Thats a good start

b) it shows how to create a queue in that user....

c) it then shows how to use this queue with plsql and java/jdbc and C to enqueue/dequeue a message.

Hmmm, looks like an example to me doesn't it.

I hate wasting time -- I have lots to do. When a perfectly good, comprehensive example exists -- I go for it, I use it. Re-inventing wheels -- I give seminars on how NOT TO reinvent a wheel.

Tom you are missed the guys point

E.Elliott, June 30, 2003 - 5:38 pm UTC

The guy wanted an example that showed a queue being processed by N consumers. With each message on the queue being processed by only one consumer. I have looked for such examples and have not found them. The idea is to simulate a javaSpace where you can scale the number of consumers.

June 30, 2003 - 7:40 pm UTC

well, sigh, if you would read the links - but ok, I was finally beat into total submission.

open wide
insert spoon

read:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:540822468136#7436461525790 <code>

it is literally cut and pasted from the above referenced material - but you might have to "port" it to windows as it uses a shell script to mimick many dequeuers...

really -- the docs don't totally stink (and they even predate java!)

Multiple customers on AQ

Elliott, July 02, 2003 - 2:14 pm UTC

Ah, The queue is not quite a QUEUE!
The AQ table select does select for update SKIP LOCKED
So if we have multiple customers on a queue, each will get the next not locked record. When the record is obtained it is locked. Only one session can have exclusive update lock on a record.
So while customer 1 gets and locks the FIRST item on the queue. Customer 2 will skip the locked record and get FIRST not locked on the queue.

select /*+ FIRST_ROWS */ tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay, tab.expiration, tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_no, tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sender_address, tab.sender_protocol, tab.dequeue_msgid
from GGGGGG.OBJMSGS80_QTAB tab
where q_name = :1 and state = :2
order by q_name, state, enq_time, step_no, chain_no, local_order_no
FOR UPDATE SKIP LOCKED

(The example in the Docs shows multiple clients on publish subscribe queue, each item on the queue is processed by all registered clients)

Thanks

single consumer model

reader, December 26, 2003 - 5:51 pm UTC

Tom,

related, to this thread, I have a very similar question/requirement.

A user orders ten items. (inserts ten rows into a table). The insert kicks of a job that selects for update all unfilled orders. Each order takes 5 minutes to complete processing. Processing doesn't change any attribute values of those rows of data that were inserted, it just loads some related binary data into another table. I am fine with these orders being processed serially by a single consumer (the database job). This is actually preferred since the processing puts a strain on the hierarhcial storage management system the data is requested from. (it can only handle a few requests at a time). However, I would like to make available the binary data that is loaded as a result of the processing of each of these rows prior to all ten finishing.

Will an autonomous transaction in the cursor for loop that iterates through the open orders satisfy this? I think it will allow me to insert/commit the binary data in the associated table making it available for dissemination, without the problem of not being able to commit in a for update cursor loop.

or should I be thinking differently. I know AQ with a single consumer would ensure that each message only is dequeued once, and that each dequeued message could result in a single transaction that commited its data as soon as it was finished. So maybe I should bite the bullet and start reading the dbms_aq guide.

December 26, 2003 - 7:13 pm UTC

well, does it make transactional SENSE for some of the data to start appearing before the transaction is completed?

I mean -- what happens if 1/2 way through, the system fails? what then? are you able to "recover from that" or will you double order (sounds like a double order to me).

I like AQ for it's "loose coupling" here -- process each transaction as a message, commit at the end. Everyone wins.

non-AQ solution

Dan Clamage, December 29, 2003 - 10:05 am UTC

A non-AQ solution I've used successfully is to:
1. Request a lock via DBMS_LOCK.request (the package executable section, run once at instantiation, allocates a unique handle with DBMS_LOCK.allocate_unique (saved in a private-global variable) for a constant string, so that every session will use the same lock). The default params give you an Exclusive lock.
2. After obtaining the lock, I SELECT just one row (limiting the rows returned with ROWNUM=1) meeting the desired criteria AND not found in a "lock" table (by primary key -- you need some way to uniquely identify the row). I don't care which one I get, so long as I get one.
3. Insert the row's primary key in the "lock table".
4. Commit and release the lock with DBMS_LOCK.release.

I've just created a "Mutex", a program area that is mutually exclusive to sessions. Only one session can enter that section of code at a time. It doesn't do a whole lot, so the time spent in that code section is small (doesn't hold up everyone too much). If lots of sessions try to obtain the lock, the internal queuing mechanism inherent in these locks gives me the "FIFO" (First In, First Out) behavior I want.

I've tested this with 16 sessions beating on it ("locking" a row to process, processing it quickly, then removing it from the "lock" table), and it worked well enough for our multiuser environment (actually an Intranet web GUI), where requests would never come in in such heavy volume (but that's the way to test it).

Don't forget to put in logic to handle timeouts (I think in your case you'd merely want to retry obtaining the lock, although in practice you will probably find it not necessary, even with a timeout of 2 secs), no work to perform (no rows meeting your criteria), releasing the lock in case of a fatal error, etc.

And be sure to read the Supplied PL/SQL Packages doc!
</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lock.htm#998100 <code>

Dan

Lou, May 16, 2005 - 12:04 pm UTC

Do you have an example of your implemtation? I will
be very interested in your approach since I am in
the same situation.

dbms_lock.allocate_unique() does a commit

Doug, August 18, 2005 - 2:02 pm UTC

In an effort to save someone else a day of debugging, I thought I'd point out that dbms_lock.allocate_unique() quietly does a commit.

This isn't documented as far as I could find, and it was only by turning on sql_trace that we were able to catch it in the act.

Of course, it makes perfect sense that it must. Now. :-)

August 18, 2005 - 4:47 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lock2.htm#1001065


specifically:

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lock2.htm#1001065 <code>

<quote>

A lock name is associated with the returned lock ID for at least expiration_secs (defaults to 10 days) past the last call to ALLOCATE_UNIQUE with the given lock name. After this time, the row in the dbms_lock_allocated table for this lock name may be deleted in order to recover space. ALLOCATE_UNIQUE performs a commit.
</quote>

dbms_lock.allocate_unique() does a commit

Doug, August 18, 2005 - 2:13 pm UTC

Oh, for goodness sake there it is, plain as day.

</code> http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_lock.htm#1002534 <code>

"A lock name is associated with the returned lock ID for at least expiration_secs (defaults to 10 days) past the last call to ALLOCATE_UNIQUE with the given lock name. After this time, the row in the dbms_lock_allocated table for this lock name may be deleted in order to recover space. ==> ALLOCATE_UNIQUE performs a commit. <=="

August 18, 2005 - 4:47 pm UTC

ahh, you saw it :) (i read these in order so do not always see a followup to a followup)

Bug with ALLOCATE_UNIQUE

Martin Rose, October 15, 2010 - 3:31 am UTC

I reckon I've found a bug with DBMS_LOCK.ALLOCATE_UNIQUE.

If you run this below, it generates 'ORA-01426: numeric overflow' coming from within the SYS.DBMS_LOCK procedure. I can confirm this is happening under both 10g & 11g rel 2.

Since the package is wrapped, I cannot peer into it to see what might be going wrong.

SET SERVEROUTPUT ON

DECLARE
  LockHandle VARCHAR2(128);
  --
  Lock_Status  INTEGER;
  --
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE ('LOCK_NAME', LockHandle);
  --
  DBMS_OUTPUT.PUT_LINE ('Lock Handle IS '|| LockHandle);
  --
  Lock_Status := DBMS_LOCK.REQUEST (ID                => LockHandle,
                                    RELEASE_ON_COMMIT => TRUE);
END;
/

Lock Handle IS 1073741955107374195539

You can see from the DBMS_OUTPUT that the handle returned is too long for DBMS_LOCK.REQUEST.

In fact, it looks like two handles have been stitched together.

SUBSTRing it works.

  Lock_Status := DBMS_LOCK.REQUEST (ID                => SUBSTR(LockHandle, 1, 10),
                                    RELEASE_ON_COMMIT => TRUE);


    My question is:  Is this a safe work-around?

October 15, 2010 - 9:01 am UTC

ops$tkyte%ORA11GR2> SET SERVEROUTPUT ON
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> DECLARE
  2    LockHandle VARCHAR2(128);
  3    --
  4    Lock_Status  INTEGER;
  5    --
  6  BEGIN
  7    DBMS_LOCK.ALLOCATE_UNIQUE ('LOCK_NAME', LockHandle);
  8    --
  9    DBMS_OUTPUT.PUT_LINE ('Lock Handle IS '|| LockHandle);
 10    --
 11    Lock_Status := DBMS_LOCK.REQUEST (#BlockHandle #b               => LockHandle,
 12                                      RELEASE_ON_COMMIT => TRUE);
 13  END;
 14  /
Lock Handle IS 10737420561073742056140

PL/SQL procedure successfully completed.

the api takes an ID *or* a lockhandle.

dbms_lock.allocate_unique returns a lockhandle, not an id. It is documented so:

http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_lock.htm#i1002533

A handle is returned instead of the actual lock ID to reduce the chance that a programming error accidentally creates an incorrect, but valid, lock ID. This provides better isolation between different applications that are using this package.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

suggestion ...

Looks good to me...

x

AQ solution

AQ with example please

Example please ...

We Want an Example ;We Want an Example ;We Want an Example ;

RE: We want an example

An Example related to the Question above...

Time and Again

Tom you are missed the guys point

Multiple customers on AQ

single consumer model

non-AQ solution

Dan

dbms_lock.allocate_unique() does a commit

dbms_lock.allocate_unique() does a commit

Bug with ALLOCATE_UNIQUE

More to Explore

PL/SQL demos

PL/SQL docs