DBMS_LOCK

Concurrent activity in a complex system is, well… complex. Oracle’s standard locking and blocking mechanisms do an admirable job in general but there will be times when more careful control over processes is required. The package DBMS_LOCK exposes Oracle’s internal locking mechanism specifically for this purpose. According to the DBMS_LOCK documentation, user locks can be used for:

• Providing exclusive access to a device, such as a terminal
• Providing application-level enforcement of read locks
• Detecting when a lock is released and cleanup after the application
• Synchronizing applications and enforcing sequential processing

The last point is where DBMS_LOCK is frequently useful. Systems often have critical background processes that are required to be run serially. To ensure multiple instances of the job are not executed at once it’s quite simple to use DBMS_LOCK to take an exclusive lock and thereby signal to any other processes that the job is in-flight. Essentially this can be coded as:

DECLARE
   -- the number of seconds before the lock request time outs
   c_request_timeout       CONSTANT NUMBER(4) := 5;
   
   -- constants for lock request responses
   -- from dbms_lock documentation
   c_response_success      CONSTANT NUMBER := 0;
   c_response_timeout      CONSTANT NUMBER := 1;
   c_response_deadlock     CONSTANT NUMBER := 2;
   c_response_param_err    CONSTANT NUMBER := 3;
   c_response_own_lock     CONSTANT NUMBER := 4;
   c_response_illegal_lock CONSTANT NUMBER := 5;

   -- the unique name of the lock identifier
   l_job_identifier        VARCHAR2(128) := 'job_101';
   
   l_lock_handle           VARCHAR2(128);
   l_request_response      INTEGER;
   l_release_response      INTEGER;
BEGIN

   -- obtain a lock handle from our job identifier string 
   dbms_lock.allocate_unique (lockname        => l_job_identifier
                             ,lockhandle      => l_lock_handle
                             ,expiration_secs => 864000);

   -- now take out a an exclusive lock using the lock handle
   l_request_response := dbms_lock.request (lockhandle        => l_lock_handle
                                           ,lockmode          => dbms_lock.x_mode
                                           ,timeout           => c_request_timeout
                                           ,release_on_commit => FALSE);

   IF (l_request_response = c_response_timeout) THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to obtain job lock due to time-out. Another process currently holds the lock');
   ELSIF (l_request_response != c_response_success) THEN
      RAISE_APPLICATION_ERROR (-20000, 'Job lock request failed with response of ' || TO_CHAR(l_request_response));
   END IF;
   
   -- the job itself goes here
   
   -- job finished so we'll clean up after ourselves
   l_release_response := dbms_lock.release (lockhandle => l_lock_handle);
   
   IF (l_release_response != c_response_success) THEN
      RAISE_APPLICATION_ERROR (-20000, 'Job lock failed with response of ' || TO_CHAR(l_release_response));
   END IF;
END;

There are a few points to note in the above script:

• The lock can be set to be released on COMMIT, just like a DML operation row level lock. Otherwise it will be retained until explicitly released or the session terminated.
• The lock name is global across the entire database. Hence it is important the name chosen can in no way conflict with a request for a totally different purpose. Choose a name that can not be mistaken for any other purpose.
• Sessions waiting to obtain a lock via DBMS_LOCK.REQUEST wait on the event “enq: UL – contention”
• The ALLOCATE_UNIQUE assigns a lock handle to the lock name specified. This association will remain in force for a minimum of time as specified by the expiration_secs parameter, which defaults to 864,000 seconds (10 days). After this time Oracle may assign a new lock handle to the lock name.

The last point above bears expansion. DBMS_LOCK.ALLOCATE_UNIQUE sets an expiry time on the lock handle, which defaults to 10 days. If you use the generated lock handle after this period of time then expect trouble. The lock handle will still be a valid handle but if another session calls ALLOCATE_UNIQUE with the same lock name it may be assigned a different handle. You will then end up with multiple sessions all believing they have obtained the lock on the lock name… problems are more than likely to occur.

You can see a listing of the locks allocated by DBMS_LOCK.ALLOCATE_UNIQUE by querying SYS.DBMS_LOCK_ALLOCATED:

SELECT *
FROM    sys.dbms_lock_allocated

Advertisement