0

What is the best practice / recommended way of using ROLLBACK operation, if any DML operation fails ?

Any ROLLBACK, COMMIT mechanism ?

FYI: I'm using InnoDB tables which complies with Atomicity.

1
  • Depends entirely on each specific scenario. It sounds like you might want to look into SAVEPOINT for use in your code. Commented May 26, 2020 at 11:20

2 Answers 2

2

There are two ways not to commit a transaction

  • Explicit ROLLBACK
  • Close a connection w/o COMMIT

Both of them are fine to use depending on what fits better the app logic.

When to rollback explicitly?

If you want to reuse a connection. If you want to make transactions as short as possible (a good thing to aim for).

When to close the connect w/o commit?

If a transaction is one-time task, and you'd like to simplify error handling.

Few things to consider.

  • Do not keep uncommitted transactions for long time. Often that happens when the app opens a trx, goes to do something else, comes back later to complete and commit the transaction. This really hurts. It leads to high CPU usage and kills performance.
  • ROLLBACK usually goes into except ... block (or whatever applies to the specific language). But ROLLBACK itself may fail, so make sure to handle exceptions correctly to avoid "Failed to rollback a transaction" errors.
  • ROLLBACK often takes more time than you'd expect. If transaction was large, the rollback takes more time than all the previous statements in the transaction. Make the transactions as short as possible.
3
  • thanks for the reply. I understand, we can do it with programming language. But, how to achieve that within SQL ? How to identify if any statement has failed within a transaction ? Then maybe based on the check we can either COMMIT or ROLLBACK using conditional statements. Commented May 21, 2020 at 6:17
  • it's not possible or I don't understand your request. From your app you send queries and get responses (query result, exceptions, etc). Based on that you proceed accordingly in your app. If you want something like IF NOT UPDATE .. THEN ROLLBACK then it's not supported by SQL itself.
    – akuzminsky
    Commented May 21, 2020 at 16:36
  • Ok. Thanks for you reply. I wanted to achieve IF NOT UPDATE .. THEN ROLLBACK in SQL Commented May 23, 2020 at 14:16
-1

Rollback is not for any DML fails specifically, but it can used in situations like :

  1. Data might be partially inserted due to transient state conflict/error.

  2. Mistakenly injected data in your table.

  3. Using Saving point + Rollback is to rollback if the data is not necessary for future from that specific (save) points.

1

Not the answer you're looking for? Browse other questions tagged or ask your own question.