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.

Daydreaming

                Daydreaming is the act of mentally removing yourself from your present surroundings. While your body sits and lingers before a desk or typewriter, your mind wanders through the universe, transgressing the borders of time, space and reality.

                Daydreaming has historical precedence. Were it not for Julius Caesar's dreams of the conquest of foreign lands the Roman Empire would not have reached to the farthest corners of the Known World. Albert Einstein's theories began as a daydream of young Albert riding a beam of light through space. In fact, some of our civilization's greatest advances in engineering, medicine, and physics began with the conscious dream.

                Imagination is the key to creative dreaming. With even an average imagination, you can go anywhere, do anything – overcome anyone! Be courageous! Be daring! All from the relative safety of your armchair.

                There are good reasons for pursuing daydream activity. It allows you to escape the boredom and drudgery of a repetitive task for a few minutes, releasing the tension and stress that build up naturally. During a daydream, the pressures and worries of daily living are forgotten. Released from these distractions your mind pursues your work with renewed vigor.

                Your creative talent needs a daily workout. By flexing your mental muscles, your capacity to handle complex problems becomes greater. Your thinking becomes more resilient, continually seeking possible solutions, regardless of how bizarre many may seem. By giving flight to your fancy the improbable becomes possible. An intuitive leap may yield a new solution. These are the rewards for a few minutes spent daydreaming.

                The ability to daydream comes naturally. First you should get comfortable. Take off that stuffy jacket or sweater. Next, relax your body. Free your mind of the problems at hand. Tune out the world and tune into yourself, your feelings. Finally, let your imagination roam. Associate freely various thoughts and moods. Soon your mind will be exploring the deeper, subtle meanings behind the ordinary world we tend to take for granted.

                Picture yourself in your new, bright red '84 Corvette, screaming up the highway toward northern Maine, where the moose hunting is best. Perhaps you've wondered what parachuting into the Super Bowl during half-time would be like. Take that jump! Or, you may enjoy a leisurely space walk with your astronaut buddies. Your imagination knows no bounds.

                Now you know what daydreaming is all about. What are you waiting for?! Loosen that tie and kick off your shoes! Now, give your imagination a good workout!

Check for the Existence of a Particular Set of Column Names

How do I check for the existence of a particular set of column names amongst all tables?
It's straight-forward how to find one column name:

SELECT utc.table_name
FROM user_tab_columns utc
WHERE utc.column_name = 'FUND_ID'
ORDER BY utc.table_name;

TABLE_NAME
------------------------------
ANNUITY_ALLOCATION
ANNUITY_FEE
ASSIGNMENT
BENCHMARK_PROCESSING
BENCHMARK_WEIGHT
CATEGORY_LINK
COLLECTION_LINK
COMPOSITE_LINK
DAILY_ASSET
DAILY_PROFILE
DAILY_PROFILE_EXTRA
...

If I need to find two such column names, I suppose I could do a self-join:

SELECT t1.table_name, t1.column_name, t2.column_name
FROM   user_tab_columns t1
     INNER JOIN
       user_tab_columns t2
     ON t1.table_name = t2.table_name
WHERE t1.column_name = 'FUND_ID'
AND   t2.column_name = 'SECURITY_ID'
ORDER BY t1.table_name;

TABLE_NAME                     COLUMN_NAME          COLUMN_NAME
------------------------------ -------------------- --------------------
EXCEPTION_STORE                FUND_ID              SECURITY_ID
SECURITY                       FUND_ID              SECURITY_ID
V_DATA_EXCEPTION               FUND_ID              SECURITY_ID
V_HOLDING_RATE                 FUND_ID              SECURITY_ID
V_HOLDING_REPORT               FUND_ID              SECURITY_ID
V_TRANS_REPORT                 FUND_ID              SECURITY_ID

But how I do extend this to 3, 4 or 5 columns? Simply adding self-joins doesn't seem to scale particularly well.

What I really need is to obtain the set of all column names as a single column per table name, then somehow cross-reference those against a set of desired column names.

Fortunately, Oracle supports the concept of a multiset. This operator allows me to quickly perform set operations between two sets.

