RE: wierd locking issue

  • From: Andy Rivenes <arivenes@xxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 27 Feb 2008 14:46:31 -0800

Another approach that I've had success with is to use application level locking on your objects using dbms_lock. In an application with a partitioned table and many "loading" jobs it is almost impossible to perform partition maintenance without stopping all of the loaders. I've had excellent success with the following scenario:


1) Any insert, update or delete statements first issue a share lock on the object using dbms_lock, perform their action, and the release the lock, something like
      --
      -- Take an application level share lock on the table so other processes
      -- don't attempt an exclusive operation.
      --
      IF dblock.get_lock_tf(lockname_in => l_loadtab,
        MODE_IN=>DBMS_LOCK.s_mode,
        timeout_IN => l_lockintvl) THEN

      Perform operation

      --
      -- Release the application lock on the table
      --
      IF dblock.release_tf(lockname_in => l_loadtab) THEN

2) Any DDL command must first issue an exclusive lock, check to make sure the action hasn't happened while waiting for the lock, perform the action, and then release the lock, something like:

        --
        -- If partition is missing, take an application lock and create
-- the missing partition. This waits for an exclusive lock on the table
        -- otherwise the partition manipulation will fail.
        --
IF dblock.get_lock_tf(lockname_in => l_loadtab, MODE_IN=>DBMS_LOCK.x_mode, timeout_IN => l_lockintvl) THEN
          --

        Perform actions

        --
        -- Release the application lock on the table
        --
        IF dblock.release_tf(lockname_in => l_loadtab) THEN

By using application level locking I no longer have any trouble with partition maintenance and the slick thing is that once the exclusive lock is obtained all of the share lock requests just stack up until the maintenance operation is complete and then start up again when the lock is released.

Andy Rivenes



At 01:28 PM 2/27/2008, Powell, Mark D wrote:

Got ya, Single threading.

-- Mark D Powell --
Phone (313) 592-5148


-----Original Message-----
From: Kurt Franke [mailto:Kurt-Franke@xxxxxx]
Sent: Wednesday, February 27, 2008 2:43 PM
To: Powell, Mark D; oracle-l@xxxxxxxxxxxxx
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.


regards

kf


> 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
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
>




--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l


Other related posts: