Re: Delete of child-entry locks father-table

hasta_l3_at_hotmail.com wrote:
> Charles Hooper wrote :
> >
> > 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;
> >
>
> Without the LOCK TABLE, phantom reads may very well kill his code ....
>
> > SELECT description, username FROM TEST_CONFIG
> > WHERE VALIDFROM IS NULL;
>
> ...
>
> > UPDATE TEST_CONFIG
> > SET VALIDFROM = validfromdate
> > WHERE VALIDFROM IS NULL;
>
> (not saying that I like LOCK TABLE :-)

I wonder if the OP has investigated different transaction isolation levels?

An isolation level of SERIALIZABLE may be sufficient to completely eliminate the need to issue LOCK TABLE commands. I demonstrated this feature in a subject thread titled "column update order" on the comp.databases.oracle.misc group:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/b1475f7f301c403d/1d2a238f8439a578?lnk=st&q=&rnum=33#1d2a238f8439a578

This concept is demonstrated more thoroughly in chapter 7 of Tom Kyte's "Expert Oracle Database Architecture" book. His book also describes the use of the SELECT FOR UPDATE syntax that I posted in this thread.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Dec 05 2006 - 07:34:31 CST