Re: Inserting null into child table locks parent table

Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:<drlvg052k6n8kuhh4pfjslsmkasroj1ak4_at_4ax.com>...
> On Tue, 3 Aug 2004 18:57:26 +0200, Lothar Armbruester
> <lothar.armbruester_at_t-online.de> wrote:
>
> >Hello out there,
> >
> >last week, we upgraded our Oracle installation from 9.0.1.4 for 9.2.0.5
> >on W2kSP4.
> >Now I discovered some different locking behaviour of the new version.
> >When I do the following:
> >
> >create table test1 (col1 number(2) primary key, col2 varchar2(10));
> >
> >create table test2 (tcol1 number(2) primary key,
> > col1 number(2) references test1(col1));
> >
> >insert into table test2 values (1,null);
> >
> >I'm having a TM-Lock (ROW SHARE) on test1 which did not occur with
> >Oracle 9.0.1. Indexing col1 on test2 does not help.
> >Normally I would not care but in my special case I'm calling a stored
> >procedure in an autonomous transaction which makes a lock table test1 in
> >exclusive mode so that I'm getting a deadlock.
> >
> >Is there a way to prevent Oracle from locking the parent table when
> >inserting null into the child? (Other than dropping the constraint :-)
> >
> >Many thanks in advance,
> >Lothar
>
> Try making the constraint deferred

Hello Sybrand,
this did not help. I will follow the advice VC gave in his post. As a quick workaround I dropped the contraint. I will rethink my application design to circumvent the deadlock. dbms_lock seems to be a good candidate.

Regards,
Lothar Received on Wed Aug 04 2004 - 01:49:15 CDT