Re: Delete of child-entry locks father-table

From: <thomas.kyte_at_oracle.com>
Date: 4 Dec 2006 04:14:20 -0800
Message-ID: <1165234460.675479.129350@n67g2000cwd.googlegroups.com>

HerbertMue wrote:
> Thanks for specifying in detail.
>
> But even now I do have the following question (don't look at the lock
> table and the "other bad code"). I only want to get answered this
> principal (fundamental, basic) question.
> <Question>
> Why does a DELETE of a child(slave)-entry causes a
> lock of the father (master, parent) table???
> </Question>

It does NOT.

There is no lock placed on the parent table that would prevent any other transaction from modifying the parent table or reading it.

The table lock would be on the CHILD table and only if the following is true:

1. you have an un-indexed foreign key from the child to the parent
2. you update the parent tables primary key OR you delete from parent.

then the child table will be locked

1. for the duration of the delete or update in 9i and above
2. for the duration of the transaction (until you commit) in 8i and below.

ops$tkyte%ORA10GR2> create table p ( x int primary key ); Table created.

ops$tkyte%ORA10GR2> create table c ( x references p ); Table created.

ops$tkyte%ORA10GR2> insert into p values ( 1 ); 1 row created.

ops$tkyte%ORA10GR2> insert into c values ( 1 ); 1 row created.

ops$tkyte%ORA10GR2> commit;
Commit complete.

ops$tkyte%ORA10GR2> insert into p values ( 2 ); 1 row created.
/* Now, we have an outstanding transaction on P, if a delete of the child record requires a table lock on P, it would block - but it doesn't (using an autonomous transaction to create a new transaction - one that could not lock C at all */

ops$tkyte%ORA10GR2> declare
  2 pragma autonomous_transaction;
  3 begin

  4      delete from c where x = 1;
  5      commit;

  6 end;
  7 /
PL/SQL procedure successfully completed.

/* that did not lock P - it did not need to - in fact P cannot be locked since we have that transaction still outstanding:*/

ops$tkyte%ORA10GR2> declare
  2 pragma autonomous_transaction;
  3 begin

  4      lock table p in exclusive mode;
  5      commit;

  6 end;
  7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4

ops$tkyte%ORA10GR2> commit;
Commit complete.

/* Now, an operation on P might try to lock C, and we can see this via the following snippet: */

ops$tkyte%ORA10GR2> insert into c values ( 2 ); 1 row created.

/* that will prevent a full table lock on C being taken by any other transaction - we are leaving a transaction "open" on C */

ops$tkyte%ORA10GR2> declare
  2 pragma autonomous_transaction;   3 begin

  4          delete from p where x = 1;
  5          commit;

  6 end;
  7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4

/* the deadlock was because our first transaction (insert into c) prevents the second transaction from taking a full table lock on C (the delete from p will lock C for the duration of the delete). Since we have only a single session (albeit with two independent transactions) this is a deadlock and our one transaction is signaled */

ops$tkyte%ORA10GR2> commit;

Commit complete.

So, there is NO table lock on the parent table - there may be on the child if you have an unindexed foreign key and update the parent primary key (and we all know we'd never do that in real life right...) or delete from the parent table.

And if you delete from the parent table, it is highly likely you would want an index on child in order to avoid the full tablescan of it for each parent record deleted... Received on Mon Dec 04 2006 - 06:14:20 CST