Skip to main content

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

Posted by bernt on June 6, 2006 at 6:34 AM PDT

Statement: A lot of programmers do not know how to use transactions.
You may now think that I miss the target completely, but think for a while.

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

How often do you write like this?