| HOW DOES COMMIT, ROLLBACK WORK IN DISTRIBUTED ENVMT. [message #45423] |
Sun, 14 March 2004 13:24  |
Katrochi
Messages: 3
Registered: March 2004
|
Junior Member |
|
|
Hi,
I don't know much about using SAVEPOINT in Distributed Environment.
Can anyone please tell me whether I am using the COMMIT, ROLLBACK AND SAVEPOINT correctly here.
Also I will appreciate more info on Effective way of using SAVEPOINTS.
CREATE OR REPLACE PROCEUDRE MAIN_PROC
IS
DECLARE
COMMIT_CTR NUMBER := 1000;
CTR NUMBER := 0;
BEGIN
FOR i IN (SELECT EMP_CODE,TOT_DAYS FROM MONTH_FILE)
BEGIN
SAVEPOINT A;
UPDATE TRAN_SAL
SET TOT_SAL = i.TOT_DAYS * 40
WHERE EMP_CODE = i.EMP_CODE;
SAVEPOINT DIST;
-- Calling remote procedure thru DB Link.
P_UPDATE_REMOTE_DB@REMOTE_DB_LINK(i.EMP_CODE, RET_CODE);
IF RET_CODE = -1 THEN
-- Rollback remote update.
ROLLBACK TO DIST;
END IF;
CTR := CTR +1;
IF CTR = COMMIT_CTR THEN
COMMIT;
CTR := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO A;
END;
LOOP;
COMMIT;
END;
CREATE OR REPLACE P_UPDATE_REMOTE_DB(A_EMP_CODE IN VARCHAR2, A_RET_CODE OUT NUMBER) IS
BEGIN
UPDATE PAYMENTS
SET PAID = TRUE
WHERE EMP_CODE = A_EMP_CODE;
INSERT INTO EMP_PROCESSED (EMP_CODE)
VALUES A_EMP_CODE;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
|
|
|
|
| Re: HOW DOES COMMIT, ROLLBACK WORK IN DISTRIBUTED ENVMT. [message #45436 is a reply to message #45423] |
Mon, 15 March 2004 08:21  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Is it acceptable to you that the local update to TRAN_SAL is kept even if the remote update fails? That is how you have coded it currently. Typically, there would only be one savepoint and if either of the calls failed, the rollback would occur.
Also, I would highly recommend not to periodically commit as you are doing. Either the entire transaction should commit, or it should all rollback. Your rollback segments should be sized correctly for your transaction volume.
|
|
|
|
| Re: HOW DOES COMMIT, ROLLBACK WORK IN DISTRIBUTED ENVMT. [message #45437 is a reply to message #45436] |
Mon, 15 March 2004 14:51 |
Katrochi
Messages: 3
Registered: March 2004
|
Junior Member |
|
|
Todd,
I may not have given the currect example, but this is what I want.
Even if remote program update fails, I want execute the main program update. But if any update in main program fails, I want to rollback the entire transaction for that emp, including remote updates.
In the case of COMMIT, you mean to say that I should commit for each employee I process, instead of 100 count I put ?
|
|
|
|
| Re: HOW DOES COMMIT, ROLLBACK WORK IN DISTRIBUTED ENVMT. [message #45438 is a reply to message #45437] |
Mon, 15 March 2004 14:58 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Given those requirements, the savepoints you have defined are correct.
As far as the commit, I was suggesting that you only commit once, outside of any loop. Periodic commits consume more resources and remove any sort of transaction control (such as, if you lose your connection to the remote database in the middle of the job, how do you restart it?, etc.).
|
|
|
|
|
|
| Re: HOW DOES COMMIT, ROLLBACK WORK IN DISTRIBUTED ENVMT. [message #45450 is a reply to message #45439] |
Tue, 16 March 2004 08:42 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Rollback is not a scarce resource - it should be sized large enough to handle your complete transaction. Two million updates (100K * 20) is not that large of a transaction, relatively speaking.
If I were writing this, I might do something like this for performance:
create or replace procedure main_proc
is
begin
update tran_sal ts
set tot_sal = (select mf.tot_days * 40
from month_file mf
where mf.emp_code = ts.emp_code)
where emp_code in (select emp_code
from month_file);
-- Option #1: update remote tables directly
update payment@dblink
set paid = 'Y'
where emp_code in (select emp_code
from month_file);
insert into emp_processed@dblink (emp_code)
select emp_code
from month_file;
-- Option #2: Loop through cursor and call remote proc
for r in (select emp_code from month_file) loop
p_update_remote_db@dblink(r.emp_code);
end loop;
commit;
end;
Note the complete absence of savepoints in this example. What exceptions could you possibly encounter with these updates? You know your constraints on these tables, but there is little that could cause an issue with these updates. A unique constraint on emp_code in the emp_processed table might be an issue, but a local exception handler to ignore dup_val_on_index would handle that easily enough.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