To do this, I first need a schema-level collection type:

CREATE OR REPLACE TYPE varchar2_nt IS TABLE OF VARCHAR2(4000);
/

4,000 bytes of course being the maximum allowable string size in SQL.

Now, how do I get the set of all column names for each table? Like this:

SELECT ut.table_name
      ,(SELECT CAST(
                 MULTISET(SELECT utc.column_name
                          FROM user_tab_columns utc
                          WHERE utc.table_name = ut.table_name) AS varchar2_nt
               )
        FROM DUAL)
         column_list
FROM user_tables ut
ORDER BY ut.table_name;

TABLE_NAME                     COLUMN_LIST
------------------------------ ----------------------------------------
ACCOUNT                        VARCHAR2_NT('IS_NONAGS', 'ACCOUNT_ID', '
                               EXTERNAL_ID', 'NAME', 'DESCRIPTION', 'AC
                               COUNT_CATEGORY', 'RECONCILIATION_RULE',
                               'PERIOD_BASED', 'FORCE_SHARE_CLASS')

ACCOUNT_GROUP                  VARCHAR2_NT('ACCOUNT_GROUP_ID', 'GROUP_N
                               AME')

ACCOUNT_GROUP_LINK             VARCHAR2_NT('ACCOUNT_GROUP_ID', 'ACCOUNT
                               _ID', 'SORT_ORDER')

The input to MULTISET in this situation is a query. If the query returns no rows, you get an empty collection. The subquery-in-a-column is called a scalar subquery. It must return precisely one value (or NULL). The collection of column names is that one value!

Now that I have a collection of column names per table, how can I scan it for one, two or more column names all at once?

I can perform the intersection of two such collections. The constructor for my schema-level type can be used to provide a hard-coded list, for example:

varchar2_nt('FUND_ID', 'SECURITY_ID', 'END_DATE')

Now I can do a MULTISET INTERSECT on this set and each column name set:

SELECT ut.table_name
      ,(SELECT CAST(
                 MULTISET(SELECT utc.column_name
                          FROM user_tab_columns utc
                          WHERE utc.table_name = ut.table_name) AS varchar2_nt
               )
        FROM DUAL)
         MULTISET INTERSECT varchar2_nt(
                              'FUND_ID'
                             ,'SECURITY_ID'
                             ,'END_DATE'
                            )
         column_list
FROM user_tables ut
ORDER BY ut.table_name;

TABLE_NAME                     COLUMN_LIST
------------------------------ ----------------------------------------
ACCOUNT                        VARCHAR2_NT()
ACCOUNT_GROUP                  VARCHAR2_NT()
ACCOUNT_GROUP_LINK             VARCHAR2_NT()
ACCOUNT_MAP                    VARCHAR2_NT()
ACCOUNT_SUMMARY                VARCHAR2_NT()
ACCOUNT_VECTORS                VARCHAR2_NT()
ACCOUNT_VIEW                   VARCHAR2_NT()
ALERT                          VARCHAR2_NT()
ALERT_USER                     VARCHAR2_NT()
ANNUITY_ALLOCATION             VARCHAR2_NT('FUND_ID')
ANNUITY_FEE                    VARCHAR2_NT('FUND_ID')

This unfortunately gives me an empty collection for those tables without any of these columns, and partial results for 1, 2, or 3 columns. I need some way to discern the fully populated collections from the rest.

For this, I can use the CARDINALITY function. I want only those tables that have all three columns:

SELECT *
FROM (SELECT ut.table_name
            ,(SELECT CARDINALITY(
                       CAST(
                         MULTISET(SELECT utc.column_name
                                  FROM user_tab_columns utc
                                  WHERE utc.table_name = ut.table_name) AS varchar2_nt
                       )
                         MULTISET INTERSECT varchar2_nt(
                                              'PORTFOLIO_ID'
                                             ,'SECURITY_ID'
                                             ,'END_DATE'
                                            )
                     )
              FROM DUAL)
               num_columns
      FROM user_tables ut) t
WHERE t.num_columns = 3
ORDER BY t.table_name;

TABLE_NAME                     NUM_COLUMNS
------------------------------ -----------
SECURITY_ENHANCEMENT                     3

If I were doing this in a PL/SQL block, I'd avoid the hard-coded constant by defining the collection once:

DECLARE
  arr_collist varchar2_nt
    := varchar2_nt(
         'PORTFOLIO_ID'
        ,'SECURITY_ID'
        ,'END_DATE'
       );
BEGIN
  FOR rec
    IN (SELECT *
        FROM (SELECT ut.table_name
                    ,(SELECT CARDINALITY(
                               CAST(
                                 MULTISET(
                                   SELECT utc.column_name
                                   FROM user_tab_columns utc
                                   WHERE utc.table_name = ut.table_name
                                 ) AS varchar2_nt
                               )
                                 MULTISET INTERSECT arr_collist
                             )
                      FROM DUAL)
                       num_columns
              FROM user_tables ut) t
        WHERE t.num_columns = CARDINALITY(arr_collist)
        ORDER BY t.table_name) LOOP
    DBMS_OUTPUT.put_line(rec.table_name);
  END LOOP;
END;
/
SECURITY_ENHANCEMENT

Now suppose I want to see which tables share the most number of common column names. I can take the intersection of the self-join of all column lists and compare each table's column list to one another; then sort by the number of common column names.

SELECT table_name1
      ,table_name2
      ,(column_list1 MULTISET INTERSECT column_list2) column_list3
      ,CARDINALITY(column_list1 MULTISET INTERSECT column_list2) card
FROM (SELECT ut1.table_name table_name1
            ,(SELECT CAST(
                       MULTISET(SELECT utc.column_name
                                FROM user_tab_columns utc
                                WHERE utc.table_name = ut1.table_name) AS varchar2_nt
                     )
              FROM DUAL)
               column_list1
            ,t2.table_name2
            ,t2.column_list2
      FROM   user_tables ut1
           INNER JOIN
             (SELECT ut2.table_name table_name2
                    ,(SELECT CAST(
                               MULTISET(
                                 SELECT utc.column_name
                                 FROM user_tab_columns utc
                                 WHERE utc.table_name = ut2.table_name
                               ) AS varchar2_nt
                             )
                      FROM DUAL)
                       column_list2
              FROM user_tables ut2) t2
           ON ut1.table_name != t2.table_name2)
WHERE CARDINALITY((column_list1 MULTISET INTERSECT column_list2)) > 0
ORDER BY card DESC;

TABLE_NAME1                    TABLE_NAME2
------------------------------ ------------------------------
COLUMN_LIST3
--------------------------------------------------------------------------------
      CARD
----------
MONTHLY_RETURN                 MONTHLY_RETURN_MATRIX
VARCHAR2_NT('FUND_ID', 'PROFILE_DATE', 'ROR_1M_C_NOLOAD', 'ROR_1M_G_NOLOAD', 'RO
R_2M_C_NOLOAD', 'ROR_3M_C_NOLOAD', 'ROR_4M_C_NOLOAD', 'ROR_5M_C_NOLOAD', 'ROR_6M
_C_NOLOAD', 'ROR_7M_C_NOLOAD', 'ROR_8M_C_NOLOAD', 'ROR_9M_C_NOLOAD', 'ROR_10M_C_
NOLOAD', 'ROR_11M_C_NOLOAD', 'ROR_YTD_C_NOLOAD', 'ROR_YTD_G_NOLOAD', 'ROR_1Y_C_N
OLOAD', 'ROR_2Y_A_NOLOAD', 'ROR_2Y_C_NOLOAD', 'ROR_3Y_A_NOLOAD', 'ROR_3Y_C_NOLOA
D', 'ROR_4Y_A_NOLOAD', 'ROR_4Y_C_NOLOAD', 'ROR_5Y_A_NOLOAD', 'ROR_5Y_C_NOLOAD',
'ROR_6Y_A_NOLOAD', 'ROR_7Y_A_NOLOAD', 'ROR_8Y_A_NOLOAD', 'ROR_9Y_A_NOLOAD', 'ROR
_10Y_A_NOLOAD', 'ROR_10Y_C_NOLOAD', 'ROR_15Y_C_NOLOAD', 'ROR_15Y_A_NOLOAD', 'ROR
_20Y_C_NOLOAD', 'ROR_20Y_A_NOLOAD', 'ROR_25Y_C_NOLOAD', 'ROR_25Y_A_NOLOAD', 'ROR
_INCP_C_NOLOAD', 'ROR_INCP_A_NOLOAD', 'ROR_ADV_INCP_C_NOLOAD', 'ROR_ADV_INCP_A_N
OLOAD', 'ROR_USER_INCP_C_NOLOAD', 'ROR_USER_INCP_A_NOLOAD', 'ROR_PERF_INCP_C_NOL
OAD', 'ROR_PERF_INCP_A_NOLOAD', 'ROR_1M_C_LOAD', 'ROR_2M_C_LOAD', 'ROR_3M_C_LOAD
', 'ROR_4M_C_LOAD', 'ROR_5M_C_LOAD', 'ROR_6M_C_LOAD', 'ROR_7M_C_LOAD', 'ROR_8M_C
_LOAD', 'ROR_9M_C_LOAD', 'ROR_10M_C_LOAD', 'ROR_11M_C_LOAD', 'ROR_YTD_C_LOAD', '
ROR_1Y_C_LOAD', 'ROR_2Y_A_LOAD', 'ROR_2Y_C_LOAD', 'ROR_3Y_A_LOAD', 'ROR_3Y_C_LOA
D', 'ROR_4Y_A_LOAD', 'ROR_4Y_C_LOAD', 'ROR_5Y_A_LOAD', 'ROR_5Y_C_LOAD', 'ROR_6Y_
A_LOAD', 'ROR_7Y_A_LOAD', 'ROR_8Y_A_LOAD', 'ROR_9Y_A_LOAD', 'ROR_10Y_A_LOAD', 'R
OR_10Y_C_LOAD', 'ROR_15Y_C_LOAD', 'ROR_15Y_A_LOAD', 'ROR_20Y_C_LOAD', 'ROR_20Y_A
_LOAD', 'ROR_25Y_C_LOAD', 'ROR_25Y_A_LOAD', 'ROR_INCP_C_LOAD', 'ROR_INCP_A_LOAD'
, 'ROR_ADV_INCP_C_LOAD', 'ROR_ADV_INCP_A_LOAD', 'ROR_USER_INCP_C_LOAD', 'ROR_USE
R_INCP_A_LOAD', 'ROR_PERF_INCP_C_LOAD', 'ROR_PERF_INCP_A_LOAD', 'RECORD_GUID', '
CREATORDB_ID', 'REVISION_NBR', 'GENERATION_ID', 'REFERENCE_COUNT', 'SYNC_SESSION
_ID')
        92

I do get the same pair of tables listed twice, first as table_name1-table_name2 and then as table_name2-table_name1.

I can also use the MULTISET and related operators and functions directly in assignment statements in PL/SQL. For example:

DECLARE
  x varchar2_nt := varchar2_nt();
BEGIN
  x :=
    varchar2_nt(
      'a'
     ,'b'
     ,'b'
     ,'c'
    )
      MULTISET INTERSECT DISTINCT varchar2_nt(
                                    'b'
                                   ,'c'
                                   ,'c'
                                   ,'d'
                                  );

  IF (x.COUNT > 0) THEN
    DBMS_OUTPUT.put_line('There are ' || x.COUNT || ' common elements:');

    FOR i IN x.FIRST .. x.LAST LOOP
      DBMS_OUTPUT.put_line(x(i));
    END LOOP;
  ELSE
    DBMS_OUTPUT.put_line('There are no common elements');
  END IF;
END;
/

There are 2 common elements:

b
c

If you play around with this, you'll see the intersection of a non-empty set and an empty set is an empty set.

In addition to INTERSECT [DISTINCT], there's UNION [DISTINCT] and EXCEPT [DISTINCT].