SEO

#1 (**permalink**) 02-26-2008, 03:25 AM

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

#2 (**permalink**) 02-26-2008, 03:25 AM

artmt@hotmail.com wrote:
> I have a procedure that looks similar to this:
>
> PROCEDURE Proc1 (p_PRICING_DATE DATE, p_FUND_NUMBER NUMBER)
> IS

You received an answer. Reposting the question a second time in the same
forum will only generate an unwillingness to help you in the future.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3 (**permalink**) 02-26-2008, 03:39 AM

DA Morgan wrote:
> artmt@hotmail.com wrote:
> > I have a procedure that looks similar to this:
> >
> > PROCEDURE Proc1 (p_PRICING_DATE DATE, p_FUND_NUMBER NUMBER)
> > IS
>
> You received an answer. Reposting the question a second time in the same
> forum will only generate an unwillingness to help you in the future.
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

The second post was unintentional. Google glitch.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Deadlock Problem