解决同一程序在并行同时调用时，出现资源等待错误-使用DBMS_LOCK.sleep

 

解决同一程序被并行同时调用时，出现资源等待错误问题。 使用dbms_lock.sleep (10);

procedure prc_lock_test(v_engine_id in varchar,
v_flag_desc in varchar,
v_sysdate in varchar,
exitcode out number) as
l_proc_name varchar2(100) := 'prc_lock_test';
v_p_name number(2);
v_status varchar2(20);
v_cou number(2);
logger logger_factory := logger_factory;
begin
exitcode := -20099;
logger.info(l_proc_name || ',start');

select count (1)
into v_cou
from z_con_status
where proc_name = 'prc_lock_test';

if v_cou = 0 then
insert into z_con_status
(proc_name, status, sys_creation_date)
values
('prc_lock_test', 'start', sysdate);
commit;

logger.info(l_proc_name || ',sysdate1:'||to_char(sysdate,'yyyyhh24miss'));
else
loop
select status
into v_status
from z_con_status
where proc_name = 'prc_lock_test';

exit when v_status = 'end';

if v_status = 'start' then
dbms_lock.sleep (10);
logger.info(l_proc_name || ',lock sysdate2:'||to_char(sysdate,'yyyyhh24miss'));
end if;

end loop;

end if;

update z_con_status
set status = 'start', sys_creation_date = sysdate
where proc_name = 'prc_lock_test';
commit;

/*keep bak table for one month */
v_p_name := to_number(to_char(sysdate + 1, 'dd'));

execute immediate 'alter table bak_z_productionorders truncate partition p_' ||
v_p_name || ' update indexes';

execute immediate 'alter table bak_z_productionorders_bom truncate partition p_' ||
v_p_name || ' update indexes';

update z_con_status
set status = 'end', sys_creation_date = sysdate
where proc_name = 'prc_lock_test';
commit;
logger.info(l_proc_name || ' completed.');
exitcode := 0;
exception
when others then
exitcode := sqlcode;
logger.error;
raise;
end prc_lock_test;