oracle行锁 merge,merge into引发死锁

create or replace procedure up_mid_66880002(v_rq number, --计算日期

rc1  out bssys.tp_cursor,

rc2  out bssys.tp_cursor)

as

nb_rq       number;

nb_ExecTime date;

check_rq    date;

nb_rows     number(10);

v_status    int;

v_msg       varchar2(200);

begin

nb_ExecTime := sysdate;

--当处理日期为空时，取系统日期作为处理日期

if v_rq is null then

nb_rq := to_number(to_char(sysdate, 'yyyymmdd'));

else

nb_rq := v_rq;

end if;

--校验日期格式

begin

select to_date(nb_rq, 'yyyymmdd') into check_rq from dual;

exception

when others then

v_status := -2;

v_msg    := '日期格式错误';

open rc1 for

select v_status as errorcode, v_msg as errormsg from dual;

return;

end;

--根据员工角色、员工角色分配查询出所有“工作平台坐席人员”、“质检人员”

--初始化坐席统计汇总表数据

/*merge into t_zxtjhz a

using(

select t2.ygbh, decode(t3.jsbh,4,1,30,1,409,2,1) as mtlx

from t_yg t1, t_yg_ygjsfp t2, t_yg_js t3

where t1.ygbh = t2.ygbh and t2.jsbh = t3.jsbh and t1.ygzt = 1

and t2.zt = 1 and (t3.jsbh = 4 or t3.jsbh = 40)

) b

on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)

when matched then update set

zxsc = 0,hrcs = 0,hrsc = 0,pjhrsc = 0,hccs = 0,hcsc = 0,pjhcsc = 0,fwcs = 0,fwsc = 0,

pjfwsc = 0,zjs = 0,zjsc = 0,fds = 0,pxs = 0,pjdf = 0,sscgs = 0,cjbhg = 0,bzjs = 0,bzjpjf = 0

when not matched then insert(a.rq, a.ygbh, a.mtlx) values(nb_rq, b.ygbh, b.mtlx);

nb_rows := sql%rowcount ;

commit;

p_cc_log(nb_rq, 'up_mid_66880002', nb_ExecTime, sysdate, '0', nb_rows,'0-初始化计算员工成功!');*/

--1.计算在线时长(只统计了1：话务，2：全媒体,91：UC签出，21%：话务签出)

nb_ExecTime := sysdate;

merge into t_zxtjhz a

using (select t1.ygbh, sum(t1.hs) zxsc, 1 as mtlx

from t_hwczlsk t1

where t1.rq = nb_rq

and t1.czlx like '21%'

and t1.sfcg &