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

Home -> Community -> Usenet -> c.d.o.server -> Re: Locking question when using Select clause with For Update and Skip locked

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

From: <xhoster_at_gmail.com>
Date: 08 Mar 2007 18:52:33 GMT
Message-ID: <20070308135920.124$6b@newsreader.com>


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
Received on Thu Mar 08 2007 - 12:52:33 CST

Original text of this message

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