Reputation: 1480
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?
Upvotes: 2
Views: 351