A m Z o n e

Preventing two routines from running simultaneously

How to prevent two routines from running simultaneously?
When one routine is being run by a user, the other routine should not be allowed to run and vice versa

We normally use flags or locks set in some table which is referred to by all sessions. This wouldn't work if session gets killed or closed abnormally. Below mentioned is a method which is provided in Oracle 8i. Afaik, this is the most applicable method.

Use *dbms_application_info*
Register the routine being run by calling dbms_application_info.set_module. This tags the routine name in V$SESSION. Check for this information at the beginning of the concerned routines. Once a routine is over, unregister it using the same above call. Note when a session is abnormally closed or killed, V$SESSION is updated accordingly. No need of commit or rollback.

 E.g.: AM_DEP1 and AM_DEP2 are inter-dependent procedures and should not run
 simultaneously.The following code will be put in both the routines.

           create or replace procedure am_dep1 is 
               l_cnt  pls_integer; 
           begin 
             begin 
               select 1 
               into   l_cnt 
               from   v$session 
               where  module = 'AM_DEP2' and rownum = 1;   --checking if am_dep2 is running.
               dbms_output.put_line('Application 2 is currently running, try after sometime.'); 
               return; 
             exception 
               when no_data_found then 
                 dbms_application_info.set_module('AM_DEP1','application 1'); --registering
           am_dep1. 
             end; 
             dbms_output.put_line('Application 2 is not running, executing application 1...'); 
             ... 
             dbms_application_info.set_module(null, null);  --unregistering am_dep1. 
           end; 
           / 

Press the Back button of you Browser to go to previous page
Home