| Deadlock situation Oracle 9i [message #129243] |
Fri, 22 July 2005 12:30 |
sgarbiglia
Messages: 1
Registered: July 2005
|
Junior Member |
|
|
Dear all,
Does anybody know how can we prevent a deadlock situation, which occurs when the code below (partly) is called simultaneously from an Oracle Job and from another package?
To avoid this issue, we have set a field to see if the record is in process but in some case we can have an overlapping zone between the cursor "c_Acc_Contracts" resulting to a conflictual situation in the database. i.e.: update not yet committed when the procedure is called from the invoicing process or from the Oracle Job.
Any help would be great! - Thanks in advance, regs.
Desc (partly):
Oracle Job - call: pkgA
Invoicing Process - call: pkgA
*** SESSION ID:(542.380) 2005-05-31 14:37:30.880
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE ctrcustomer set QTYBWSUM = 0 ...
Note: the deadlock occurs on update (Contract_package.Acc_Contract) called from this procedure.
Code (partly):
PROCEDURE Acc_Contracts IS
v_Changed CUSCONTRACT.CHANGED%TYPE;
cursor c_Acc_Contracts is
select distinct ctrno, subno from cuscontract where changed = '1';
BEGIN
FOR c in c_Acc_Contracts LOOP
select distinct nvl(changed,'0') into v_Changed from cuscontract where ctrno = c.ctrno and subno = c.subno;
IF v_Changed = '1' THEN
/* set changed = 'C' to avoid contract is accumulated more then once */
update cuscontract set changed = 'C' where ctrno = c.ctrno and subno = c.subno;
COMMIT;
/* Start accumulation per Contractno/subno */
Contract_package.Acc_Contract(c.ctrno,c.subno);
commit;
/* After accumulation set cuscontract.changed to 0 */
update cuscontract set changed = '0' where ctrno = c.ctrno and subno = c.subno;
COMMIT;
END IF;
END LOOP;
END Acc_Contracts;
PROCEDURE Acc_Contract (p_ctrno NUMBER, p_subno NUMBER) IS
...
update cuscontract set ...
...
END Acc_Contract;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
