Re: Deadlock Problem

Comments embedded.
a..._at_hotmail.com wrote:
> 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.

How else will you execute it using a cursor loop?

> If I modify Proc2 to submit 10 calls to Proc2 via DBMS_SCHEDULER to run
> simultaniously most executions fail with "ORA-00060: deadlock detected
> while waiting for resource".

Why shouldn' t they? I can't understand how you can call Proc 1 10 times each pass through the loop and expect different parameters to be passed. Possibly if you posted the 'revised' code you're using (the one to run 10 copies of Proc 1 simultaneously) someone could understand what you're doing. Given the examples you've posted here you'll get nothing but deadlocks with that scenario.

>
> 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.

Really? Please post how you tested this and the results you obtained from such testing.

> I also increased INITRANS on Tab1 to 50.
>
> Any suggestions?
>

Certainly. Post EXACTLY what you're doing with this 10 simultaneous calls function. Until you do I, and likely others, cannot understand how you're generating 10 different sets of parameters simultaneously with a cursor loop (unless you're using dynamic sql and building query strings in the loop, but that would be some interesting code to see).

> Thanks
> Art

David Fitzjarrell Received on Wed Jan 10 2007 - 16:31:19 CST