Re: skipping locked rows

The problem is that the rownum stop key is evaluated before the 'skip locked'. Hence, the query always selects the first row and then tries to lock it. Take a look at this thread from comp.databases.oracle.misc back in november...

http://groups.google.com/groups?hl=en&q=c_main+c_lock

Basically, he justs has an outside cursor that selects all the rows. Then his inside cursor that attempts to lock the row. If it succeeds he processes it otherwise he moves to the next row.

Richard

Kent Brye wrote:
>
> I've searched for an answer and unable to come up with one.
> We have a need to have multiple people update rows in a single table. It is
> desired that the application get the first unlocked row that needs updating,
> make changes with user input, update and commit. Then get next row. Same
> app will be used by at least two people simultaneously. Querying the db
> after each update will not be a performance problem for us in this
> application.
>
> In SQL Plus I've tried
> select col1, col2, col3
> from table1
> where rownum = 1
> for update nowait skip locked
>
> The first query brings back the result set,
> the second brings back no rows.
>
> We're using Oracle 8i, Delphi, Direct Oracle Access Components
>
> Any suggestions?
>
> Thanks in advance,
> Kent Brye
> kbrye_at_ncbex.org
Received on Tue Mar 05 2002 - 12:26:28 CST