Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 551
eold Avatar asked Jan 18 '12 20:01

eold


People also ask

Should I end with either commit or rollback?

A transaction ends when it is committed or rolled back, either explicitly (with a COMMIT or ROLLBACK statement) or implicitly (when a DDL statement is issued). To illustrate the concept of a transaction, consider a banking database.

What is the difference between a commit and rollback?

The COMMIT statement lets a user save any changes or alterations on the current transaction. These changes then remain permanent. The ROLLBACK statement lets a user undo all the alterations and changes that occurred on the current transaction after the last COMMIT.

What is a difference between commit rollback and savepoint?

The following commands are used to control transactions. COMMIT − to save the changes. ROLLBACK − to roll back the changes. SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.

Is rollback possible after commit?

COMMIT permanently saves the changes made by the current transaction. ROLLBACK undo the changes made by the current transaction. 2. The transaction can not undo changes after COMMIT execution.


1 Answers

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.

like image 166
Luis Avatar answered Oct 16 '22 18:10

Luis