Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Inserting null into child table locks parent table
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.deReceived on Tue Aug 03 2004 - 11:57:26 CDT
![]() |
![]() |