Master-detail locking problem

Hi All,

I have found a strange effect. In order to reproduce it run the following script in SQL*plus window :

• begin of the script ----- drop sequence m_seq; drop table d; drop table m; create sequence m_seq; create table m( a number, o number ); alter table m add constraint m_pk primary key ( a ); CREATE OR REPLACE TRIGGER m_BR_TRG before INSERT or UPDATE ON m for each row declare begin if :new.a is null then SELECT m_seq.NEXTVAL into :new.a FROM dual; end if; end; /

create table d ( a number, d number );
alter table d add constraint f_m_fk foreign key ( a ) references m(a); insert into m values( 1,1 );
commit;
insert into d values (1,1);
------- end of the script -----

after last insert statemet has been executed open new SQL*plus window and run the statement

update m set o=1 where a =1;

this statement waits till commit or
rollback is executed in the first sql*plus window.

In my opinion, this isn't correct behaviour. I can understand that oracle must lock
primary key field in the parent table,
but the field "o" should not be locked.

I've checked this behaviour for
8.1.6.0, 8.1.6.3 for sparc Solaris
8.0.5 for Windows NT

I also found out that the following statment in the trigger of table m is responsible for the locking:

    SELECT m_seq.NEXTVAL into :new.a FROM dual;

locking of field "o" doesn't occur if this statment is removed from the trigger body .

Thanx in advance
WBR, Alexander Chupin Received on Mon Mar 12 2001 - 21:00:55 CET