Re: Locking question when using Select clause with For Update and Skip locked

harvinder76_at_gmail.com wrote:
> Hi,
>
> We have an query in SQL Server like following that locks 1 row of a
> table for update and skipped the rows locked by other sessions on same
> table:
> select top 1 empno from emp
> with(readpast, updlock)
> (we also have order by clause but will remove it here for simplicity)
> --order by empno
>
> This is working fine in SQL Server and multiple session can get the
> different rows and do processing on them. Now on Oracle system it is
> ported as:
> select empno from emp
> where rownum < 2
> for update skip locked;
>
> But in Oracle the first session only return 1 row but locks all the
> rows and other session gets no rows returned(skip locked clause),

I don't think that that is what is happening. Rather, the first session locks just one row. The second session encounters that row, increments the internal row-count, and then skips the row. All successive rows fail the rownum<2 check and therefore nothing gets returned. I have no idea why Oracle increments counts on rows that are skipped. Doing it that way causes various problems and as far as I can tell doesn't solve any.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB