Bernt Johnsen's Blog

Do you need to learn how to use transactions? Again?

Very often they (and myself, I admit) think of transactions as atomic changes to the database and write code like

... some SQL statements
commit
catch an SQLException and report a bug

This will work for most databases in most cases. BUT:

Seldom you see code that assumes that the transaction actually may fail, and that it is normal for transactions to fail (i.e. it is not a bug).

Occasionally, transactions do fail, and the frequency of failure is dependent on the database and the applications using the database. Transactions in distributed HA databases fail more frequently than in simple databases, and I would assume distributed transactions (XA) also fail more often. The proper way to write code for this is

while(not done) {
   ... some statements
   commit;
   done = true;
}  catch (SQLException e) {
   if (transient error) {
      .. 
      rollback;
  } else {
      rollback;
      done = true;
      e.g. throw exception
  }
}

Comments

• I'm personnaly using JPOX (a JDO 2 implementation) and i simply canno't use it without using transaction, it's a good way to keep integrity when some error occure when adding complex linked data to datastore.

  Posted by: alois on June 06, 2006 at 06:54 AM

• You'd better hope that a persistent error doesn't crop up looking like a transient error. Some attempt count is apparently customary. Even then you probably don't want multiple layers all attempting something like this.

  The only time I've seen something like this done in commercial software, it was to hide a race condition.

  Posted by: tackline on June 06, 2006 at 09:23 AM

This work is licensed under a Creative Commons License.