Re: dbms_lock

From: Mark D Powell <>
Date: Sat, 8 Aug 2009 11:19:21 -0700 (PDT)
Message-ID: <>

On Aug 8, 6:53 am, wrote:
> I have a server program that should run in active/standby mode:
>    - a copy of the program is running on two different computers
>    - only one copy should be active
>    - if the active copy exits, fails, etc, the standby should take
>      over and become the active
>    - when the original active resumes, it is now the standby
> Using a dbms_lock makes this quite simple, put this code
> at the top of the program:
>     dbms_lock.allocate_unique('myserver', v_lockhandle);
>     v_result := dbms_lock.request(v_lockhandle, dbms_lock.x_mode);
> 1.  Is there any problem with holding a lock for a long time?  The
>     active may stay active for months on end.
> 2.  If there is a problem with holding a lock for a long time,
>     what is a "reasonable" time to keep a lock open?
> 3.  The most common way this lock will be released is for the
>     process or the computer to die.  Are there any problems
>     related to this?
> Many TIA,
> Mark
> --
> Mark Harrison
> Pixar Animation Studios

We have used user locks since Oracle introduced them, usually to prevent concurrent running of a business process that cannot support concurrent use. Most of our single-threaded processes use the timeout parameter to notify the running program that the lock is in use, meaning the process is already running, so the second execution usually terminates. In cases where we just want to single thread the process we either do not use the timeout or the program loops and tries again a few seconds latter.

In theory you can wait forever though I have to wonder if a program that was blocked for months would not get swapped out or otherwise be subject to some type of network/OS timeout depending on your environment.

I would say try it and see how it works. You could always change the program(s) to poll for the lock, sleep, and try again so it is an active process. This second approach would be my approach to the problem.

I do not see any issue with a session holding the UL for as long as the instance is running even if it is months. Oracle does the same with file recovery locks.

HTH -- Mark D Powell --

Is the standby process going to be lock waited this entire time, or are you going to use the timeout parameters so the second session will be notified the lock is being held and it will cycle through logic Received on Sat Aug 08 2009 - 13:19:21 CDT

Original text of this message