Create a “barrier” (prevent parallel execution) in Oracle PL/SQL

lockingoracle

I'm maintaining some stored procedures in an Oracle 11g RAC environment that are part of the pipeline of steps for data-interchange with other systems. One in particular has been producing duplicate records. Files get loaded into a set of staging-tables by an ETL job, which then calls this stored procedure, which has no parameters (except an OUT parameter that gets ignored by the calling ETL code, but that's another issue). The body of the procedure starts with a statement like

For I in ( SELECT * from  SOME_STAGING_TABLE where status = 'NEW' order by X_ID) LOOP

However, the ETL job that calls this is in turn called by another ETL job that finds all "files" that haven't been processed and calls it, once per file, in parallel. I think what's happening is:

  1. Master ETL job runs, finds two files, invokes two worker jobs
  2. Worker ETL job A runs, gets through first phase
  3. Worker ETL job B runs, gets through first phase
  4. Worker ETL job A invokes stored procedure
  5. Stored procedure A' gets two files' worth of records to process
  6. Worker ETL job B invokes stored procedure
  7. Stored procedure B' gets two files' worth of records to process

One solution might be to enforce serialization in the ETL service. Another might be to change the calling-convention of the stored procedure so it takes a file ID as a parmeter. However, let's say I need to do it in the database. Is there any way to set up a stored procedure so that only one connection can be executing a designated block of code inside it at a time?

(In C or Java this would be called a "mutex" or "synchronized block", and I know MySQL has a "LOCK TABLES" statement that could be used for that purpose; does Oracle 11g have anything like that?)

Best Answer

Oracle has several locking mechanisms that you can use to serialize operations:

  • You could create a single-row table that is locked at the beginning of the procedure. The lock will be locked until the transaction ends (commit/rollback).
  • You could request a lock with DBMS_LOCK.request. This lock can be maintained for the duration of the session or until you call DBMS_LOCK.release. This lock can be maintained across commits.

Example of serialization using DBMS_LOCK:

SQL> CREATE OR REPLACE PROCEDURE serial IS
  2     l_lock_handle  VARCHAR2(128 BYTE);
  3     l_lock_request INTEGER;
  4  BEGIN
  5     dbms_lock.allocate_unique(lockname=> 'MY_SERIAL_PROC', lockhandle => l_lock_handle);
  6     LOOP
  7        l_lock_request := dbms_lock.request(lockhandle        => l_lock_handle,
  8                                            timeout           => 5,
  9                                            release_on_commit => FALSE);
 10        CASE l_lock_request
 11           WHEN 0 THEN
 12              EXIT; -- success
 13           WHEN 1 THEN
 14              dbms_output.put_line('lock already reserved, wait...');
 15              dbms_lock.sleep(5); -- sleep 5 seconds before retrying
 16           ELSE
 17              raise_application_error(-20001, 'Lock error: ' || l_lock_request);
 18        END CASE; --
 19     END LOOP;
 20     ----------------------------------------
 21     -- serialized block of code           --
 22     -- (lock will be kept accross commit) --
 23     ----------------------------------------
 24     dbms_lock.sleep(30);
 25     ----------------------------------------
 26     -- End of serialized code             --
 27     ----------------------------------------
 28     l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 29  END;
 30  /

Procedure created

Only one session can run this procedure at the same time:

session1> exec serial;             
                                   session2> exec serial;
                                   lock already reserved, wait...
                                   lock already reserved, wait...
                                   lock already reserved, wait...

PL/SQL procedure successfully completed                            

                                   PL/SQL procedure successfully completed.
Related Question