如何在引數相同時阻止程式運行并在引數不同時允許程式運行。讓我解釋一下確切的問題
創建構建腳本
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;
正常測驗：-
現在，如果我在代碼下面運行，將插入一條記錄
begin
insert_to_lock_test(p_year => 1999);
end;
/
現在，如果我再次重新運行上面的代碼，則不會插入任何記錄。
硬測驗：-
但是如果我在下面運行，則會插入重復的記錄，這是不可取的。
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;
/
這種重復是我必須避免的。
約束：-
- 我無法創建唯一索引或主鍵。
- 我不能使用任何額外的檢查和存在條件。
我嘗試過的：-
- 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.
uj5u.com熱心網友回復：
你的問題出在引數的設定上 timeout => 0
該檔案說：
繼續嘗試授予鎖定的秒數。如果在此時間段內無法授予鎖定，則呼叫將回傳值 1（超時）。
在零秒內，您可能會也可能不會超時，因此有時您不會被阻止，有時會被阻止。
洗掉timout引數（并使用 dafault MAXWAIT）或將其設定為某個實際值并檢查回應值 - 如果是，則1您超時并且必須處理它。您必須處理所有退貨！= 0。
我一般你應該總是檢查中的回傳代碼request，最好還部署一些邏輯來檢測懸掛句柄并釋放它們（例如設定release_on_commit）
例子
注意位更新程式。盡管洗掉了timeout引數，我還是檢查了request函式的回傳碼。
最后我添加了p_wait引數，所以我可以擺脫長時間等待的阻塞程序和沒有等待的測驗程序，我可以清楚地看到行為，這也記錄在程序的總經過時間中。
一切都按預期作業 - 見下文
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/385729.html