Database Transaction Design Question

Question

Consider the following two blocks of Java psedo-code in a system that uses optimistic transactions.

Example A:

try {
    txn.begin();
    // database operations
    txn.commit();
}
catch (Exception e) {
    txn.rollback();
}

Example B

txn.begin();
// database operations
try {
    txn.commit();
}
catch (Exception e) {
    txn.rollback();
}

I'm seeing transactions being conducted both ways in our code; I'm sure that A is correct. my intuition tells me that B is wrong, but it seems that there is no harm in B since the commit() is in the try block, and can be caught and rolled-back in the case of an error. Please explain whether B is correct, and why. Thanks!

Edit: So I'm not really getting the answer I'm looking for. I already know that B is somehow "bad", what I'm looking for is why it is bad; that is, is there some possible situation where A would work where B would fail?

-tjw

Answer 1

I would do a slight mix (Example C):

txn.begin(); 
try {
    // database operations
    txn.commit();
}
catch (Exception e) {
    txn.rollback();
}

Keep your database commands in the try block, but leave the 'begin' transaction out. If you error on 'begin' you won't try to rollback a transaction that was never started in the catch block.

Edit

The reason why example B is bad is because the only way you will ever rollback your transaction is if the commit fails. However, the reason A is also bad is because you have the small potential to fail when beginning a transaction, in which case you will attempt to rollback something that doesn't exist.