Re: Locking question when using Select clause with For Update and Skip locked
- From: "Mark D Powell" <Mark.Powell@xxxxxxx>
- Date: 7 Mar 2007 11:59:45 -0800
On Mar 7, 2:56 pm, "Mark D Powell" <Mark.Pow...@xxxxxxx> wrote:
On Mar 7, 12:42 pm, harvinde...@xxxxxxxxx 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.
-- Mark D Powell --
.
- Follow-Ups:
- References:
- Locking question when using Select clause with For Update and Skip locked
- From: harvinder76
- Re: Locking question when using Select clause with For Update and Skip locked
- From: Mark D Powell
- Locking question when using Select clause with For Update and Skip locked
- Prev by Date: Re: Locking question when using Select clause with For Update and Skip locked
- Next by Date: CBO & different execution plans
- Previous by thread: Re: Locking question when using Select clause with For Update and Skip locked
- Next by thread: Re: Locking question when using Select clause with For Update and Skip locked
- Index(es):
Relevant Pages
|
Loading
