RE: nowait cursors in triggers

From: Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com>
Date: Wed, 8 Jun 2005 23:36:33 -0700
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B94445F7@wafedixm10.corp.weyer.pri>

Hey,

I'm no trigger expert and may be way off, but...

Just trying to work out the event flow here...

Session 1 deletes where x =3D 1 (this sets the lock on this row), then = the
trigger fires and locks the remaining rows

Session 2 tries to delete where x =3D 2, but Sess 1's trigger would have acquired a lock on this row, thus session 2 waits.=20

To me the trigger for session 2 would not have fired yet since it's being blocked on the delete process. The trigger is an AFTER delete trigger but the delete never gets a chance to complete due to the lock acquired by the first trigger.

Sess 2's select ... for update nowait statement never gets a chance to execute...By the way is there a way to check if a trigger has fired?

Feel free to correct me if I'm way off. =20

Thanks!=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Connor McDonald Sent: Wednesday, June 08, 2005 10:22 PM
To: oracle-l_at_freelists.org
Subject: nowait cursors in triggers

Hi all,

Does anyone know of a restriction (documented or otherwise) that prohibits triggers from using 'for update nowait' cursors ?

Consider the following example:

---

SQL> create table T ( x number, y number );

Table created.

SQL> insert into T values (1,1);

1 row created.

SQL> insert into T values (2,1);

1 row created.

SQL> create or replace
  2 trigger TRG after delete on T
  3 declare
  4 cursor C is select * from t

  5     where y =3D3D 1
  6     for update NOWAIT;

  7 begin
  8 open c;
  9 end;
 10 /

Trigger created.

Session 1 then does the following:

SQL> set sqlprompt 'SES1> '
SES1> delete from t where x =3D3D 1;

1 row deleted.

Session 2 then tries to delete a different row, but the after-statement trigger will then try to get a nowait lock on both rows:

SQL> set sqlprompt 'SES2> '
SES2> delete from t where x =3D3D 2;

(waiting....waiting....)

Is this correct behaviour ? Why does ses2 not get a "ORA-00054: resource busy and acquire with NOWAIT specified"

Cheers
Connor

--=3D20
Connor McDonald
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D=
3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
=3D3D=3D
=3D3D=3D3D

email: connor_mcdonald_at_yahoo.com
web: http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 09 2005 - 02:41:38 CDT