deadlock in trigger.

From: Hugues Landry <hugues_landry_at_videotron.ca>
Date: Sun, 20 Jan 2002 19:05:14 -0500
Message-ID: <3XI28.863$%p2.84232@weber.videotron.net>

Hi i got this deadlock problem while trying to map an object model into oracle table. Oracle
tell to me that two request are waiting for the same resources and he is correct Here's what i
do.

---

create table vectorroot(

   vid number(20,0) primary key,
   vName varchar2(50)
)
/

create table vector2(

   vid number(20,0) primary key references verctorroot on delete cascade,    v0 float,
   v1 float,
   v2 float
)
/

create table vectormap(

   vid number(20,0) primary key references vectorroot on delete cascade,    vec1 number(20,0) references vector2,    vec2 number(20,0) references vector2
)
/

create procedure deleteVector2(

   in_vid number(20,0)
) is

   pragma autonomous_transaction;
begin

   delete from vector2 where vid = in_vid; end;
/

create trigger onDeleteVectorMap

   on delete on vectormap for each row
   begin

      call deleteVector2( :old.vec1 );
      call deleteVector2( :old.vec2 );

   end;
/

---

At first, I was deleting directly from the trigger. As you can imagine, this didn't work because the table was mutating. I tought the autonomous transaction would take care of this since it's done after. But finally it get
me the deadlock ( This is a valid sql-plus script by the way ).

I really need to have the vectorroot table ( this is a mapping for the c++ container patern ). Does anyone know a workaround that would enable me to delete from the vector2 table within a trigger without blowing everything
up :-)

Thanks in Advance.

Hugues Landry. ( A poor c++ programmer that try to understanding the relational way of life ). Received on Sun Jan 20 2002 - 18:05:14 CST