Re: Bad theory

Alexander Chupin wrote:
>
> 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

Bummer eh? It locks the whole lot - this is known and expected behaviour which (as you've discovered) the fk index will fix.

Cheers
C

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"