User-named locks with DBMS_LOCK

The application I’m working on involves a number of views that provide an interface between two databases that have quite different models. The views implement inserts, updates and deletes via Instead-of triggers.

On one of these views, the update trigger needs to actually do an update and an insert on the source table: it must update the existing row to mark it as “closed”, and then insert a new row to take its place. In this way the view hides the fact that the table keeps a history of changes.

Now, a logical constraint is that there can only be one “open” record for a given entity. Therefore, there can be zero or more “closed” records, and there must be a single “open” record. This constraint, however, has not been implemented on the database (naughty naughty) because our code is perfect and never makes a mistake 😉

This worked perfectly well until our thorough Java guy decided to do some load testing, in which a process would hammer away inserting, updating and deleting the same row over and over again, in multiple concurrent sessions.

This had the result that we ended up seeing lots of “open” records for the entity, which violates the logical constraint described above. “That’s not allowed” I said, and soon realised that the fact that multiple sessions are updating the same row means that even a trigger-based constraint check will not solve the problem, since the triggers cannot see updates from other sessions.

So, we need to have a lock of some sort, so that when one session wants to update an entity, other sessions cannot start updating it as well until the first one is finished. I can’t just lock the row I want to update, because after I’ve updated it, I still need the lock while I insert the new row.

The solution is to use a package supplied with Oracle calls DBMS_LOCK, with which I can request “user-named locks”. This package gives a lot of options but I’m only interested in simple Exclusive locks, so I’ll also create some wrapper procedures.

Easy, right? Well, yeah once you get past a few gotchas.

Try #1:

