How to prevent an procedure from running when parameters are same and allow when parameters are different. let me explain the exact problem
creating build up script
drop table datalock_test;
create table datalock_test
(year_ number) tablespace wad;
drop TYPE test_lock_type_arr;
drop TYPE test_lock_type;
create or replace TYPE test_lock_type AS OBJECT
(
year_ number
);
/
create or replace TYPE test_lock_type_arr IS TABLE OF test_lock_type
;
/
drop procedure insert_to_lock_test;
create or replace procedure insert_to_lock_test (p_year number)
as
l_arr test_lock_type_arr := test_lock_type_arr();
begin
select test_lock_type(year_) bulk collect into l_arr from (select p_year as year_ from dual) a
where not exists (select NULL from datalock_test b
where a.year_ = b.year_);
dbms_lock.sleep(15);
forall i IN l_arr.first .. l_arr.last SAVE EXCEPTIONS
insert into datalock_test
values
(
l_arr(i).year_
);
commit;
end;
/
truncate table datalock_test;
Normal Testing:-
Now if I run below code one record will be inserted
begin
insert_to_lock_test(p_year => 1999);
end;
/
Now If I rerun the above code again then NO record will be inserted.
Hard Testing:-
But If I run below then duplicate records will be inserted, which is not desirable.
begin
dbms_scheduler.create_job (
job_name => 'load1',
job_type => 'plsql_block',
job_action => 'begin
insert_to_lock_test(p_year => 2000);
end;',
enabled => true);
dbms_scheduler.create_job (
job_name => 'load2',
job_type => 'plsql_block',
job_action => 'begin
insert_to_lock_test(p_year => 2000);
end;',
enabled => true);
end;
/
This duplicate is what I have to avoid.
Constraints :-
What I have Tried :-
Recreate Procedure as below
create or replace procedure insert_to_lock_test (p_year number)
as
l_arr test_lock_type_arr := test_lock_type_arr();
lv_lockhandle VARCHAR2(500);
lv_ret_code PLS_INTEGER;
lv_retcode NUMBER;
p_nm varchar2(200) := 'testlock';
begin
dbms_lock.allocate_unique(p_nm, lv_lockhandle);
lv_retcode := dbms_lock.request(lockhandle=>lv_lockhandle,
lockmode => dbms_lock.x_mode);
select test_lock_type(year_) bulk collect into l_arr from (select p_year as year_ from dual) a
where not exists (select NULL from datalock_test b
where a.year_ = b.year_);
dbms_lock.sleep(15);
forall i IN l_arr.first .. l_arr.last SAVE EXCEPTIONS
insert into datalock_test
values
(
l_arr(i).year_
);
commit;
lv_ret_code := dbms_lock.release(lv_lockhandle);
end;
/
Post this lets run the code in parallel with same parameter
begin
dbms_scheduler.create_job (
job_name => 'load1',
job_type => 'plsql_block',
job_action => 'begin
insert_to_lock_test(p_year => 2000);
end;',
enabled => true);
dbms_scheduler.create_job (
job_name => 'load2',
job_type => 'plsql_block',
job_action => 'begin
insert_to_lock_test(p_year => 2000);
end;',
enabled => true);
end;
/
No duplicates were inserted, only one record, which is desirable. But now issue is when we run below code
begin
dbms_scheduler.create_job (
job_name => 'load1',
job_type => 'plsql_block',
job_action => 'begin
insert_to_lock_test(p_year => 2020);
end;',
enabled => true);
dbms_scheduler.create_job (
job_name => 'load2',
job_type => 'plsql_block',
job_action => 'begin
insert_to_lock_test(p_year => 2021);
end;',
enabled => true);
end;
/
Here for insert 2021 it takes too long and the delay is not desirable when the parameters are different.
Your problem is in the setting of the paramater timeout => 0
The documentation says
Number of seconds to continue trying to grant the lock. If the lock cannot be granted within this time period, then the call returns a value of 1 (timeout).
In zero seconds you may or may not get a timeout, so sometimes you are not blocked and sometimes you are.
Remove the timout
parameter (and use the dafault MAXWAIT
) or set it to some realistic value and check the response value - if is it 1
you got a timeout and must handle it. You mast handle all returns != 0.
I general you should always check the return code in the request
and preferably also deploy some logic to detect hanging handles and release them (e.g. setting release_on_commit
)
Example
Note the bit updated procedure.
Despite the removal of the timeout
parameter I check the return code of the request
function.
Finally I added the p_wait
paramater, so I can shedule the blocking procedure with a long wait and the testing procedure without a wait and I can clearly see the behavior, which is also logged in the total elapsed time of the procedure.
All works as expected - see below
create or replace procedure testlockProc (p_nm varchar2, p_wait int default 0)
as
lv_lockhandle VARCHAR2(500);
lv_ret_code PLS_INTEGER;
lv_retcode NUMBER;
request_failed EXCEPTION;
lv_start DATE;
begin
lv_start := sysdate;
dbms_lock.allocate_unique(p_nm, lv_lockhandle);
DBMS_OUTPUT.PUT_LINE (' got handle '|| lv_lockhandle);
lv_retcode := dbms_lock.request(lockhandle=>lv_lockhandle, /**timeout => 0,**/
lockmode => dbms_lock.x_mode);
if lv_retcode != 0 then
raise request_failed;
end if;
DBMS_OUTPUT.PUT_LINE ('request got REGEST response '|| lv_retcode);
dbms_lock.sleep(p_wait);
lv_ret_code := dbms_lock.release(lv_lockhandle);
DBMS_OUTPUT.PUT_LINE ('release got REGEST response '|| lv_retcode|| ' in ' || to_char( round((sysdate-lv_start)*24*3600))|| ' seconds');
end;
/
-- session 1 - procedure blocks the parameter for 60 seconds
set serveroutput on
begin
testlockProc (p_nm => 'wow', p_wait => 60);
end;
/
got handle 1073741964107374196448
request got REGEST response 0
release got REGEST response 0 in 60 seconds
-- session 2 (procedure blocked due to identical parameter from session 1)
set serveroutput on
begin
testlockProc (p_nm => 'wow');
end;
/
got handle 1073741964107374196448
request got REGEST response 0
release got REGEST response 0 in 50 seconds
-- session 3 different parameter (handle) - runns immediately
set serveroutput on
begin
testlockProc (p_nm => 'wow23');
end;
/
got handle 1073741965107374196549
request got REGEST response 0
release got REGEST response 0 in 0 seconds