Re: Inserting null into child table locks parent table

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

--
Sybrand Bakker, Senior Oracle DBA