I've moved...

This blog now has a new home - please update your shortcuts and readers to: www.jeffkemponoracle.com. Sorry for any inconvenience!

Wednesday, October 19, 2005

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.

[Full Article]


  1. Jeff, you saved my butt on this one, couldn't figure out the commit thing until I saw your post. Thanks a million!


  2. This was very helpful to me. Thanks for your efforts.

    Minor bug: I think v_lock_status needs to be declared in procedure "release_lock".

  3. Thanks, Kurt. I'll correct it.

  4. 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.


    Here is the code

    Lock process:


    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)

    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
    elsif nReturnValue=2 then
    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');
    end if;


    Release lock:

    procedure ReleaseLockByProcess (cProcessName in varchar2) is
    cLockHandle varchar2(128);
    return_value integer := null;
    dbms_lock.allocate_unique(upper(substr(cProcessName,1,128)), cLockHandle);
    insert into test values ('ReleaseLockByProcess cLockHandle '||cLockHandle||' cProcessName '||cProcessName) ;

    procedure ReleaseLockByHandle (cLockHandle in varchar2) is
    nReturnValue integer := null;
    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;

  5. 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.


  6. Thanks for you reply Jeff!


Note: Only a member of this blog may post a comment.