Bernt Johnsen's Blog

June 2006 Archives

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
  }
}

This work is licensed under a Creative Commons License.