Re: Delete of child-entry locks father-table

HerbertMue wrote:
> Charles Hooper schrieb:
>
> > HerbertMueller3_at_gmx.at wrote:
> > > If I delete data from a child-table, then the father tables get locked.
> > > I can not understand the reason for locking the father-table when
> > > deleting child-entries.
> > >
> > > Can someone explane that to me???
> > ---------------------------------------------------------------------------------------------------
> > >
> > > If the deletion job and the "make valid task" run at the same time than
> > > one task do not work, because the config-table is locked.
> >
> > It could be a problem with un-indexed foreign keys. A Google search
> > may help:
> > unindexed foreign keys kyte
> >
> > Some of the results returned by the above:
> > http://www.jlcomp.demon.co.uk/faq/fk_ind.html
> > http://tkyte.blogspot.com/2006/11/interesting-post.html
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> @Charles Hooper
> The FK has an index ==> look at the following piece of code:
>
> CREATE INDEX FK_TEST_PRODUCTION_CONFIGID ON TEST_PRODUCTION
> (CONFIGID);
>
> ALTER TABLE TEST_PRODUCTION ADD (
> FOREIGN KEY (CONFIGID)
> REFERENCES TEST_CONFIG (CONFIGID)
> DEFERRABLE INITIALLY IMMEDIATE);
I read the title of your post, the first paragraph of your post, and the last paragraph of the post. I did not look at the code that you posted.

In the code, you wrote:
LOCK TABLE TEST_CONFIG IN EXCLUSIVE MODE NOWAIT; Then:
SELECT description, username
  FROM TEST_CONFIG
 WHERE VALIDFROM IS NULL; Why are you locking the full table? If you remove the full table lock and the SELECT, then replace the SELECT with this, would it achieve the desired results?
SELECT description, username
  FROM TEST_CONFIG
 WHERE VALIDFROM IS NULL
FOR UPDATE; The lock on the returned rows is released on a COMMIT or ROLLBACK.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Dec 03 2006 - 10:28:49 CST