RE: wierd locking issue
Date: Wed, 27 Feb 2008 16:28:34 -0500
Got ya, Single threading.
- Mark D Powell -- Phone (313) 592-5148
From: Kurt Franke [mailto:Kurt-Franke_at_web.de] Sent: Wednesday, February 27, 2008 2:43 PM To: Powell, Mark D; oracle-l_at_freelists.org Subject: RE: wierd locking issue
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
> LOCK TABLE your_table IN EXCLUSIV MODE;
> 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
> > 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?