Re: Deadlock Question

In article <1117741042.088472.124300_at_f14g2000cwb.googlegroups.com>, wagen123_at_yahoo.com says...
>
>Oracle10g 10.1.0.3 (64 bit)
>Solaris 2.8 (64 bit)
>
>Billy --> You are correct. As per Metalink Note:223303.1 in 9.2.0 and
>later versions, the foreign keys don't have to be indexed.

oh, yes -- they do.

In 8i the child table lock was for the duration of the transaction. So, upon UPDATE of the parent primary key or DELETE of a row in the parent table -- that transaction would get a full table lock on every child table that had unindexed foreign keys and keep it until it committed that transaction.

In 9i and above the child table lock was for the duration of the modification. So, upon UPDATE of the parent primary key or DELETE of a row in the parent table -- that transaction would get a full table lock on every child table that had unindexed foreign keys (causing blocking at that point -- if there were a transaction on the child table -- the parent modification would block and start blocking others). When the parent modification finished, the lock was released (before the commit)

HOWEVER, if you

1. update the parent primary key (bad bad)
2. delete from the parent

and have an unindexed foreign key, you have to ask yourself "why" -- regardless of the locking. You really want to full scan the child table for every modified row in the parent???

SESSION 1:
ops$tkyte_at_ORA10G> create table p ( x int primary key );  

Table created.  

ops$tkyte_at_ORA10G> create table c ( x references p );  

Table created.  

ops$tkyte_at_ORA10G>
ops$tkyte_at_ORA10G> insert into p values ( 1 );  

1 row created.  

ops$tkyte_at_ORA10G> insert into p values ( 2 );  

1 row created.  

ops$tkyte_at_ORA10G> commit;  

Commit complete.

SESSION 2:
         ops$tkyte_at_ORA10G> insert into c values ( 1 );  

         1 row created.

SESSION 1:
ops$tkyte_at_ORA10G> delete from p where x = 2; (block)

SESSION 3:

                   ops$tkyte_at_ORA10G> insert into c values ( 1 );
                   (block)




 

>
>Could some one explain the following via an example.
>
>Quote from J. Lewis: the problem of two processes trying to insert new
>rows into two tables, and crossing over in their use of uncommitted
>primary keys.
>
>Quote from Metalink: what happens when you have deadlock on insert due
>to primary key overlap. This is relatively easy to reproduce, and
>results in
>deadlock where each process holds X mode and waits on S mode. Also,
>row waited on is 'no row'.
>
>Thanks for any pointers.
>wagen
>
>
>
>Billy wrote:
>> wagen123_at_yahoo.com wrote:
>> > Hi,
>> >
>> > Quote fron Steve Adams website - "you need indexes on all the foreign
>> > key columns. Otherwise, you need table locks to be disabled on any
>> > table that is missing a foreign key index"
>>
>> If I'm not mistaken this is no longer the case with 9i and later (the
>> child table lock)
>>
>> One always has to be careful with articles on Oracle as Oracle is
>> always elvolving and what was true a year or three ago may not be the
>> case anymore. Can get quite "interesting" at times..
>>
>> --
>> Billy
>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation