On Mar 7, 2:56 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> On Mar 7, 12:42 pm, harvinde..._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), so
> > for debugging purposes i removed the "skip locked syntax" and now i
> > can see the following blocking information in database between 2
> > sessions:
>
> > DBA_LOCKS INFO
> > ----------------------------
>
> > SID Lock Type Mode Held Blocking?
> > 737 DML Row-X (SX) Not Blocking
> > 943 DML Row-X (SX) Not Blocking
> > 737 Transaction Exclusive Blocking
> > 943 Transaction None Not Blocking
>
> > select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
> > from v$session where sid in (737,943)
>
> > ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
> > 79436 6 5205 19
> > 79436 6 5205 20
>
> > It looks like both the session got the ROW-X lock but one session is
> > waiting on getting the Transaction lock.
> > (It looks normal since without the where clause Oracle have read the
> > full index scan on emp and then just return 1 row to satisfy rownum
> > whereas in SQL Server optimizer only read the 1 row from the index.)
>
> > Why we see the blocking on Transaction lock in Oracle and not on index
> > blocks and is there any workaround to acheive the same functionality
> > in Oracle?
>
> > Thanks
> > --Harvinder
>
> Oracle and SQL Server have different read consistency schemes and
> different locking schemes. You generally cannot just port an
> application from one to the other without giving careful thought to
> the differences and how this will affect the application logic.
>
> In Oracle updaters do not block normal readers. Select for update
> exists to make a session wait for the updated data before proceeding.
>
> If what you want to do is run two (or more) update engines against the
> same data set you should use a non-select for update cursor to select
> all the rows you want to process then when you loop through the result
> set you re-select the rows one at a time using select for update with
> the skip option. The where clause should respecify the same where
> clause criteria used in the driving cursor to make sure the row still
> fits the processing requirements. This way both processes will go
> through the same data set while skipping over those rows being
> processed by the other engine and as long as the update changes the
> rows such that the row no longer meets the where clause conditions
> this logic will also skip rows alreadys processed by the other engine.
>
> If this will not work for you then you need to explain your process
> logic in more detail some someone can offer suggestions.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
PS I intended to add a comment about if you believe that you need to
run concurrent identical update tasks that you verify that you really
need to do this in Oracle. The Oracle locking and read consistency
model supports higher concurrency than SQL Server's model so you might
not need to do this.
Received on Wed Mar 07 2007 - 13:59:45 CST
