More on using Application Locks to serialize runtime code
In lieu of my old article (link below), I have provided here a more detailed sample code to using DBMS_APPLICATION_INFO for creating application locks using oracle dictionary. For those who use DBMS_APPLICATION_INFO for a different purpose can check the 2nd part of this note where I have discussed usage of a custom locking table to achieve the same.
Link to the original article that provides elementary information
Using DBMS_APPLICATION_INFO
Below is a package that basically has 3 programs for acquiring a lock for runtime process that should run serially. A process to be run serially will make a call to this application lock program to ensure that no other session is running an instance of it already and then make a decision to continue the processing or not. This example will also prevent parallel processing for serialized code, as shown in examples .The application lock program makes use of DBMS_APPLICATION_INFO to establish a programming lock. It has 3 procedures for establishing this mechanism
REGISTER_CALL - Successful registering gives the calling program exclusive right to continue processing.
VERIFY - A verification needs to be performed to ensure that other session have not registered during the same time frame.
RELEASE_CALL - Clear the dictionary update for application lock after the required processing has been done.
- Call AM_APP_LOCK.REGISTER_CALL to verify and reserve an application lock for your session. This requires that the current program name and a key be passed. To avoid multiple runs, key can be same as program.
If the requirement is to allow multiple runs of the same program but avoid the same transaction processing, pass a value that uniquely identifies the transaction. For instance, invoice no, Delivery Order No. or JV no. that is to be processed.
If another session is already running with the given attribute, this call will give error. If not, this call will register your session for exclusive lock and all other session making the same request will be rejected.
Note this call will return success only after V$SESSION has been updated with required information for tracking this mechanism.
- Next, call AM_APP_LOCK.VERIFY to re-check that no two sessions have registered for the same job. This could happen when parallel processing is carried out and V$SESSION is updated in-parallel for more than one session. In case of any conflict, this call will prevent all sessions from running and avoid any parallel processing request.
- Call AM_APP_LOCK.RELEASE_CALL at end of the program, after the required job is completed.
create or replace package am_app_lock is
procedure register_call(pi_proc_name in varchar2, pi_proc_key in varchar2,
pio_status in out nocopy varchar2);
procedure verify(pi_proc_name in varchar2, pi_proc_key in varchar2,
pio_status in out nocopy varchar2);
procedure release_call;
end am_app_lock;
/
create or replace package body am_app_lock is
procedure register_call(pi_proc_name in varchar2, pi_proc_key in varchar2,
pio_status in out nocopy varchar2) is
l_prgnam varchar2(60) := ' [am_app_lock.register_call].';
l_client_info v$session.client_info%type;
l_module v$session.module%type;
l_action v$session.action%type;
l_chk pls_integer;
errexc exception;
begin
pio_status := 'OK';
begin
select 1
into l_chk
from v$session
where module = pi_proc_name
and action = pi_proc_key
and rownum = 1;
pio_status := 'Error: Process already running' || l_prgnam;
raise errexc;
exception
when no_data_found then
dbms_application_info.set_module(module_name => pi_proc_name,
action_name => pi_proc_key);
end;
verify(pi_proc_name, pi_proc_key, pio_status);
if pio_status != 'OK' then
raise errexc;
end if;
exception
when errexc then
null;
when others then
pio_status := 'Error: ' || substr(sqlerrm, 1, 120) || l_prgnam;
end register_call;
procedure verify(pi_proc_name in varchar2, pi_proc_key in varchar2,
pio_status in out nocopy varchar2) is
l_prgnam varchar2(60) := ' [am_app_lock.verify].';
l_sid v$session.sid%type;
l_mysid v$session.sid%type;
errexc exception;
begin
pio_status := 'OK';
dbms_lock.sleep(2);
select sid
into l_sid
from v$session
where module = pi_proc_name
and action = pi_proc_key;
select sid
into l_mysid
from v$mystat
where rownum = 1;
if l_sid != l_mysid then
pio_status := 'Error: Lock is already acquired by another session'
|| l_prgnam;
raise errexc;
end if;
exception
when errexc then
null;
when others then
release_call;
pio_status := 'Error: ' || substr(sqlerrm, 1, 120) || l_prgnam;
end verify;
procedure release_call is
begin
dbms_application_info.set_module(module_name => null,
action_name => null);
end release_call;
end am_app_lock;
/
Testing the routine
I will now try to test the above routine in parallel processing situations. I need a table to track the below example.create table am_lock_chk (prgnam varchar2(60), start_time timestamp, end_time timestamp, status varchar2(240));Now I need a program that will call the above application lock routine to run in serial mode. Basically, this program will need to identify if other sessions running the same program and then abort or continue accordingly. Only one instance of the program should run.
create or replace procedure am_run_chk is
l_start_time timestamp := systimestamp;
l_status varchar2(240);
errexc exception;
procedure insert_lock_chk(pi_status in varchar2) is
begin
insert into am_lock_chk(prgnam, start_time, end_time, status)
values('am_run_chk', l_start_time,
systimestamp, l_status);
commit;
end;
begin
am_app_lock.register_call('am_run_chk', 'am_run_chk', l_status);
if l_status != 'OK' then
insert_lock_chk(l_status);
raise errexc;
end if;
am_app_lock.verify('am_run_chk', 'am_run_chk', l_status);
if l_status != 'OK' then
insert_lock_chk(l_status);
raise errexc;
end if;
-- Place the exclusive code here..
dbms_lock.sleep(3);
-- call completed.
insert_lock_chk('OK!!');
am_app_lock.release_call;
exception
when errexc then
null;
end;
/
Case - 1 (Two jobs in parallel)
The above routine "am_run_chk" is run in parallel using dbms_job. I get the below data on completion in the temporary lock table.PRGNAM |START_TIME |END_TIME |STATUS ----------|------------------------------|------------------------------|------------------------------------------------------------ am_run_chk|26-MAR-09 03.02.28.919065 PM |26-MAR-09 03.02.28.921379 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 03.02.28.896756 PM |26-MAR-09 03.02.35.930779 PM |OKNotice, oracle has still run the job on different timestamp. This case is enough to show that the process works to prevent multiple session from running the same job.
Case - 2 (multiple jobs in parallel to simulate more than one registration)
The above case was simple, now I will submit multiple jobs and try to simulate multiple registration at the same time. The provided application lock code will allow multiple sessions to register at the same time as this can happen in parallel, but then this will get trapped in verify process as more than one session would have been issued the lock.I submitted the same case with about 20 jobs.
SQL> show parameter job_queue NAME |TYPE |VALUE ------------------------------------|-----------|------------------------------ job_queue_processes |integer |30 exec dbms_job.isubmit(job => 2001, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2002, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2003, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2004, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2005, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2006, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2007, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2008, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2009, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2010, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2011, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2012, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2013, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2014, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2015, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2016, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2017, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2018, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2019, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60)); exec dbms_job.isubmit(job => 2020, what => 'am_run_chk;', next_date => trunc(sysdate) + 16/24 + 20/(24 * 60));
Below records get inserted in the locking table now. Result no jobs got the lock to go through. Excellent, I have a case now.
SQL> select * from am_lock_chk; PRGNAM |START_TIME |END_TIME |STATUS ----------|------------------------------|------------------------------|------------------------------------------------------------ am_run_chk|26-MAR-09 04.20.03.330054 PM |26-MAR-09 04.20.03.335459 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.334919 PM |26-MAR-09 04.20.03.336016 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.378234 PM |26-MAR-09 04.20.03.379159 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.378441 PM |26-MAR-09 04.20.03.379333 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.394411 PM |26-MAR-09 04.20.03.395961 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.407534 PM |26-MAR-09 04.20.03.408445 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.408512 PM |26-MAR-09 04.20.03.409623 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.425933 PM |26-MAR-09 04.20.03.426753 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.437395 PM |26-MAR-09 04.20.03.438361 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.438336 PM |26-MAR-09 04.20.03.439272 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.449228 PM |26-MAR-09 04.20.03.450185 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.452849 PM |26-MAR-09 04.20.03.454405 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.461810 PM |26-MAR-09 04.20.03.462663 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.474410 PM |26-MAR-09 04.20.03.475269 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.479464 PM |26-MAR-09 04.20.03.480610 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.491665 PM |26-MAR-09 04.20.03.492578 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.492731 PM |26-MAR-09 04.20.03.493677 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.511073 PM |26-MAR-09 04.20.03.512603 PM |Error: Process already running [am_app_lock.register_call]. am_run_chk|26-MAR-09 04.20.03.328836 PM |26-MAR-09 04.20.05.341521 PM |Error: ORA-01422: exact fetch returns more than requested nu | | |mber of rows [am_app_lock.verify]. am_run_chk|26-MAR-09 04.20.03.328071 PM |26-MAR-09 04.20.05.342035 PM |Error: ORA-01422: exact fetch returns more than requested nu | | |mber of rows [am_app_lock.verify]. 20 rows selected.The last two record's session shown above were the first to fire and then both interlocked themselves. Both jobs were able to register themselves using the am_app_lock.register_call function, but both got rejected in am_app_lock.verify as only one session could exists with the lock. Remaining 18 jobs fired and were rejected in registration process itself because the lock was already acquired. The logic has thus prevented parallel processing, which should anyway not be done if you want to ensure serial usage or call.
Using a custom locking Table for application locks
I had to make use of a custom locking table having unique index to create application locks. The above logic could not be used in this particular case as DBMS_APPLICATION_INFO was already in use for tracking and auditing modules in the system. Here is the 2nd method of doing this. TBD.... Best viewed in medium text size. Please refresh (F5) to view the latest information.This page was created on 26-Mar-09. Last updated on 26-Mar-09.
please forward all queries to amar@amar-padhi.com