Re: Deadlock Problem

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

This is extremely inefficient and is grinding the system with a lot of unnecessary work. Also, tuning by guess work, changing INITRANS, is a guaranteed waste of time or worse.

Assuming Galen's supposition of your intent is correct, you didn't post your business rules, do as he suggests and forget the procedural code.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org