This discussion is archived
9 Replies Latest reply: Dec 12, 2012 7:26 PM by Joe Weinstein RSS

How to ensure data integrity?

938489 Newbie
Currently Being Moderated
What I am going to do is on very minute run a daemon on a local machine and check for available data to be inserted into a remote machine. How I do now is open 2 connection one for local and run the while loop and in it I will do the insert to remote table and run an update statement to the local table to marked it have been inserted. Should I use transaction based to ensure integrity or this is fine?
  • 1. Re: How to ensure data integrity?
    gimbal2 Guru
    Currently Being Moderated
    A transaction might perform a bit better, but beware of doing too many operations in a single transaction as the transaction size may grow out of proportions. I don't see how a transaction would ensure more data integrity in the case you sketch.
  • 2. Re: How to ensure data integrity?
    Joe Weinstein Expert
    Currently Being Moderated
    The closest to a safe approach would be to run each local-and-remote-update
    as an XA transaction (two-phase-commit) involving the two connections.
    The irreducible risk is if you lose connectivity either DBMS to be updated,
    after you send the update command (or commit command), but before
    hearing the response. Until you regain connectivity, you will never know
    whether part or all of your transaction went through.
  • 3. Re: How to ensure data integrity?
    938489 Newbie
    Currently Being Moderated
    Dear Joe,
    When you say XA transaction here mean both under one distributed transaction am I right or keep them in separate transaction and commit them in a finally statement? This is where I am stuck and doing my research in it too. My db is mysql so is it possible to achieve with pure java connector library or any external libraries needed for this. Thank you.
  • 4. Re: How to ensure data integrity?
    Joe Weinstein Expert
    Currently Being Moderated
    If you have one logical transaction that requires successful updates to two independent DBMSes,
    then you irreducibly have a distributed transaction, and will have to commit each one separately,
    and hope that those two calls both succeed. If the first one does not, no big deal, but if the
    first commit succeeds and the second doesn't, or worse doesn't respond and you lose contact,
    you have a mess to clean up. If you can do that cleanup, and there is no risk in an incomplete
    transaction until you do clean up, then fine.
    The XA protocol tries to help, but cannot completely solve this dilemma. XA provides a 2-phase
    commit, where you tell each DBMS to prepare, which means that the DBMS now knows that
    its transaction is logically complete, and to do all it can now to make the coming commit call as
    simple and foolproof as possible. Then the XA coordinator (which could be your code) calls commit
    on each of those DBMSes, with an enhanced hope that the commit succeeds. XA also provides
    a protocol for recovery if you do lose contact, but even that is not perfect, with some heuristic
    assumptions required...
  • 5. Re: How to ensure data integrity?
    938489 Newbie
    Currently Being Moderated
    Dear Joe,
    You explanation of the scenario is quite clear. Let me explain what I do basically first I get data inserted into a table and have a field called processStatus='n'. Then I will run the codes to check all those with processStatus=''n'. Process here means read this lines with processStatus=''n' and insert them into the remote db thereafter update the processStatus='y'. So by you suggestion I understand means for both local and remote db set auto commit as false. Then in a finally statement commit first local connection then commit the remote connection? Is this what you are saying if I got your correctly? Or is the a better safer mechanism to conduct the XA ?Thank you.
  • 6. Re: How to ensure data integrity?
    Joe Weinstein Expert
    Currently Being Moderated
    That is essentially all you can do. Ask yourself what you would do if your second commit
    call fails with a DBMS message, or fails with a network failure and you cannot reconnect
    to the second DBMS...
  • 7. Re: How to ensure data integrity?
    938489 Newbie
    Currently Being Moderated
    Dear Joe,
    That is the question which worries me how if the local succeed and the remote db fail at the time of commit. Is there any work around for this to ensure integrity? Thank you.
  • 8. Re: How to ensure data integrity?
    Joe Weinstein Expert
    Currently Being Moderated
    As I said before, describing how a twop-phase commit works, it's the closest you can come to safe,
    but even with XA, you may have to clean up a problem manually after-the-fact with such error conditions.
  • 9. Re: How to ensure data integrity?
    938489 Newbie
    Currently Being Moderated
    Dear Joe,
    Below is the skeleton how I am working on now. So in this case I am not using any transaction but just loop one by one and insert into the remote db and thereafter only update the local db. So if the remote db insert failed the whole thing will fall into the catch right? What is your opinion on this?
    try{
         Select statement from local db
    
         while (rs1.next())
         {
             Insert into remote db 
             
             Update local db
         }
    }
    catch (SQLException e) {
       e.printStackTrace(System.out);
    }

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points