Re: deadlock

In article <960318123739.M0103180_at_s_haiden2.tsi.co.jp> syoujit_at_tsi.co.jp (Pauro Tanaka) writes:
>
>>> "Shitole Sanjay (OG)" <sanjay> wrote:
>>>
>>> >Oracle does acquire table level locks when tables involving dml operations
>>> >are defined using foregin key integrity constraints.
 

>>> >For example:
 

>>> > create table dept(dept_no number(5) primary key,
>>> > dept_name varchar2(30));
>>> > create table emp(emp_no number(5) primary key,
>>> > dept_no number(5),
>>> > constraint dept_fk foreign key (dept_no) references dept
>>> > on delete cascade);
>>> > (Assumption: No index on dept_no column of emp table)
>>> >Now perform following operations
 

>>> >1. User A : Delete dept where dept_no = 100;
>>> >2. User B : Update dept set dept_name = 'SALES' where dept_no = 200;
 

>>> >Result: User B has to wait for lock acquired by User B.
>
> I tried but User B didn't wait.
> I think that,
> User A request SRX lock on table emp.
> But User B doesn't request locks on emp table.
> (User B doesn't do anything to emp table.)
>
> By the way, I understand that sequential retrieval of child table by
> cascading deletion requires very high mode table locks.
>
> But I can't understand the reason why Oracle want to lock table
> in such high mode only when cascading deletion of child table.
>
> In general, ( like RDB, DB2, etc ) not only when sequential
> retrieval of cascading deletion but also when sequential retrie
> val of usual retrieval, if RDBMS want to
> achieve serializability, RDBMS has to lock table.(PR in RDB, S
> in DB2). I suspect that Oracle has given up serializability.(*)
> Again, I can't understand the reason why Oracle want to lock
> table when sequential retrieval caused by cascading deletion
> but Oracle doesn't want to lock when usual sequential retrieval.
> (*)My understanding is if you specify serializability to Oracle,
> Oracle always lock table not only when sequential retrieval,
> but also when oracle can use index.

Oracle doesn't give up serializibility because it bases the "existence" of a transaction on timestamps. So, "A user always sees a consistent view of all data, which consists of all data committed by other transactions as of that time and all changes made by the user up to that time." See Ch. 10 of the Server Concepts Manual, again. You have to mess around if you want consistency at different times - repeatable reads. You're on your own if you start manually adjusting the default locking for transaction-level consistency.

An argument can be made that Rdb has better locking, with full degree 3 consistency - what you see is what is in the database. With Oracle, if there are DDL statements, definitions might be changing, so a higher level of locking is required with a cascade. You wouldn't want to be modifying an object that someone else is deleting.

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O