Just as a row level lock will block another session from obtaining a lock on the same row, a session holding a named lock created with DBMS_LOCK can prevent another session obtaining a lock with the same name.
(updated 21.11.2016) An essential difference is that by default these locks will survive a commit or rollback. Although you can chose to release them on commit.
In my package MY_PACKAGE, I have a procedure MY_PROCEDURE. I want to ensure that only a single instance of MY_PROCEDURE can run at any one time in my database.
CREATE OR REPLACE PACKAGE BODY my_package AS … k_module CONSTANT VARCHAR2(48 CHAR) := $$PLSQL_UNIT; --name of package for instrumentation l_lockhandle VARCHAR2(128); -- lock handle for session lock object … PROCEDURE my_procedure (/*other parameters*/ ,p_lock_timeout INTEGER DEFAULT 0 /*override lock timeout*/ ) IS l_retcode NUMBER; … BEGIN unlock;/*unlock any session lock from previous execution in this session*/ DBMS_LOCK.ALLOCATE_UNIQUE(k_module,l_lockhandle); /*take out an exclusive lock*/ l_retcode:=DBMS_LOCK.REQUEST(lockhandle=>l_lockhandle ,lockmode=>DBMS_LOCK.x_mode ,timeout=>p_lock_timeout /*,release_on_commit=>FALSE by default*/ ); /*check we have the lock*/ IF l_retcode<>0 THEN raise_application_error(-20000,k_module||'.MY_PROCEDURE is already running'); END IF; … /*do rest of package*/ … unlock; END my_procedure;
- A package global constant is set to $$PLSQL_UNIT$$. This evaluates to the name of the package, in this case MY_PACKAGE. I often use this during instrumentation to pass the name of the package to DBMS_APPLICATION_INFO. In this case, it will be used as the name of the lock.
- I also have a package global variable L_LOCKHANDLE which will be used as the handle of the lock. The contents of this variable will be available to subsequent instances of the package in the same session.
- A unique lock identifier is allocated with DBMS_LOCK.ALLOCATE_UNIQUE. The lock is available for the life of the session, up to a default timeout of 10 days. The name of the lock is the name of the package taken ultimately from $$PLSQL_UNIT$$.
- The lock is acquired with the DBMS_LOCK.REQUEST function. It is possible to pass a timeout in section to this function for which it will wait if it cannot acquire the lock.
- The lock is requested in exclusive mode. Other lock modes are available, but I only want one session to be able to acquire this lock.
- If the request function returns a non-zero code then the lock has not been acquired and the package raises an exception with a custom message.
If the package failed with an error it would continue to hold the lock in the session. Restarting the procedure in the same session would generate an error because the session could not acquire the lock again. I put the code to release the lock in a separate procedure. The lock handle is stored in a package global variable, so the handle will be available from previous executions of the package in the same session. The first and last thing MY_PROCEDURE does is to call the unlock procedure.
ERROR at line 1: ORA-20000: MY_PACKAGE.MY_PROCEDURE is already running
I also found it useful to be able to call the unlock procedure during debugging, so I exposed it in the package header.
DBMS_LOCK is a simple and robust mechanism that can easily be incorporated into a package. Even if you can control execution concurrency via whatever you use to schedule the package, it is a useful safety mechanism.
PROCEDURE unlock IS l_retcode NUMBER; … BEGIN … IF l_lockhandle IS NULL THEN debug_msg('no lock handle',9); ELSE l_retcode:=DBMS_LOCK.RELEASE(l_lockhandle); /*release lock*/ debug_msg('dbms_lock.release('||l_lockhandle||') return code '||l_retcode,8); IF l_retcode = 0 THEN debug_msg('Lock Released',2); ELSIF l_retcode = 3 THEN debug_msg('Parameter Error',8); ELSIF l_retcode = 4 THEN debug_msg('Do not own lock specified by lockhandle '||l_lockhandle,8); ELSIF l_retcode = 5 THEN debug_msg('Illegal lock handle '||l_lockhandle,8); END IF; END IF; … END unlock;