Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Inserting null into child table locks parent table

Inserting null into child table locks parent table

From: Lothar Armbruester <lothar.armbruester_at_t-online.de>
Date: Tue, 3 Aug 2004 18:57:26 +0200
Message-ID: <PM0003E0C2CFE53302@hades.none.local>


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

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de
Received on Tue Aug 03 2004 - 11:57:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US