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

From: Yasin Baskan <yasin.baskan_at_yapikredi.com.tr>
Date: Thu, 8 Mar 2007 09:24:45 +0200
Message-ID: <083667B535F3464CA0DD0D1DAFA4E3760CB9FECE@camexc1.kfs.local>

You said " It looks like both the session got the ROW-X lock but one session is
waiting on getting the Transaction lock.". Can you provide a full test case for that? The second session waits on the row lock in the following test case.

SQL> create table t as select * from all_objects;

Table created.

SQL> create index tind on t(object_name);

Index created.

SQL> select object_name from t where rownum<2 for update;

OBJECT_NAME

DUAL From another session:

SQL> select object_name from t where rownum<2 for update;

This second session waits until the first session ends the transaction. And v$lock shows that the second session is waiting to get a row lock.

SQL> select sid,type,lmode,request,block from v$lock where sid in (145,344);

SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------

       145 TX          6          0          1
       145 TM          3          0          0
       344 TM          3          0          0
       344 TX          0          6          0

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
Sent: Wednesday, March 07, 2007 7:43 PM
To: oracle-l
Subject: 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

Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi zararlardan Bankamiz sorumlu tutulamaz.

This message and the files attached to it are under the privacy liability in accordance with the Banking Law and confidential to the use of the individual or entity to whom they are addressed. This message cannot be copied, disclosed or sold monetary consideration for any purpose. If you are not the intended recipient of this message, you should not copy, distribute, disclose or forward the information that exists in the content and in the attachments of this message; please notify the sender immediately and delete all copies of this message. Our Bank does not warrant the accuracy, integrity and currency of the information transmitted with this message. This message has been detected for all known computer viruses thence our Bank is not liable for the occurrence of any system corruption caused by this message
--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 08 2007 - 01:24:45 CST