Re: Bad theory

HI All and Sybrand Bakker,

Many thanx for you attention, but it seems you not quite careful read my first letter.:(

> Some people just shout 'bug' too often.

I completely agree with you :-)

> When you start the second session the first session has an exclusive lock
 on
> table d because of your uncommitted insert.

Hm, what do you mean when you say "exclusive lock on table d". I think you wanted to say ROW EXCLUSIVE mode!

ok, I make run following statement

insert into d values( 1, 1 );

and in the dba_lock view I can see for this session

27 Transaction   Exclusive       None   65578   29406  3  Not Blocking
27 DML               Row-X (SX)  None   25756            0  3  Not Blocking

where is here exclusive lock for table?

> Hence when it comes to foreign key checking, and as you didn't define an
> index on your foreign key column, your second session will too try to
 obtain
> a table level exclusive lock on your table d. Evidently, it won't get
 that,
> and it will wait forever, as this is not a deadlock.

My second session was not going make any changes of field "a" which is primary key for master table. Also at the end of my first letter I mentioned that this effect doesn't occur IF SIMPLE REMOVE TRIGGER FOR MASTER TABLE. Could you explain the fact?

> You need to create an index on your foreing key column
> and/or issue appriopate update commands

Ok. But why I must not create this index if trigger for master table doesn't exists or disabled ;-)?

> (as you probably are not aware, an update without select for update or
> without a previous lock table in row share mode also results in a table
> level exclusive lock)

could you explain this thought more exactly with example (I mean the select * from dba_locks).

WBR, Alexander Chupin Received on Tue Mar 13 2001 - 12:03:29 CST