RE: wierd locking issue

From: Kurt Franke <>
Date: Wed, 27 Feb 2008 20:42:51 +0100
Message-Id: <>

the LOCK TABLE statement is not there to hold a lock while the following ALTER TABLE ... ADD/SPLIT/DROP PARTITION statement is executed but to avoid starting the partitioning statement while a concurrent one is already running because in this case the new started partitioning statement will break immediatly with an exception which will cause unnecessary manually handling in an automatic job.

this will work without any other handling if only one try is done while a concurrent partitioning statement is running.

if more than one does waiting with on such a lock only one of these waiters can continue normally with the partitioning statement while the others will get an ORA-00054 because of the fact you described that a commit is issued first inside a ddl statement.

to handle those possible multiple concurrent partitioning statements on a table just catch the ORA-00054 in an exception handler and try the same again, possible with a limit counter to make sure no endless loop will occure in unexpected circumstances.

catch and do again without the LOCK TABLE will cause a busy loop with a lot of CPU consumption and should be avoided because no wait is involved.



> Unfortunately, based on my experience trying to add indexes to busy
> tables since the ALTER TABLE is a DDL statement there is an implicit
> commit before it runs which releases the LOCK TABLE and between the
> release of the table lock and the execution of the ALTER some other
> session gains access to the object. Even if you get the table lock you
> might end up still getting the error. I would still give it a try but
> it is not a foolproof solution.

> you may issue a
> just before the ALTER TABLE ... ADD/SPLIT/DROP PARTITION ...
> in your code to force a wait.
> > I have code that adds and drops partitions. I am constantly getting
> this error. I am doing a few things to track this down and alleviate it.
> >
> > 1. at the beginning of the execution of my package I issue:
> > alter session set ddl_wait_for_locks=true;
> >
> > now I thought that with this set to true, I would just wait when
> adding or dropping a partition instead of erroring out?
> >
> regards
> kf

Received on Wed Feb 27 2008 - 13:42:51 CST

Original text of this message