I have a procedure that looks similar to this:
PROCEDURE Proc1 (p_PRICING_DATE DATE, p_FUND_NUMBER NUMBER)
DELETE FROM Tab1
WHERE pricing_date = p_PRICING_DATE
AND fund_number = p_FUND_NUMBER;
INSERT INTO Tab1
SELECT pricing_date, fund_number, COUNT(*)
GROUP BY pricing_date, fund_number;
It is called by another procedure:
FOR Cur1 IN(SELECT DISTINCT pricing_date, fund_number FROM Tab3)
The routine runs fine when Proc1 is executed sequencially.
If I modify Proc2 to submit 10 calls to Proc1 via DBMS_SCHEDULER to run simultaniously most executions fail with "ORA-00060: deadlock detected while waiting for resource".
If I comment out "DELETE FROM Tab1..." block all 10 simultaneous executions run fine.
Based on the program logic each of the 10 calls to Proc1 deletes a separate non-intersecting set of rows. I tested and verified that.
I also increased INITRANS on Tab1 to 50.
[Updated on: Thu, 11 January 2007 12:10]
Report message to a moderator