Re: Inserting null into child table locks parent table

Pls. see below:

"Lothar Armbruester" <lothar.armbruester_at_t-online.de> wrote in message news:PM0003E0C2CFE53302_at_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.

There is a note (223303.1) on metalink regarding locking behaviour during parent/child DMLs. What can be surmised from the note is that locking behaviour was changed to fix a parallel DML bug and apparently the change occured between 9.0.1 and 9.2.

When you insert a record into the child table under 9.2, *two* TM locks (row-share against the p-table and row-exclusive on the c-table) will be taken. There is nothing you can do about it short of changing the app logic or dropping the constraint.

VC

> 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 - 13:45:58 CDT