| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Deadlock Problem
fitzjarrell_at_cox.net wrote:
> 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
I found the problem.
The INSERT statement had /*+ APPEND */ hint - by mistake.
I am guessing that because of the direct insert it could not determine
whether or not the two statements are modifying the same rows.
For those who are interested I am posting the relevant code below.
PROCEDURE PR_EVENT_WATCHER
IS
v_CNTR1 VARCHAR2(8); v_CNTR2 INTEGER := 0; v_JOB_NAME VARCHAR2(24);
BEGIN fi_operations.util_log.log_event('PR_EVENT_WATCHER','0','FUND EVENTS','Event Manager Begin');
FOR c IN (SELECT DISTINCT PRICING_DATE, FMR_FUND_NUMBER FROM FUND_EVENT WHERE EVENT_STATUS_CODE = 'S' AND EVENT_STATUS_TS >= TRUNC(SYSDATE)-5) LOOP v_CNTR1 := TO_CHAR(c.PRICING_DATE,'YYYYMMDD');
FOR i IN (SELECT PROGRAM_TO_RUN
FROM(SELECT E.PROGRAM_TO_RUN, E.EVENT_TYPE_CODE
FROM event_predecessor P,
EVENT_TYPE E,
(SELECT EVENT_TYPE_CODE, EVENT_STATUS_CODE
FROM FUND_EVENT
WHERE EVENT_STATUS_CODE = 'S'
AND FMR_FUND_NUMBER = c.FMR_FUND_NUMBER
AND PRICING_DATE = c.PRICING_DATE) FE
WHERE E.EVENT_TYPE_CODE = P.EVENT_TYPE_CODE
AND E.PROGRAM_TO_RUN IS NOT NULL
AND P.PREDECESSOR_EVENT_TYPE_CODE = FE.EVENT_TYPE_CODE(+)
GROUP BY E.PROGRAM_TO_RUN, E.EVENT_TYPE_CODE
HAVING MIN(CASE WHEN FE.EVENT_STATUS_CODE = 'S' THEN
1 ELSE 0 END) = 1) A
WHERE NOT EXISTS (SELECT NULL FROM FUND_EVENT
WHERE PRICING_DATE = c.PRICING_DATE
AND FMR_FUND_NUMBER = c.FMR_FUND_NUMBER
AND EVENT_TYPE_CODE = A.EVENT_TYPE_CODE
AND EVENT_STATUS_CODE = 'S'))
LOOP
v_CNTR2 := v_CNTR2+1;
v_JOB_NAME := 'EVENT_JOB'||v_CNTR1||'_'||v_CNTR2;
DBMS_SCHEDULER.create_job (
job_name => v_JOB_NAME,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
start_date => SYSTIMESTAMP,
enabled => TRUE);
END LOOP; END LOOP; fi_operations.util_log.log_event('PR_EVENT_WATCHER','0','FUND EVENTS','Event Manager End');
END PR_EVENT_WATCHER; PROCEDURE PR_FUND_AGG_POC (p_PRICING_DATE DATE,
p_FMR_FUND_NUMBER NUMBER)
IS
v_DUMMY PLS_INTEGER := 0;
BEGIN BEGIN
SELECT 1 INTO v_DUMMY
FROM FUND_EVENT
WHERE PRICING_DATE = p_PRICING_DATE
AND FMR_FUND_NUMBER = p_FMR_FUND_NUMBER
AND EVENT_TYPE_CODE = 'FUND_AGGREGATION'
AND EVENT_STATUS_CODE = 'I';
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END; IF v_DUMMY = 0 THEN
PR_UPD_FUND_EVENT
(p_PRICING_DATE,p_FMR_FUND_NUMBER,'FUND_AGGREGATION','I',NULL);
DELETE FROM FMR_HOLDINGS.FUND_AGG_POC
WHERE PRICING_DATE = p_PRICING_DATE
AND FUND_INDEX_CODE = (SELECT FUND_CODE FROM FUND WHERE
FUND_NUMBER = p_FMR_FUND_NUMBER);
INSERT INTO FMR_HOLDINGS.FUND_AGG_POC
SELECT H.PRICING_DATE,
F.FUND_CODE,
SUM(A.ACCRUED_NUMBEREREST),
NULL OAC,
NULL OAS,
NULL OAD
FROM FMR_HOLDINGS.HOLDINGS_POC H,
ANALYTICS A,
FUND F
WHERE H.PRICING_DATE = A.PRICING_DATE(+)
AND H.INSTRUMENT_ID = A.INSTRUMENT_ID(+)
AND H.FUND_NUMBER = F.FUND_NUMBER
AND H.PRICING_DATE = p_PRICING_DATE
AND H.FUND_NUMBER = p_FMR_FUND_NUMBER
GROUP BY H.PRICING_DATE, F.FUND_CODE;
COMMIT;
PR_UPD_FUND_EVENT
END IF;
EXCEPTION
WHEN OTHERS THEN
PR_UPD_FUND_EVENT
(p_PRICING_DATE,p_FMR_FUND_NUMBER,'FUND_AGGREGATION','F',NULL);
fi_operations.util_log.log_error('PR_FUND_AGG_POC', '0','FUND
EVENTS', 'PRICING_DATE: '||to_char(p_PRICING_DATE)||' FUND:
'||p_FMR_FUND_NUMBER);
END PR_FUND_AGG_POC; Received on Mon Jan 22 2007 - 14:18:01 CST
 |
 |