SQL & PL/SQL

Hi All,

In one of my application , I am using SELECT ..... FOR UPDATE id, flag on ORDER and VOUCHER table. Because during this process , we are doing some insertion and updation on both the table, so we do not want other user to change these rows. thats the reason i have placed FOR UPDATE.

In our Business , there are 40 to 50 applications are using these ORDER and VOUCHER table, those all r different environment Like Mainframe , JAVA, .Net,Crysatl Reports, So they will be using these tables very frequently for reporting and some other purpose. These all applications are developed 10 years ago , also I do not know the functionality and i do not have control for other application.

Now the issues is due to this SELECT FOR UPDATE usage in one of the application , our other application is getting failed , and some time blocking / dead lock issues is ORA - 00060 detected.

Also it is very difficult for me to redesign the application. But I am sure that due to this SELECT FOR UPDATE on one of my applciation causing this issues , this JOB is running 9hrs per day.... So because of that all other application detecting blocking / dead lock issues.

I believe that , If we fix the issues in this application , I can rectify the issues. Or do we have any generic solution for this.


Another condition that may have warranted for using FOR UPDATE locking is ( – if order placed has not completed and the record is deleted from order_source, the row can get deleted from ml order table.
So incase the voucher has picked up the record and not yet marked the order Process id_flag complete, in this time if any order is deleted in order_source table, when procedure returns to update the row – it may not exist.



--------------------------------------------------------------------------------

Current Program will look like:


--------------------------------------------------------------------------------

App_Prc_1

BEGIN
/***** taking the order details (source) and will be populate into the table ****/

CURSOR Cursor_Upload IS
SELECT col1, col2 … FROM orders t1, order_source s
WHERE t1.id_no = s.id_no
AND t1.id_flag = ‘N’
FOR UPDATE OF t1.id_flag;

/************* used for inserting the another entry , if theres any updates happened on the source table , for the records inserted using 1st cursor. **************/
CURSOR cursor_update IS
SELECT col1, col2 … FROM voucher t2 , orders t1
WHERE t1.id_no = t2.id_no
AND t1.id_flag = ‘Y’
AND t1.DML_ACTION = ‘U’,’D’ -- will retrieve the records which are updated and deleted recently for the inserted records in transaction table 1 for that particular INSERT..
FOR UPDATE OF t1.id_no,t1.id_flag;

BLOCK 1

BEGIN

FOR v_upload IN Cursor_Upload;
LOOP
INSERT INTO voucher ( id_no , dml_action , …. ) VALUES (v_upload.id_no , ‘I’ , … ) RETURNING v_upload.id_no INTO v_no -- I specify for INSERT

/********* Updating the Flag in the source table after the population ( N into Y ) N  order is not placed yet , Y  order is processed first time )

UPDATE orders
SET id_FLAG = ‘Y’
WHERE id_no = v_no;
END LOOP;
EXCEPTION WHEN OTHER THEN
DBMS_OUTPUT.PUT_LINE( );
END ;

BLOCK 2

BEGIN -- block 2 starts

FOR v_update IN Cursor_Update;
LOOP

IF v_update.dml_action = 'D' THEN

UPDATE orders
SET ADD__CANCEL_FLAG = 'C' -- A: If its new records| C: If its deleted records
WHERE id_no = v_update.id_no

ELSE

INSERT INTO voucher ( id_no ,id_prev_no, dml_action , …. ) VALUES (v_id_seq_no, v_upload.id_no ,, … ) RETURNING v_upload.id_no INTO v_no

UPDATE orders
SET id_FLAG = ‘Y’
WHERE id_no = v_no;
END LOOP;
EXCEPTION WHEN OTHER THEN
DBMS_OUTPUT.PUT_LINE( );
END IF ;
END; -- block2 end

END app_proc; -- Main block end



--------------------------------------------------------------------------------

Sample output in table1 :

Id_no | Tax_amt | re_emburse_amt | Activ_DT | Id_Flag | DML_ACTION
01 1,835 4300 12/JUN/2009 N I ( these DML Action will be triggered when ever if theres in any DML operation occurs in this table )
02 1,675 3300 12/JUN/2009 Y U
03 4475 6500 12/JUN/2009 N D

Sample output in table2 :

Id_no | Prev_id_no Tax_amt | re_emburse_amt | Activ_DT
001 01 1,835 4300 12/JUN/2009 11:34 AM ( 2nd cursor will populate this value , bcoz there s an update happened for the below )
01 0 1,235 6300 12/JUN/2009 09:15 AM ( 1st cursor will populate this record when job run first time )

02 0 1,675 3300 12/JUN/2009 8:15AM

003 03 4475 6500 12/JUN/2009 11:30 AM
03 0 1,235 4300 12/JUN/2009 10:30 AM



Now the issues is :

When these Process runs, our other application jobs failing, because it also uses these main 2 tranaction table. So dead lock is detecting in these applications.


Now Can anyone please suggest me ,

1) How can I handle this Exception/Situation
2) Is there any generic Solution to handle this issue across all application in all environment.
3) I do not want my other application to wait / getting failed due to this FOR UPDATE in one of the application. Its impacting my business in a large.

My Ideas :






--------------------------------------------------------------------------------

1) can any one suggest me that can we remove SELECT FOR UPDATE cursor , and can we take those rows viirtually and can process . so that we can avoid lock .... but I am not sure .. is it possible. or can we use some staging / temp table . Is this will impact performance ???

2) or can we use priortize or Serialization concepts, But i do not have the control over all other applciation , So i dont think its possible. Or can i do it in data base level using Stack/Queue FIFO method... I am not sure how to go ahead ....

Please help / suggest me .


Regards,
Maran A

Edited by: Maran on Jul 27, 2009 1:43 PM