Locking a Section of Code

Sometimes, it’s necessary to guarantee exclusive access to a section of code (sometimes referred to as a “region”), typically for the duration of a procedure or function call. This is often necessary to prevent a race condition from occurring between two or more sessions. For example, a procedure might need to query a table before modifying some of its rows. Because the query and subsequent DML statements occur in series, it’s conceivable that another session might come along in between and change the state of the row before the first session completes its mission. So to prevent such a situation from arising, we should lock the section of code that executes these two statements.

In PL/SQL, we can use the Oracle-supplied package DBMS_LOCK to provide this mechanism. When the section of code is locked, only one session is guaranteed to be in that code section; all other sessions are queued up. When the session holding the lock performs a COMMIT or ROLLBACK, the lock is released, and the next session in the queue gains access.

Examining the Oracle documentation for DBMS_LOCK.request()

We see there are some important things to take note.

First, we need to obtain a lock handle via DBMS_LOCK.allocate_unique. My personal preference is to provide a lock name that’s the same as the routine. It needs to be unique anyway and it’s a handy way to associate the lock with the routine.

 c_lock_name CONSTANT VARCHAR2(30) := 'addorget_cust';

In all of my stored subprograms that do locking, you’ll always find the same variable or constant name used. Consistency makes it that much easier to maintain these programs.

Now we just need to get a lock handle and store it locally. It’ll be used when making the lock request.

 v_lock_handle VARCHAR2(128);
…
  DBMS_LOCK.allocate_unique(c_lock_name, v_lock_handle);

Now we’re about ready to request a lock; but first we have some important design decisions to make. Here is the signature for the packaged function DBMS_LOCK.request:

