Re: Delete of child-entry locks father-table

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 :-) Received on Sun Dec 03 2006 - 11:23:53 CST