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


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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
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



--
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


Other related posts: