corrupted state in connection.commit() execution ?
Suppose we have the following code:
Connection con = DriverManager.getConnection(url, "login","pass");
con.setAutoCommit(false);
//.. inserts/updates
con.commit();
I wonder if the following situation may happen:
1- the con.commit() starts execution successfully;
2- the RDBMS commits successfully;
3- After the RDBMS committed and before the con.commit() returns, the network fails,
so the con.commit() can complete and produces a SQLException;
If this may happen, then we should deal with an inconsistent situation in which the
RDBMS committed but the con.commit() produces SQLException. May it happen?
Thanks in advance,
[715 byte] By [
Dmazzuca] at [2007-9-26 6:34:46]
Hm, good question.
Even if the DB recognized that the client was gone, it wouldn't be able to tell him this.
In practice:
This would be the same like if your client would be switched power off.
What would you do then?
Restart, and try the changes again.
The same is in your case, if you get SQLException from commit: you'll retry the changes.
Either you'll find, they have been made already (your scenario, DB has committed),
or you'll have to repeat the failed steps.
If all records are identified by real clean keys (never changing!), not by certain attributes (evtl. changing), then they are safe in the db though client processes may fail temporarily.
So I would not see this as a big problem.
Other opinions? Come on, cracks!
As far as I know there is no way to guarantee that a 'transaction' succeeded in all possible scenarios. Any solution that you come up with involves software (even embedded software) and if I can force a break anywhere I want to, I can produce an inconsistent result.
So one learns to assess the probability that a particular scenario could realistically happen and ignore the ones which can not be fixed economically for the given project.
