René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Synchronizing sessions in Oracle with DBMS_LOCK

Let's say, we want two sessions to do a task concurrently, that is, both tasks should start at the same time.
This is ideally solved with dbms_lock.
The following table will be filled by these two tasks: 
create table lock_test (
  action varchar(10),
  when   date
);
Three sessions are needed for this experiment.
In the first session, an exclusive lock (indicated by the x_mode flag) is requested: 
declare
  v_lockhandle varchar2(200);
  v_result     number;
begin

  dbms_lock.allocate_unique('control_lock', v_lockhandle);

  v_result := dbms_lock.request(v_lockhandle, dbms_lock.x_mode);

  if v_result <> 0 then
    dbms_output.put_line(
           case 
              when v_result=1 then 'Timeout'
              when v_result=2 then 'Deadlock'
              when v_result=3 then 'Parameter Error'
              when v_result=4 then 'Already owned'
              when v_result=5 then 'Illegal Lock Handle'
            end);
  end if;

end;
/
The following block is identical for the two other sessions. Similarly to the first session, a lock is requested, however, this time, the lock is a shared lock (indicated by the ss_mode flag).
According to the lock compatibility chart, a shared lock cannot be aquired if another session holds an exclusive lock. So, if a session executes the following block, it will stop execution on the request() call until the session holding the exclusive lock releases that lock.
Ok, the following block is started within two different sessions: 
declare
  v_result     number;
  v_lockhandle varchar2(200);
begin

  dbms_lock.allocate_unique('control_lock', v_lockhandle);

  v_result := dbms_lock.request(v_lockhandle, dbms_lock.ss_mode);

  if v_result <> 0 then 
    dbms_output.put_line(
           case 
              when v_result=1 then 'Timeout'
              when v_result=2 then 'Deadlock'
              when v_result=3 then 'Parameter Error'
              when v_result=4 then 'Already owned'
              when v_result=5 then 'Illegal Lock Handle'
            end);
  end if;

  insert into lock_test values ('started', sysdate);
  dbms_lock.sleep(5);
  insert into lock_test values ('ended'  , sysdate);

  commit;

end;
/
Now, the first session can release the exclusive lock: 
declare
  v_lockhandle varchar2(200);
  v_result     number;
begin

  dbms_lock.allocate_unique('control_lock', v_lockhandle);

  v_result := dbms_lock.release(v_lockhandle);

  if v_result <> 0 then 
    dbms_output.put_line(
           case 
              when v_result=1 then 'Timeout'
              when v_result=2 then 'Deadlock'
              when v_result=3 then 'Parameter Error'
              when v_result=4 then 'Already owned'
              when v_result=5 then 'Illegal Lock Handle'
            end);
  end if;

end;
/
As soon as the exclusive lock is released, both sessions that waited on aquiring the shared lock can resume their execution. This time, according to the lock compatibility chart, it is possible for two (or more) sessions to hold a shared lock. This, after all, is why it is called shared.
The two sessions will return after five seconds. Let's see what they've written into lock_test: 
select to_char(when,'dd.mm.yyyy hh24:mi:ss'), action from lock_test order by when;

TO_CHAR(WHEN,'DD.MM ACTION
------------------- ----------
30.10.2005 12:16:13 started
30.10.2005 12:16:13 started
30.10.2005 12:16:18 ended
30.10.2005 12:16:18 ended
Both sessions started at the same time!

Links

An example of how synchronizing sessions might be used is found in this article: Why is dynamic SQL bad.