Search code examples
mysqltransactionsinnodb

Cost / performance of COMMIT vs. ROLLBACK if no data has been changed in the transaction

Disclaimer:

I already have read For a writeless transaction which is cheaper/quicker: COMMIT or ROLLBACK?, which is similar to my question, but relates to MS SQL Server and is quite old. Furthermore, the answer surprised me somehow, so I would like to know how the situation with MySQL 5.7 is in 2018.

Having said this:

Suppose the following scenario:

  • I am running MySQL 5.7.
  • I have turned off implicit transactions.
  • I have an InnoDB table.
  • I BEGIN a transaction.
  • I SELECT ... FOR UPDATE which locks a few rows in the table (in most cases, one row).
  • I examine the row(s) being selected / locked and come to the conclusion that the data is fine as it is, and therefore ...
  • ... I decide to not change any data at all in the rows being locked.

Now I want to finish the transaction. I could do that either the normal way, that is, by issuing a COMMIT, which in this case will just remove the locks, or, as an alternative, by issuing a ROLLBACK, which in this case also will just remove the locks.

The outcome of the two methods would be the same, but my feeling is that there may be a big difference regarding cost / performance.

Could somebody please tell me which of the methods is recommended if the fraction of the rows being locked is always negligible (i.e. something like 1/1e6), and perhaps give some background (or a link to some background)?

Solution

  • (Caveat: This Answer is an educated guess, but could be wrong.)

    The usual use of commit/rollback is to actually make changes, then undo them. InnoDB is optimistic in that it assumes a transaction will be committed. That is, it maximizes the efficiency of making changes (inserts, updates, etc), while not optimizing for rollback. Hence, ROLLBACK is more (sometimes 'much more') costly than COMMIT. The stuff for a potential rollback is saved in an undo log, which eventually needs cleaning up. But this cleanup is done 'later', not while the user is waiting for COMMIT to finish.

    Your use does not involve any actual changes, so I would guess that the performance is similar. I would probably execute ROLLBACK to make it clear to future readers (including yourself) that "nothing was done".

    Since the clumsy part of the undo stuff is old copies of rows, and your case does not generate such, I see little difference.

    Your link refers to SQL Server, which probably has different algorithms.