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