如何使用目标PL/SQL存储过程复现ORA-00060死锁问题？

我们有PL/SQL存储过程mark_records，功能为标记jisip_agreements表中待处理记录，完成额外处理后取消标记。客户并发运行该过程6-10次时触发ORA-00060死锁，我们已有修复方案但无法内部复现。目前通过不同会话单独更新不同agreement_id的语句可复现该错误，现需通过该存储过程复现此死锁问题。

存储过程代码 #

procedure mark_records (p_request_id number)
is
begin
    -- 标记待处理的合同
    update  jisip_agreements jp
    set     jp.request_id = p_request_id
    where exists (select 1 from
                    jisip_eligible_transactions x
                    jisip_eligible_suppliers y
                    jisip_eligible_schedules z
                    where x.schedule_id = z.schedule_id 
                    and y.supplier_id = x.supplier_id
                    and jp.agreement_id = y.agreement_id);
    -- 额外处理逻辑 --
    -- 取消已处理合同的标记        
    update  jisip_agreements
    set     request_id = null
    where   request_id = p_request_id;
end;
/

当前手动复现的测试代码 #

会话1： #

set serveroutput on;
begin
    update  jisip_agreements
    set     request_id = 345435345
    where   agreement_id = 1;    
    dbms_session.sleep(5);
    update  jisip_agreements
    set     request_id = 345435345
    where   agreement_id = 2;        
end;
/

会话2： #

set serveroutput on;
begin
    update  jisip_agreements
    set     request_id = 12345
    where   agreement_id = 2;    
    dbms_session.sleep(5);
    update  jisip_agreements
    set     request_id = 12345
    where   agreement_id = 1;
end;
/

基于存储过程的复现方案 #

要通过mark_records复现死锁，核心是让多个会话的存储过程以不同顺序锁定jisip_agreements表中的行，模拟手动测试的交叉等待场景，具体步骤如下：

1. 准备测试数据
   确保jisip_agreements表中存在至少2条agreement_id为1和2的记录，且这两条记录均满足存储过程中exists子查询的关联条件（即能关联到jisip_eligible_transactions、jisip_eligible_suppliers、jisip_eligible_schedules中的对应数据）。

2. 临时修改存储过程
   在第一次update和第二次update之间加入延迟，模拟实际业务中"额外处理"的耗时，确保会话间的锁等待有足够时间形成死锁：

   procedure mark_records (p_request_id number)
   is
   begin
       -- 标记待处理的合同
       update  jisip_agreements jp
       set     jp.request_id = p_request_id
       where exists (select 1 from
                       jisip_eligible_transactions x
                       jisip_eligible_suppliers y
                       jisip_eligible_schedules z
                       where x.schedule_id = z.schedule_id 
                       and y.supplier_id = x.supplier_id
                       and jp.agreement_id = y.agreement_id);
       -- 加入延迟模拟额外处理耗时
       dbms_session.sleep(5);
       -- 取消已处理合同的标记        
       update  jisip_agreements
       set     request_id = null
       where   request_id = p_request_id;
   end;
   /
   

3. 控制行锁定顺序
   Oracle的update语句默认按数据存储顺序或索引顺序锁定行，要稳定复现死锁，需强制不同会话以相反顺序锁定目标行：

   • 会话1使用的存储过程：在update语句后添加order by agreement_id asc，让其先锁定agreement_id=1，再锁定agreement_id=2
   • 会话2使用的存储过程：在update语句后添加order by agreement_id desc，让其先锁定agreement_id=2，再锁定agreement_id=1

4. 并发执行测试
   打开至少两个数据库会话，几乎同时执行各自的mark_records调用。会话1先锁定agreement_id=1，会话2先锁定agreement_id=2，延迟结束后两者尝试锁定对方已持有的行，就会触发ORA-00060死锁。

5. 验证死锁
   通过查看数据库alert.log或查询v$deadlock视图，确认死锁已发生。

内容的提问来源于stack exchange，提问作者Migs