Java: commit vs rollback vs nothing when semantics is unchanged?

Question

Ok, I know the difference between commit and rollback and what these operations are supposed to do. However, I am not certain what to do in cases where I can achieve the same behavior when using commit(), rollback() and/or do nothing.

For instance, let's say I have the following code which executes a query without writing to db: I am working on an application which communicates with SQLite database.

try {
  doSomeQuery()
  // b) success
} catch (SQLException e) {
  // a) failed (because of exception)
}

Or even more interesting, consider the following code, which deletes a single row:

try {
  if (deleteById(2))
    // a) delete successful (1 row deleted)
  else
    // b) delete unsuccessful (0 row deleted, no errors)
} catch (SQLException e) {
  // c) delete failed (because of an error (possibly due to constraint violation in DB))
}

Observe that from a semantic standpoint, doing commit or rollback in cases b) and c) result in the same behavior.

Generally, there are several choices to do inside each case (a, b, c):

• commit
• rollback
• do nothing

Are there any guidelines or performance benefits of choosing a particular operation? What is the right way?

Note: Assume that auto-commit is disabled.

Answer 1

If it is just a select, I wouldn't open a transaction and therefore, there is nothing to do in any case. Probably you already know is there is an update/insert since you have already passed the parameters. The case where you intend to do a manipulation is more interesting. The case where it deletes is clear you want to commit; if there is an exception you should rollback to keep the DB consistent since something failed and there is not a lot you can do. If the delete failed because there was nothing to delete I'd commit for 3 reasons:

• Semantically, it seems more correct since the operation was technically successful and performed as specified.

• It is more future proof so if somebody adds more code to the transaction they won't be surprised with it is rolling back because one delete just didn't do anything (they would expect on exception that the transaction is rolled back)

• When there is an operation to do, commit is faster but in this case I don't think it matters.