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 warehouse_all_data;
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 :-
- I cannot create unique Index nor Primary key's.
- I cannot use any additional check and exists conditions.
What I have Tried :-
- Exclusive lock was tried. But issue here is it has blocked below functionality as well. In below case parameters are different and so process should be able to run in parallel. Let me provide code on how this was done
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.
CodePudding user response：
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