I recently heard a fellow co-worker mention that InnoDB rollbacks are costly and negatively impact performance. How true is this statement?
To give a context, we use spring+hibernate and set @Transactional(readonly=true) for transactions where we need to only retrieve data. We also do see large number of rollbacks in sys.statements_analysis table.
Best Answer
InnoDB uses "optimistic" MVCC - when you do some changes, it applies them to the real tables and may even write them to disk. Commit only ensures that all the writes were finished and makes the changes visible and is otherwise almost instantaneous. That means that the rollback has to undo all these changes, possibly rewriting the pages on disk again and it has to do some more work in finding the previous versions in the undo logs etc. And the code is imho more optimized for the "primary" writes than for rollbacks too.
So the rollback may take at least the same and possibly more time than the inserts/updates/deletes took. I've met cases where someone tried to delete million of rows, killed the transaction after few hours (because it kept most of the table locked and they wanted to get rid of it and batch it properly next time) and the rollback took few more hours - in the worst cases they decided to restart the server (by force, because it does not respond until done) and found out that it continued the rollback during startup, effectively becoming inacessible for few more hours (before the kill it at least worked for rest of the data).
But as I wrote - the rollback time depends directly on the number of writes the transaction did and indirectly on how long it was active (more precisely how many changes other concurrent transactions did - the size of undo logs, which are "global" and not per-transaction). So if most of your transactions are select-only or not write heavy, the possible rollbacks possibly do not have to do too much work.
Contrast with other implementations, for example Firebird DB where when I executed a big delete, it finished surprisingly fast, but then when I tried to commit that, it took much longer than the delete itself.