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@oktagramm.de
Hauptstr. 26 | la@heptagramm.de
D-65346 Eltville | lothar.armbruester@t-online.de

On Tue, 3 Aug 2004 18:57:26 +0200, Lothar Armbruester
<lothar.armbruester@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

Pls. see below:

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

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@oktagramm.de
> Hauptstr. 26 | la@heptagramm.de
> D-65346 Eltville | lothar.armbruester@t-online.de

Sybrand Bakker <gooiditweg@sybrandb.verwijderdit.demon.nl> wrote in message news:<drlvg052k6n8kuhh4pfjslsmkasroj1ak4@4ax.com>. ..
> On Tue, 3 Aug 2004 18:57:26 +0200, Lothar Armbruester
> <lothar.armbruester@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