Re: insert / delete locking conflict

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 24 Apr 1998 13:14:49 GMT
Message-ID: <35418f5a.1065091@192.86.155.100>

A copy of this was sent to Jeffrey Tristram Colket <jtc_at_acpub.duke.edu> (if that email address didn't require changing) On Thu, 23 Apr 1998 15:55:46 -0400, you wrote:

>I am experiencing a lock on a table when trying to insert from one Oracle
>session while another Oracle session is deleting from the table (the
>delete is actually propogated from a cascade delete but I have the
>columns of the foreign key indexed so there are no table level locks
>preventing the insert). Any ideas why I would be getting a row level
>lock or something similar preventing the insert? Any help would be
>greatly appreciated.
>
>Thanks,
>Jeff Colket

See the section in chapt 6 of the Application Developers Guide for Oracle7.x (section called "Concurrency Control, Indexes, and Foreign Keys").

Basically, if you have an unindexed foreign key, a full table lock on the child table (in 7.2 on up, in 7.1 on down it was the parent parent) will occurr when you update the parent PRIMARY KEY or delete from the parent.

If in fact, you have an unindexed foreign key (script to find out is included below) AND you are using ON DELETE CASCADE, you'll find performance of the delete to be much increased after indexing the child tables foreign key. If the child tables foreign key is not indexed, then a full scan of the child table must be done for every row deleted from the parent table.

• unindexed.sql -- finds unindexed foreign keys in a schema -----

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,

           a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,

	     max(decode(position, 1,     column_name,NULL)) || 
	     max(decode(position, 2,', '||column_name,NULL)) || 
	     max(decode(position, 3,', '||column_name,NULL)) || 
	     max(decode(position, 4,', '||column_name,NULL)) || 
	     max(decode(position, 5,', '||column_name,NULL)) || 
	     max(decode(position, 6,', '||column_name,NULL)) || 
	     max(decode(position, 7,', '||column_name,NULL)) || 
	     max(decode(position, 8,', '||column_name,NULL)) || 
	     max(decode(position, 9,', '||column_name,NULL)) || 
	     max(decode(position,10,', '||column_name,NULL)) || 
	     max(decode(position,11,', '||column_name,NULL)) || 
	     max(decode(position,12,', '||column_name,NULL)) || 
	     max(decode(position,13,', '||column_name,NULL)) || 
	     max(decode(position,14,', '||column_name,NULL)) || 
	     max(decode(position,15,', '||column_name,NULL)) || 
	     max(decode(position,16,', '||column_name,NULL)) columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name ) a, ( select table_name, index_name,

	     max(decode(column_position, 1,     column_name,NULL)) || 
	     max(decode(column_position, 2,', '||column_name,NULL)) || 
	     max(decode(column_position, 3,', '||column_name,NULL)) || 
	     max(decode(column_position, 4,', '||column_name,NULL)) || 
	     max(decode(column_position, 5,', '||column_name,NULL)) || 
	     max(decode(column_position, 6,', '||column_name,NULL)) || 
	     max(decode(column_position, 7,', '||column_name,NULL)) || 
	     max(decode(column_position, 8,', '||column_name,NULL)) || 
	     max(decode(column_position, 9,', '||column_name,NULL)) || 
	     max(decode(column_position,10,', '||column_name,NULL)) || 
	     max(decode(column_position,11,', '||column_name,NULL)) || 
	     max(decode(column_position,12,', '||column_name,NULL)) || 
	     max(decode(column_position,13,', '||column_name,NULL)) || 
	     max(decode(column_position,14,', '||column_name,NULL)) || 
	     max(decode(column_position,15,', '||column_name,NULL)) || 
	     max(decode(column_position,16,', '||column_name,NULL)) columns

    from user_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%' /  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Apr 24 1998 - 08:14:49 CDT