Re: Urgent Help ! Share Row Exclusive Lock when cascading delete.

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 10 Jul 1999 15:49:10 GMT
Message-ID: <37876b19.132103@news.demon.nl>

On Sat, 10 Jul 1999 23:06:34 +0800, Michael Ho <infoage_at_hk.super.net> wrote:

>I have encounter a situation. When delete a row with a on delete cascade
>foreign key constraint, the statement will process a share row exclusive
>table lock (SRX Lock - lock mode 5) on the cascading delete tables.
>Such action prevent all insert, update, delete (RX Lock) on those
>tables. Obviously, such situation is unacceptable.
>
>Anybody try implementing an on delete cascade foreign key ? Do you
>encounter such problem ? Is it a normal oracle behaviour or is it an
>oracle bug ?
>
>I am using Oracle 8.0.4.3.7 on Intel NT 4.0(SP4).
>
>Can somebody help me out of this ?
>
>Regards,
>Michael
>

Hi Michael,
Check for indexes on the foreign keys. Whenever there is no index Oracle will lock the complete table, whenever there is an index Oracle will lock the detail rows only.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Jul 10 1999 - 10:49:10 CDT