RE: Locking question when using Select clause with For Update and Skip locked
- From: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
- To: <Harvinder.Singh@xxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Mar 2007 09:24:45 +0200
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
