How to set manual locks to synchronize processes in Oracle

Published: Wednesday, 18 November 2015 21:11 | Written by Stefan | Print | Email | Hits: 41718

lock database Customers often ask, how to make sure, that a particular PL/SQL-code which runs in several sessions at the same time, process the data for the FIFO paradigm. The answer is: no problem at all. Oracle offers the dbms_lock package which is very helpful there to solve problems arround locking and synchronizing.

The best way to handle it is, to each have a function for set and release a lock. Look at the following examples:

create or replace package body my_package is
[...]
  function lockSemaphore(pi_lock_name   in varchar2,
                         po_lock_result out number) return boolean is
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_lock_handle   varchar2(128);
    l_lock_duration number := 3600;  -- 1h
  begin
    -- try to get a lock handle  
    dbms_lock.allocate_unique(pi_lock_name, l_lock_handle);

    -- set a lock for the desired time
    po_lock_result := dbms_lock.request(l_lock_handle, 6, l_lock_duration); -- 6 = exclusive, wait for ever
  
    -- if the lock is new or the session has already set the same lock return true
    if (po_lock_result in (0, 4)) then
      return(true);
    else
      -- otherwise return false
      return(false);
    end if;
  end;

[...]
  ---
  --- releaseSemaphore
  ---
  function releaseSemaphore(pi_lock_name   in varchar2,
                            po_lock_result out number) return boolean is
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_lock_handle varchar2(128);
  begin
  
    -- first, get the lock-handle with the lock-name
    dbms_lock.allocate_unique(pi_lock_name, l_lock_handle);

    -- try to release the lock
    po_lock_result := dbms_lock.release(l_lock_handle);
  
    -- if release is ok, return true
    if (po_lock_result = 0) then
      return(true);
    else
      -- otherwise false
      return(false);
    end if;
  
  end;
[...]

Now you can use the lock-function to manualy set a lock. If more functions want to set the lock at the same time, the calls will be queued (waiting), until the function which is called prior releases the lock. Example:

[...]
declare
  l_lock_result number;
  l_lock_name   varchar2(20);
begin
  l_lock_name := 'my lock';

  if (my_package.lockSemaphore(l_lock_name, l_lock_result)) then
    dbms_output.put_line('Lock success: ' || l_lock_result);
  else
    dbms_output.put_line('Lock Error: ' || l_lock_result);
  end if;

[...] some processing for which it's necessary to run only once at one time


  if (my_package.releaseSemaphore(l_lock_name, l_lock_result)) then
    dbms_output.put_line('Release success: ' || l_lock_result);
  else
    dbms_output.put_line('Release Error: ' || l_lock_result);
  end if;

end;

[...]