Re: Get the first non-locked record.

On Tue, 20 Nov 2001 20:42:02 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>There is an undocumented SKIP LOCKED option
>used with "select for update" in the Advanced
>Queue packages. You could try that:
>
>select rowid , id
>from tableX
>for update
>skip locked
>order by processing_order desc;
>
>Then rollback, then select the first row
>returned for update (nowait, in case
>something else has slipped in between
>the select and the rollback)
>
>
>Unfortunately if you try to use
> where rownum = 1
>the rownum take precedence
>over the SKIP, so you get the
>first row before Oracle checks
>to see if it is locked.
>
>You may find a better way of doing
>this, as it would be rather expensive
>on rollback if the table was allowed to
>get fairly large.

Thank you. I did not know of SKIP LOCKED. I'll have to keep that in mind.

Brian Received on Tue Nov 20 2001 - 15:49:15 CST