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

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

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 07 2007 - 11:42:48 CST