DBMS_LOCK.REQUEST(
   id                 IN  INTEGER ||
   lockhandle         IN  VARCHAR2,
   lockmode           IN  INTEGER DEFAULT X_MODE,
   timeout            IN  INTEGER DEFAULT MAXWAIT,
   release_on_commit  IN  BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;

We do wish to obtain an exclusive lock. But it’s rather a bad idea to “wait forever” (the package constant maxwait waits forever) on a lock request. Frankly, if we can’t get a lock within just a few seconds, we’ve got bigger design problems. The whole point to a code section lock is to get in and get out of it as fast as possible. Locks produce waits, and the last thing we wish to do is slow down the entire system. So if we don’t get the lock pretty quickly, we’ll need a way to go to the end of the queue and retry – up to a point (again, not wishing to find ourselves in a long-running loop). The “contract” all of these stored procedures share in common is they will release the lock as soon as possible, by performing a COMMIT or ROLLBACK.

So I’m going to need some more variables to track what’s going on with my lock request:

   -- locking parameters
    c_timeout CONSTANT INTEGER := 2; -- seconds
    c_max_loop_counter CONSTANT PLS_INTEGER := 5; -- sanity check
    v_req_ret INTEGER := -1; -- initialized to meaningless non-zero/null value
    v_loop_counter PLS_INTEGER := 0; -- # of retries
    v_count INTEGER := 0;

If my request fails, I’ll retry (in a loop), up to a point. After that, I’m going to bail (and in all of the production code I’ve ever used this in, I’ve never stressed it enough to require more than a single retry – but that’s the way to code it).

Now I’m ready to make my lock request:

     v_req_ret :=
        DBMS_LOCK.request(v_lock_handle
                         ,timeout => c_timeout
                         ,release_on_commit => TRUE
                         );

Note I start off using Positional Notation, and quickly switch to Named Notation so I can skip the lockmode parameter (accepting its default value).

The documentation describes the request’s return value:

0	Success
1	Timeout
2	Deadlock
3	Parameter error
4	Don't own lock specified by id or lockhandle
5	Illegal lock handle

I will have to deal with each scenario:

     CASE v_req_ret
        WHEN 0
        THEN
          NULL; -- success!
        WHEN 1
        THEN -- timeout; retry up to max times
          v_loop_counter := v_loop_counter + 1;
        WHEN 2
        THEN -- deadlock
          ROLLBACK;
          v_loop_counter := v_loop_counter + 1;
        WHEN 3
        THEN -- parameter error; bail
          raise_application_error(-20002, 'Lock failed: parameter error.');
        WHEN 4
        THEN -- already own lock specified by id or lockhandle
          v_req_ret := 0; -- success from a previous run (should never happen)
        WHEN 5
        THEN -- illegal lock handle (allocate failed)
          raise_application_error(-20002
                                 ,'Lock failed: illegal lock handle.'
                                 );
        ELSE
          raise_application_error(
            -20002
           ,'Lock failed: unknown error [' || v_req_ret || '].'
          );
      END CASE;

I’ll do this in a loop until the request return code is zero (meaning success), up to c_max_loop_counter times.

Now quite frankly, the last thing I should do is have code like this repeated in a number of like-minded routines. The code is intended to be tightly integrated. Therefore, it should be in its own stored procedure. Then my routines that need locking can do so with a single procedure call, so long as they all adhere to the defined contract – get the lock, do your thing, then release it as soon as you can. Here is my completed stored (actually packaged) procedure, obtain_lock:

 -- obtain a lock for this region of code.
  -- release the lock with a commit or rollback.
  PROCEDURE obtain_lock(p_lockname IN VARCHAR2)
  IS
    -- locking parameters
    c_timeout CONSTANT INTEGER := 2; -- seconds
    c_max_loop_counter CONSTANT PLS_INTEGER := 5; -- sanity check
    v_lock_handle VARCHAR2(128);
    v_req_ret INTEGER := -1; -- initialized to meaningless non-zero/null value
    v_loop_counter PLS_INTEGER := 0; -- # of retries
    v_count INTEGER := 0;
  BEGIN
    -- lock section of code
    DBMS_LOCK.allocate_unique(p_lockname, v_lock_handle);

    WHILE (v_req_ret != 0) LOOP
      v_req_ret :=
        DBMS_LOCK.request(v_lock_handle
                         ,timeout => c_timeout
                         ,release_on_commit => TRUE
                         );

      CASE v_req_ret
        WHEN 0
        THEN
          NULL; -- success!
        WHEN 1
        THEN -- timeout; retry up to max times
          v_loop_counter := v_loop_counter + 1;
        WHEN 2
        THEN -- deadlock
          ROLLBACK;
          v_loop_counter := v_loop_counter + 1;
        WHEN 3
        THEN -- parameter error; bail
          raise_application_error(-20002, 'Lock failed: parameter error.');
        WHEN 4
        THEN -- already own lock specified by id or lockhandle
          v_req_ret := 0; -- success from a previous run (should never happen)
        WHEN 5
        THEN -- illegal lock handle (allocate failed)
          raise_application_error(-20002
                                 ,'Lock failed: illegal lock handle.'
                                 );
        ELSE
          raise_application_error(
            -20002
           ,'Lock failed: unknown error [' || v_req_ret || '].'
          );
      END CASE;

      IF (v_loop_counter > 0)
      THEN -- need to retry acquiring lock
        put_long_line(   'Lock not acquired with message #'
                      || v_req_ret
                      || '. This is retry #'
                      || v_loop_counter);
        v_req_ret := -1; -- reset

        IF (v_loop_counter = c_max_loop_counter)
        THEN
          raise_application_error(
            -20001
           ,'Failed ' || c_max_loop_counter || ' retries to get lock.'
          );
        END IF;
      END IF;
    END LOOP;
  -- got the lock; this is the protected code region
  END obtain_lock;

This really simplifies the use of this feature. For example:

-- given the customer name,
-- looks up the customer ID and returns it.
-- if none is found, it creates one.
CREATE OR REPLACE PROCEDURE addorget_cust(
  p_cust_name IN customer.cust_name%TYPE
 ,p_cust_id   OUT customer.cust_id%TYPE
)
IS
  c_lock_name CONSTANT VARCHAR2(30) := 'addorget_cust';
BEGIN
  obtain_lock(c_lock_name);

  BEGIN
    SELECT c.cust_id
    INTO p_cust_id
    FROM customer c
    WHERE c.cust_name = p_cust_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN -- OK to add
      INSERT INTO customer(cust_name)
      VALUES (p_cust_name)
      RETURNING cust_id
      INTO p_cust_id;
  END;

  COMMIT;  -- release the lock
END addorget_cust;
/

Short and sweet.