MySQL- Best practices for using rollback 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.
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.