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

ses011 · Accepted Answer · 2011-03-23T13:30:42.437

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.

edited Mar 23 '11 at 13:30

answered Mar 22 '11 at 03:15

ses011

1,216
1
9
15

No. B defeats the purpose of having a transaction. The only way a transaction would ever be rolled back (in the immediate code block, see @Thilo) would be if the commit failed...which would just be weird. – ses011 Mar 23 '11 at 04:22

score 0 · Answer 2 · answered Mar 22 '11 at 03:09

0

Well, in B you would not rollback the transaction if there is an error before the commit. You do not commit either, at least not in that piece of code, but maybe later, by accident? It seems better to commit or rollback as early as possible, and not leave the transaction hanging around for some cleanup that hopefully takes place later.

It'd say that is a problem with B.

Also, depending on your system you may need finally blocks as well to properly decommission the transaction.

answered Mar 22 '11 at 03:09

Thilo

257,207
101
511
656

yes, but rolling back the transaction only matters if changes have been effected in the database, and before `commit()`, no changes are effected, so I don't think this matters – Travis Webb Mar 22 '11 at 03:35
If you do not commit or rollback for a long time on an idle transaction, that seems to waste resources (at least in some systems). Also, the mere existence of a code path that has `begin`, but neither `commit`, `rollback` or `finish` in it seems bad. – Thilo Mar 22 '11 at 06:20
yes I know it seems "bad", but I'm wondering if it is "incorrect". – Travis Webb Mar 22 '11 at 13:14

Database Transaction Design Question

2 Answers2