Re: preventing NOWAIT locks in DDL commands

Kirill Richine <kirill_at_cs.ualberta.ca> wrote in article <6o5nqm$944$1_at_scapa.cs.ualberta.ca>...
> Hi!
>
> Suppose there is a referential constraint between A (parent) and B
> (child). Then if one tries to delete from A, Oracle will have to check
> that no children in B exist for the record being deleted from A.
>
> If such deletions are massive, one could disable the constraint to
> avoid these checks and thus speed them up.
>
> So, the application disables the constraint, deletes the records, and
> then reenables the constraint. We assume that the application is
> trusted not to violate the constraint.
> The application reenables the constraint:
>
> ALTER TABLE B ENABLE CONSTRAINT A_FK;
>
> Now suppose that another program modifies either A or B. Suppose that
> this other program is also trusted not to violate the integrity
> constraint.
>
> The problem is that whenever the first program attempts to issue the
> above DDL and the second program has an uncommitted transaction that
> locked A or B, the first program gets the following error:
>
> ORA-00054 resource busy and acquire with NOWAIT specified
>
> It appears to be impossible to give the above DDL an option not to use
> NOWAIT in the attempt to acquire the lock.
>
> It also appears to be of little use to LOCK TABLE command before
> issuing the DDL -- because any DDL commits (thus releasing the lock)
> before and after it executes.
>
> Is it possible to make the DDL (the ALTER TABLE...) command wait for
> the lock to become available?
>
> Thank you.
> k&
>
>

Perhaps you can create your own locking scheme using the oracle dbms_lock package. The 'locks' from this package are more like semaphores/mutexes: they will not LOCK a table, but you (the developer) can use it to manage a shared resource (a printer for example) between multiple applications.

gert
--
reply address is altered to keep the spam down remove the nospamplease part to reply... Gert Rijs
gem at wirehub.nl
www.wirehub.nl/~gem Received on Sat Jul 11 1998 - 04:06:59 CDT