Re: What to do with this situiation 2

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/03
Message-ID: <34393649.18478891_at_newshost>#1/1

On Fri, 03 Oct 1997 16:51:28 GMT, Thenardier_at_POBoxes.com (ªü´ö Thenardier) wrote:

>
>Another situation - a critical issue on emergency plan.
>
>In a client-server system, a client is committing a batch
>of records, say, 200, to an Oracle database (multiple tables
>perhaps) at the server. During the prcess, OOPS! the client's
>power is off! Only half of the records could be successfully
>committed. The user's just sitting there, shocked! Saying
>'where have all the data gone...?'
>

It doesn't work that way. If you commit and the commit succeeds all of the records are there. If you commit and the commit does not succeed, none of the records are there. The data will either be fully saved or not at all -- thats the whole purpose of transactions.

If the problem is the user needs to be able to know if the transaction succeeded or failed after such a situation (blackout during the commit), then all you need to do is piggy back an extra insert just prior to the commit. For example, you are going to commit, instead of just saying commit, do something like:

  insert into commit_tab ( msg ) values
  ( USER || ' committed transaction ' || trans_name || ' at ' || sysdate );

   commit;

then, the user can query the commit_tab to see if their transaction was committed or not (lack of record indicates the transaction rolled back, presence of record indicates all data saved).

>When the power is on again, I don't think I could rollback
>all the records cos some of them has been committed.
>
>But what to do with the rest of the 'committing' data?
>Would it disappear forever?
>What should a system designer handle in design time?
>Does Oracle say anything about this?
>
>Most important. Any good books covering this issue?
>
>I'll be happy if anyone here could share ur experience :D
>
>Thanx very very very much!
>
>
>Thenard

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 03 1997 - 00:00:00 CEST