ARTICLE FOR SETTING SPECIFIC SESSIONS AND RELEASING ETC

ARTICLE FOR SETTING SPECIFIC SESSIONS AND RELEASING ETC


---------------------------------------------------------------------------------------------------
Use Oracle's DBMS_APPLICATION_INFO to Prevent Routines from Running Simultaneously


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:

A critical accounting process is triggered by a user. It is necessary to avoid multiple runs of the same process at any given time. Therefore, if one user has triggered the process, others should be prevented from doing so.
Heavy data loading processes or data interfacing may require that two routines do not run simultaneously. When a heavy upload is triggered, simultaneous uploads from other sources should be prevented until resources are available for use.
Several heavy-duty batch processes exist in the system and proper resource sharing should be done. Running multiple batch processes may slow down the system and eventually result in resource contentions. Preventing such identified processes from running simultaneously is necessary.
Two or more processes carry out DML activities on common objects, which may result in locking issues. This could give rise to errors like 0RA-00054 (resource busy) or ORA-00060(deadlock detected).
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;
Conclusion

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.




---------------------------------------------------------------------------------------------------