RE: nowait cursors in triggers
- From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
- To: <mcdonald.connor@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Jun 2005 23:36:33 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Connor McDonald
Sent: Wednesday, June 08, 2005 10:22 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx
web: http://www.oracledba.co.uk
"Semper in excremento, sole profundum qui variat"
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