-- wrapper function to Request a lock; returns a lock handle
FUNCTION request_lock (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
  lock_status NUMBER;
  lock_handle VARCHAR2(128);
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE (
    lockname => lock_name,
    lockhandle => lock_handle,
    expiration_secs => 864000); -- 10 days
  lock_status := DBMS_LOCK.REQUEST(
    lockhandle => lock_handle,
    lockmode => DBMS_LOCK.X_MODE, -- eXclusive
    timeout => DBMS_LOCK.MAXWAIT, -- wait forever
    release_on_commit => TRUE);
  IF lock_status > 0 THEN
    RAISE_APPLICATION_ERROR(-20000,'request_lock failed: ' || lock_status);
  END IF;
RETURN lock_handle;
END request_lock;

-- wrapper to release a lock; call with the lock handle obtained previously
PROCEDURE release_lock (lock_handle IN VARCHAR2) IS
  lock_status NUMBER;
BEGIN
  lock_status := DBMS_LOCK.RELEASE(
    lockhandle => lock_handle);
  IF lock_status > 0 THEN
    RAISE_APPLICATION_ERROR(-20000,'release_lock failed: ' || lock_status);
  END IF;
END release_lock;

--example calling code
...
DECLARE
  lock_handle VARCHAR2(128);
BEGIN
  lock_handle := request_lock('MYLOCK1');
  -- do some stuff
  release_lock(lock_handle);
END;
...

The above code “worked” ok (although I hadn’t yet tested to see if it was actually getting the lock) until I started calling it from triggers on the views, when I started getting “ORA-04092: cannot commit in a trigger”. What the? I wasn’t doing any commits in my triggers. I couldn’t find any references to the DBMS_LOCK functions doing commits in the 10g documentation, but I thought, ok let’s make these wrappers Autonomous, so they can commit in peace:

Try #2:

FUNCTION request_lock (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  lock_status NUMBER;
  lock_handle VARCHAR2(128);
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE (
    lockname => lock_name,
    lockhandle => lock_handle,
    expiration_secs => 864000); -- 10 days
  lock_status := DBMS_LOCK.REQUEST(
    lockhandle => lock_handle,
    lockmode => DBMS_LOCK.X_MODE, -- eXclusive
    timeout => DBMS_LOCK.MAXWAIT, -- wait forever
    release_on_commit => TRUE);
  IF lock_status > 0 THEN
    RAISE_APPLICATION_ERROR(-20000,'request_lock failed: ' || lock_status);
  END IF;
  RETURN lock_handle;
END request_lock;

PROCEDURE release_lock (lock_handle IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  lock_status NUMBER;
BEGIN
  lock_status := DBMS_LOCK.RELEASE(
    lockhandle => lock_handle);
  IF lock_status > 0 THEN
    RAISE_APPLICATION_ERROR(-20000,'release_lock failed: ' || lock_status);
  END IF;
END release_lock;

No good: I got “ORA-06519: active autonomous transaction detected and rolled back”! This didn’t make much sense at the time, but I put a COMMIT in anyway. But of course, this wouldn’t work with the triggers. Anyway, I started experimenting and soon realised that even if I got the wrappers to work, the locks weren’t being made anyway, and I was able to request locks multiple times, which I expected to be impossible if the wrapper was working correctly.

Finally, I logged in as a user with some DBA privileges and read the specification of the SYS.DBMS_LOCK package. The one thing that jumped out at me was a little comment under ALLOCATE_UNIQUE:

“This routine will always do a commit.”

There was my answer. It was the ALLOCATE_UNIQUE that was doing a commit, not REQUEST or RELEASE. And, because I was setting release_on_commit => TRUE in the call to REQUEST, whenever I committed the lock was being released, straight away.

So I changed release_on_commit => FALSE, and moved ALLOCATE_UNIQUE to a separate function (set up as an Autonomous Transaction). Having read a bit more I realised I could make this API nicer to the programmers by allowing the release_lock to accept the lock name instead of requiring them to manage the lock handle. I also improved the error messages.

Try #3:

-- internal function to get a lock handle
-- (private for use by request_lock and release_lock)
FUNCTION get_handle (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  lock_handle VARCHAR2(128);
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE (
    lockname => lock_name,
    lockhandle => lock_handle,
    expiration_secs => 864000); -- 10 days
  RETURN lock_handle;
END get_handle;

PROCEDURE request_lock (lock_name IN VARCHAR2) IS
  lock_status NUMBER;
BEGIN
  lock_status := DBMS_LOCK.REQUEST(
    lockhandle => get_handle(lock_name),
    lockmode => DBMS_LOCK.X_MODE, -- eXclusive
    timeout => DBMS_LOCK.MAXWAIT, -- wait forever
    release_on_commit => FALSE);
  CASE lock_status
    WHEN 0 THEN NULL;
    WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');
    WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');
    ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed: ' || lock_status);
  END CASE;
END request_lock;

-- wrapper to release a lock
PROCEDURE release_lock (lock_name IN VARCHAR2) IS
  lock_status NUMBER;
BEGIN
  lock_status := DBMS_LOCK.RELEASE(
    lockhandle => get_handle(lock_name));
  IF lock_status > 0 THEN
    RAISE_APPLICATION_ERROR(-20000,'release lock failed: ' || lock_status);
  END IF;
END release_lock;

--example calling code
...
BEGIN
  request_lock('MYLOCK1');
  -- do some stuff
  release_lock('MYLOCK1');
END;
...

This worked perfectly, even from triggers. I even tried it out with multiple sessions:

Session 1:

exec request_lock('JEFF');

Session 2:

exec request_lock('JEFF');

At this point, session 2 just hung – it was waiting for Session 1 to release the lock.

Session 1:

exec release_lock('JEFF');

Now session 2 returned and had the lock. The same thing happened if I just closed down session 1.

If I changed request_lock to set timeout to some value (e.g. 3 seconds), session 2 would wait for that amount of time, then raise my error message “request lock failed – 1” (1 is the status code when a lock request times out).

I tried it with a deadlock situation, with timeout = MAXWAIT:

Session 1:

exec request_lock('MYLOCKA');

Session 2:

exec request_lock('MYLOCKB');

Session 1:

exec request_lock('MYLOCKB');

At this point session 1 hangs as it waits for session 2 to release the lock.

Session 2:

exec request_lock('MYLOCKA');

This is a deadlock. Both sessions are waiting for a lock held by the other session. After a few seconds Oracle automatically detects the deadlock and picks one session (at random, apparently) and it returns the lock status 2 (my code raises the error message “deadlock detected”). The other session stayed hung, however – it was still waiting, but there was no deadlock.

I tried the above scenario again, but with timeout = 5 seconds. If I executed the request_locks quick enough, I got “deadlock detected” on one session, and the other session eventually timed out.

So, everything works as expected, and the Java guys can hammer away at my views with impunity.

UPDATE for Oracle 12.1 and later:

The get_handle function no longer needs to be autonomous, since DBMS_LOCK now provides an autonomous variant of ALLOCATE_UNIQUE:

FUNCTION get_handle (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
  lock_handle VARCHAR2(128);
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE_AUTONOMOUS (
    lockname => lock_name,
    lockhandle => lock_handle,
    expiration_secs => 864000); -- 10 days
  RETURN lock_handle;
END get_handle;

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOCK.html#GUID-0D6C0622-05CA-4259-8059-330D512E1574

(thanks to Kim Berg Hansen for the update)

SQL problem
Avoiding Lost Updates: Protecting Data in a Multi-user environment

Comments

  1. Hi Alexander,Thanks for your question.Check the sequence in which you call dbms_lock.request and dbms_lock.release. If you try to release a lock that has already been released, or which was never requested by that session in the first place, I'd expect to see a return value of 4.Jeff

  2. Hi Jeff,We are calling from the oracle form DB package to lock and release the lock and sometimes DBMS_LOCK.release returns sometimes 4 after releasing the lock All I found in Oracle documentation that it means: Do not own lock specified by id or lockhandle.I didn’t find any reason why it could happen. Looks like the session id which is trying to release lock Is not an owner but we have only one form running.Any advice will be much appreciated.Thanks,AlexHere is the codeLock process:— cLockHandle:=ApplicationManager.lockprocess('MONITOR');—function LockProcess (cProcessName in varchar2,nTimeout in integer := 0,bReleaseOnCommit in boolean := false,nExpirationSecs in number := 0) return varchar2 is cLockHandle varchar2(128); nReturnValue integer := null; nLockMode integer := 6; –exclusive mode (ULX) begin dbms_lock.allocate_unique(upper(substr(cProcessName,1,128)), cLockHandle,nExpirationSecs); nReturnValue := dbms_lock.request(cLockHandle, nLockMode , nTimeout, bReleaseOnCommit); –lockmode = 6 : exclusive mode (ULX) insert into test values ('LockProcess cLockHandle '||cLockHandle||' cProcessName '||cProcessName ) ; commit ; if nReturnValue=0 then return cLockHandle; elsif nReturnValue=1 then PdError.Throw(4008,cProcessName,'1-timeout'); elsif nReturnValue=2 then PdError.Throw(4008,cProcessName,'2-deadlock'); elsif nReturnValue=3 then PdError.Throw(4008,cProcessName,'3-parameter error'); elsif nReturnValue=4then PdError.Throw(4008,cProcessName,'4-already own'); elsif nReturnValue=5 then PdError.Throw(4008,cProcessName,'5-illegal lockhandle'); else PdError.Throw(4008,cProcessName,'6-Unknown'); end if;end;Release lock:————————————————————————————————–procedure ReleaseLockByProcess (cProcessName in varchar2) is cLockHandle varchar2(128); return_value integer := null;begin dbms_lock.allocate_unique(upper(substr(cProcessName,1,128)), cLockHandle); insert into test values ('ReleaseLockByProcess cLockHandle '||cLockHandle||' cProcessName '||cProcessName) ; ReleaseLockByHandle(cLockHandle);end;————————————————————————————————–procedure ReleaseLockByHandle (cLockHandle in varchar2) is nReturnValue integer := null;begin if cLockHandle is not null then nReturnValue := dbms_lock.release(cLockHandle); if nReturnValue != 0 then PdError.Throw(4133,to_char(nReturnValue)); — ,'Error in releasing the lock:'||to_char(nReturnValue)); end if; end if;end;

  3. Thanks for you reply Jeff!

  4. Like the solution!

    Thanks for Sharing

  5. I’ cant create this function:

    FUNCTION request_lock (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
    lock_status NUMBER;
    lock_handle VARCHAR2(128);
    BEGIN
    DBMS_LOCK.ALLOCATE_UNIQUE (
    lockname => lock_name,
    lockhandle => lock_handle,
    expiration_secs => 864000); — 10 days
    lock_status := DBMS_LOCK.REQUEST(
    lockhandle => lock_handle,
    lockmode => DBMS_LOCK.X_MODE, — eXclusive
    timeout => DBMS_LOCK.MAXWAIT, — wait forever
    release_on_commit => TRUE);
    IF lock_status > 0 THEN
    RAISE_APPLICATION_ERROR(-20000,’request_lock failed: ‘ || lock_status);
    END IF;
    RETURN lock_handle;
    END request_lock;

    error is dbms_lock mu st be declared. There is some addional step do to BEFORE using this script?

    • That most probably means the user doesn’t have EXECUTE privilege on the SYS.DBMS_LOCK package. Grant this and your function should compile.

  6. This article is helpful.

    Here is my question – Initially I have tried to request a lock (by sending a lock_name) as the parameter to dbms_lock.request. For some reason, it is not working. It gave mea message “illegal lock handle” (5).
    When I started to use allocate_unique (before request), it started working. Can you help me with an explanation.
    The only reason I was able to think is if the lock name is too long. I have probably used I think around 25 characters.
    Thank you.

    • Hi Viswa,

      dbms_lock.request requires a lock handle, which is NOT the lock name. You get a lock handle by first calling dbms_lock.allocate_unique, which returns a lock handle that you can then pass to dbms_lock.request.

      I hope this helps,

      Jeff

  7. Mike Smithers
    6 April 2020 - 5:02 am

    Jeff,

    thanks. This article has certainly stood the test of time.

    Mike

  8. Jeff, 15 years on, this still saved me time. Thanks!

    One question: in a way, did you double-fix the issue by both making the lock allocation execute in the separate transaction and also specifying “release_on_commit => FALSE”? If you left “release_on_commit => TRUE” as you had it in your first try, you should still get sequential executions as long as your code doesn’t do any premature commits. And you don’t need the release_lock API at all – or worry about what happens in case of an exception before releasing the lock?

    • Hi Al, yes, sometimes I will use Release on Commit, it depends on the situation. In some cases I want the lock to be released as soon as the logic that requires the lock has finished, but before the session has issued a commit; and I want to have explicit control over the lock duration. In many cases it won’t make any difference but if the code being run might take a while before it commits, I wouldn’t want it holding up other concurrent sessions which are waiting for it. Of course, in some cases I need the lock to survive past a commit, in which case there is no choice but to set this to False.

  9. Matthew Moisen
    2 June 2021 - 9:50 am

    Hi Jeff, I have a similar use case to what you described: a table where only one of many related records can have an active status, and the rest have an inactive status. I think I could solve this either using SELECT FOR UPDATE or DBMS_LOCK. Any idea how I pick between the two?

    • Jeffrey Kemp
      2 June 2021 - 9:57 am

      Hi Matthew,

      If you can use SELECT FOR UPDATE to stop other sessions messing with the records while you’re processing a change, use it. You generally only need DBMS_LOCK when there’s no specific record that you can lock.

      Jeff

  10. Hi Jeff,

    Which table does this proc(allocate_unique, request()) locks? We are not providing any table name here to lock. Are we acquiring lock on all tables?

    • Hi Swati,

      The DBMS_LOCK package doesn’t lock any table. It is merely a named lock – it serializes the path of code in which you add the call to request the lock.

      For example, if you have some external resource named SHARED_PRINTER, you could take a lock on ‘SHARED_PRINTER’ and the lock would ensure only one process can run your code at any one time.

      Jeff

  11. Kim Berg Hansen
    31 May 2023 - 3:52 pm

    Hi, Jeff

    Just leaving a quick note for others that google dbms_lock and reach this good information of yours.

    DBMS_LOCK package now supports ALLOCATE_UNIQUE_AUTONOMOUS, which does the same as your get_handle():

    https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOCK.html#GUID-0D6C0622-05CA-4259-8059-330D512E1574

    Docs state it’s supported since version 12.1, but it’s not in the docs until version 18 – that may have been a doc bug in 12.1 and 12.2 docs.

    Cheerio
    /Kim

  12. Thanks! Seconding Al’s comment from 2020. Your article article helped me understand the workings of the dbms_lock and saved me time.

Leave a Reply

Your email address will not be published / Required fields are marked *