Have only one session execute code (Critical section)

Sometimes when you’re writing code you can encounter the situation only one session / thread may execute it (the code) at a time. This is commonly referred as a “Critial Section“. How do you solve such a problem within the Oracle Database?

Turns out this is quite easy….. The Oracle Database provides you with the opportunity to create a custom (named) lock with which you can “shield” the section of code.

So how does that work? Below an example of a code block which contains a critical section which is shielded by a named lock.

DECLARE
  G_LOCKNAME    CONSTANT VARCHAR2(20) := 'MYLOCK';
  G_MAXWAIT     CONSTANT INTEGER      := 600;
  
  l_result      INTEGER;
  l_lock_handle VARCHAR(128);
BEGIN
  Dbms_Output.put_line('Acquiring lock...');
  
  Dbms_Lock.allocate_unique(
    lockname   => G_LOCKNAME
   ,lockhandle => l_lock_handle
  );

  l_result := Dbms_Lock.request(
        lockhandle        => l_lock_handle
       ,lockmode          => Dbms_Lock.x_mode
       ,timeout           => G_MAXWAIT
       ,release_on_commit => true
  );
  
  CASE l_result
    WHEN 0 THEN null; -- All ok
    WHEN 4 THEN null; -- We already have the lock
    WHEN 1 THEN raise_application_error('-20001', 'Timeout occured');
    WHEN 2 THEN raise_application_error('-20002', 'Deadlock');
    WHEN 3 THEN raise_application_error('-20003', 'Error in call');
    WHEN 5 THEN raise_application_error('-20005', 'Illegal handle');
  END CASE;
  
  -- Superimportant stuff we need to do alone
  Dbms_Lock.sleep(10);
  
  Dbms_Output.put_line('Releasing lock...');
  
  l_result := Dbms_Lock.release(l_lock_handle);
  
  CASE l_result
    WHEN 0 THEN null; -- All ok
    WHEN 3 THEN raise_application_error('-20013', 'Parameter error');
    WHEN 4 THEN raise_application_error('-20014', 'We did not own the lock');
    WHEN 5 THEN raise_application_error('-20015', 'Illegal handle');
  END CASE;
END;
/

As you can see it’s quite easy. At line 15 we try to acquire a lock we named MYLOCK. This name has been converted into a lock handle by the call at line 10. Dbms_Lock.request will wait for a maximum of 600 seconds to acquire the lock. (specified in G_MAXWAIT).

If we succeeded in acquiring the lock we then can perform the superimportant code for which we need to be “the only one executing it”.

Finally we release the lock so we do not bother other sessions which might want to acquire the lock as well. If you forget this and you have not specified the value true to the parameter release_on_commit the other sessions will not be able to acquire the lock (and eventually get a timeout). So you are able to cause some trouble by not releasing the lock. In the case of this example the lock will be automatically released upon a commit or rollback.

Note: it’s important to know that the allocate_unique function will perform a commit so if you’re using this method within a piece of code where you do not want a commit to occur you need to move the allocate_unique (and probably the acquire lock code as well) towards a procedure or function with the autonomous_transaction pragma.

Links

Dbms_Lock (search on Oracle site)