Home » RDBMS Server » Server Administration » Deadlock Problem
Deadlock Problem [message #213642] Thu, 11 January 2007 12:04
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
I have a procedure that looks similar to this:

PROCEDURE Proc1 (p_PRICING_DATE DATE, p_FUND_NUMBER NUMBER)
IS

BEGIN

DELETE FROM Tab1
WHERE pricing_date = p_PRICING_DATE
AND fund_number = p_FUND_NUMBER;

INSERT INTO Tab1
SELECT pricing_date, fund_number, COUNT(*)
FROM Tab2
GROUP BY pricing_date, fund_number;

COMMIT;

END;

It is called by another procedure:

PROCEDURE Proc2
IS

BEGIN

FOR Cur1 IN(SELECT DISTINCT pricing_date, fund_number FROM Tab3)
LOOP

Proc1(i.pricing_date, i.fund_number);

END LOOP;

END;

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.

Any suggestions?

Thanks
Art

[Updated on: Thu, 11 January 2007 12:10]

Report message to a moderator

Previous Topic: listener
Next Topic: Create Database - Error in Archive format
Goto Forum:
  


Current Time: Fri Dec 02 18:40:11 CST 2016

Total time taken to generate the page: 0.31959 seconds