Oracle User Locking

Posted by admin on Jul 4, 2011 in Uncategorized |

The DBMS_LOCK package can be used to lock sections of code for concurrency control. Simple usage is as follows;

DECLARE
      lock_handle VARCHAR2(128);
      lock_status number;
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE (lockname => 'mylockset', lockhandle => lock_handle);
    -- If this is taking a long time to run check if a lock is being held and kill the session
    -- e.g. select osuser,program,sys.v_$lock.type,lmode,request
    --         from sys.v_$lock,v$session
    --         where v$session.sid = sys.v_$lock.sid
    --         and sys.v_$lock.type = 'UL'

lock_status := DBMS_LOCK.REQUEST(lockhandle => lock_handle, lockmode => DBMS_LOCK.X_MODE, timeout => 60, release_on_commit => FALSE);

--- Some protected code ---

lock_status := DBMS_LOCK.RELEASE(lockhandle => lock_handle);

Leave a Reply

XHTML: You can use these tags:' <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Copyright © 2008-2017 Brinsmead Data Services All rights reserved.