Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL for NOT_LOCKED?

Re: SQL for NOT_LOCKED?

From: <mikedba_at_my-deja.com>
Date: Mon, 29 Jan 2001 03:27:26 GMT
Message-ID: <952nup$sag$1@nnrp1.deja.com>

>>>> Your ability to limit the cursor really depends
>>>> on how the records will be processed, and if when
>>>> they are updated that they drop out of the driving
>>>> cursor result set so that if the cursor is re-opened
>>>> the updated records will not be picked up again.
 
>>> Yes, this sounds like what I had in mind.
>>> I figured I'd just requery each time a user needs >>> another record to edit  
>>> If I'm re-querying for each "next" request, it
>>> sounds like I don't need to worry about
>>> "snapshot too old".
 

>> Mike, I am not sure you should re-query every time just
>> to advance to the next record. Opening the driving
>> cursor is probably the most expensive part of
 

>> No one else will be able to access the rows
>> until they are committed, but the nowait will prevent
>> the other users from being lockwaited so they can
>> continue on to unprocessed rows.
 

> There may be benefit from using 'select for update skip locked' for > multiple users trying to get 'locked' access to a common set of rows

Connor:

Thanks a lot. Maybe the point was already being made, and I was missing it, but the part about "skip locked" sure seems to make it clearer to me. Now I've just got to figure out how to make this work through the Direct Oracle Access components I am using, or use the DOA components to access a stored procedure/package that implements your suggestion (and Mark's suggestion). Not sure if I need the "nowait" if I'm using "skip locked", but I'll check the docs.... maybe they're mutually exclusive, or one is redundant in this scenario.

I've got the 8.1 client installed, so now I should be able to try the various DOA components.

mIKE

Sent via Deja.com
http://www.deja.com/ Received on Sun Jan 28 2001 - 21:27:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US