I am trying to replicate an issue we recently faced. We have a PL/SQL procedure that has the logic below:

procedure mark_records (p_request_id number)
is
begin
    
    -- this marks which contracts are going to be processed
    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);
    
    -- additional processing --

    -- this unmarks the contracts were already processed        
    update  jisip_agreements
    set     request_id = null
    where   request_id = p_request_id;
    

end;
/

This program basically “marks” those records in jisip_agreements to be processed, does some additional processing (i.e. inserts into table X), then later on those same records will be “unmarked” once processing is completed.

One of our clients is running this process 6-10 times simultaneously and concurrently within an hour and is causing a ORA-00060 deadlock issue. We already have a fix in mind (i.e. Autonomous Transaction, additional filters, etc.), but we are unable to replicate the issue internally and it always completes successfully. We want to replicate it before moving the fix to Production.

I created a mock test in a local Database to replicate it, but in a slightly different approach:

Session 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;
/

Session 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;
/

I am getting the expected error:

 Error report - ORA-00060: deadlock detected while waiting for resource  
 ORA-06512: at line 9  
 00060. 00000 -  "deadlock detected while waiting for resource"  
 *Cause:    Transactions deadlocked one another while waiting for resources.  
 *Action:   Look at the trace file to see the transactions and resources  
            involved. Retry if necessary.

How can I replicate the ORA-0060 issue using the procedure?