Use Oracle's DBMS_APPLICATION_INFO to Prevent Routines from Running Simultaneously
March 10, 2004
On occasion, it may be necessary to implement business logic where, when one routine or business process is being run by a user, certain other routine(s) should not be allowed to run and vice versa. This requirement may be a functional or a technical need.
You may be able to relate to the following scenarios:
One solution would be to request that users avoid running identified processes simultaneously; however, this is a manual control that can be easily overlooked, requiring the DBA to kill the bad sessions to avoid database congestion.
Using code, developers normally use flags or locks set in some table that is referred to by all sessions. This will not work if the session is killed or closed abnormally. Explicit coding needs to be done in such cases to make the system foolproof; this could again amount to an overhead.
The Oracle provided package, DBMS_APPLICATION_INFO, could be used to satisfy this requirement, thus preventing the abnormal situations mentioned above from arising.
Register the routine that is being run by calling DBMS_APPLICATION_INFO.SET_MODULE. This tags the routine name in V$SESSION dynamic view. Check for this information at the beginning of the concerned routines. Once a routine is complete, unregister it. Note when a session is abnormally closed or killed, V$SESSION is updated accordingly. There is no need of commit or rollback or explicit updates.
Three columns, MODULE, ACTION and CLIENT_INFO can be set in V$SESSION using DBMS_APPLICATION_INFO which could later be referred to, to identify processes already running in the system. The following example sets these three columns.
begin dbms_application_info.set_module(module_name => 'File Upload Process', action_name => 'Uploading file'); dbms_application_info.set_client_info('WDA001'); end;
On running the above code, the three columns are set with the value provided, and can be viewed from V$SESSION.
SQL> select module, action, client_info 2 from v$session 3 where sid = (select sid from v$mystat where rownum = 1); MODULE ACTION CLIENT_INFO -------------------- -------------------- -------------------- File Upload Process Uploading file WDA001
If direct access to the dictionary is not available, the set data can be viewed using the same package.
declare l_client_info varchar2(64); l_module varchar2(48); l_action varchar2(32); begin dbms_application_info.read_module(l_module, l_action); dbms_application_info.read_client_info(l_client_info); dbms_output.put_line(l_module||'/'||l_action||'/'||l_client_info); end; Output: File Upload Process/Uploading file/WDA001
The data returned in the above case is only for the current session. For our situation it is required that access be given to V$SESSION to view all sessions data. Select privilege can be granted to the public so that all logins have access to it.
SQL> grant select on v_$session to public; Grant succeeded.
Let's look at an example. This is a very specific example but generic code could be written along similar lines. AM_DEP1 and AM_DEP2 are inter-dependent procedures and should not run simultaneously. The following function checks to see if a particular procedure session is active and returns the status accordingly.
create or replace function chk_dep_run (pi_proc in varchar2) return boolean is l_cnt pls_integer; begin select 1 into l_cnt from v$session where module = pi_proc and rownum = 1; return(false); exception when no_data_found then return(true); end;
AM_DEP1 and AM_DEP2 will call the above function in the beginning. Once the function confirms that the other procedure is not active, the current routine will be registered and executed. Registering will tag the necessary information in V$SESSION view.
create or replace procedure am_dep1 is .. begin if not chk_dep_run('AM_DEP2') then dbms_output.put_line('AM_DEP2 is currently running'); return; end if; dbms_application_info.set_module('AM_DEP1', 'running apps1'); --register -- continue executing the code. .. dbms_application_info.set_module(null, null); --unregister end; create or replace procedure am_dep2 is .. begin if not chk_dep_run('AM_DEP1') then dbms_output.put_line('AM_DEP1 is currently running'); return; end if; dbms_application_info.set_module('AM_DEP2', 'running apps2'); --register -- continue executing the code. .. dbms_application_info.set_module(null, null); --unregister end;
Use of DBMS_APPLICATION_INFO depends a lot on the coding. Flexibility is provided to put any type of character string in the dictionary table columns and then refer to it from the application. Proper coding standards should be implemented for using this feature as per the application requirement. Call the package at the beginning of all application sessions to set the appropriate information. The package may be called multiple times to update the dictionary during the lifetime of the session.