Locking question when using Select clause with For Update and Skip locked
harvi...@gmail.com
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
Mark D Powell
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 --
Mark D Powell
>
> - 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.
harvi...@gmail.com
trying to support same type of code on different RDBMS. I am preety
sure there must be more locking issues in the application besides this
and trying to isolate and fix them. This was bit tricky with the way
how the "rownum" and "top" are implemented and for developers it is
very difficult to see the difference unless they test the concurrency
of the code while developing. This particular issue is not that
serious since this block takes few ms to run and commit and there is
not much blocking overhead since rest of the processing is more time
consuming then getting the row to work from this table. Since Advance
Queuing is introduced long time back and we are also doing same type
of processing there must be a way in Oracle to implement FIFO between
different sessions (I have not yet looked into the AQ code so just
assuming that oracle must be be able to get the next message and
passed to waiting session so there must be some kind of lock single
row and get the rownum with order by clause implemented).
I will do research also but It will be great if you can send some
linke about how oracle process the rownum and why in this scenario it
is showing as blocking on Transaction lock and if blocking session is
locking all the rows howcome the waiting session was able to get the
DML lock.
Thanks
--Harvinder
hpuxrac
Tom Kyte has this whole subject covered in detail in several of his
books.
Before you go any further I recommend that you purchase and read his
latest one "Expert Oracle Database Architecture". Read chapters 1
thru 9. Rinse and repeat.
It's better to get the whole complete picture and understand it in
detail than trying to pick and fix 1 piece of SQL at a time.
xho...@gmail.com
> 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