Is it true that RDBMS systems are optimized for
COMMIT operations? How much slower/faster are
ROLLBACK operations and why?
For SQL Server, you could argue that a commit operation is nothing more than writing LOP_COMMIT_XACT to the log file and releasing locks, which is of course going to be faster than the ROLLBACK of every action your transaction performed since BEGIN TRAN.
If you are considering every action of a transaction, not just the commit, I'd still argue your statement is not true. Excluding external factors, speed of log disk compared to data disk speed for example, it's likely the rollback of any work done by a transaction will be faster than doing the work in the first place.
A rollback is reading a sequential file of changes and applying them to in-memory data pages. The original "work" had to generate an execution plan, acquire pages, join rows etc.
Edit: The it depends bit...
@JackDouglas pointed to this article which describes one of the situations where rollback can take significantly longer than the original operation. The example being a 14 hour transaction, inevitably using parallelism, that takes 48+ hours to rollback because rollback is mostly single threaded. You would most likely also be churning the buffer pool repeatedly, so no longer are you reversing changes to in-memory pages.
So, a revised version of my earlier answer. How much slower is rollback? All other things considered, for a typical OLTP transaction it isn't. Outside the bounds of typical, it can take longer to "undo" than "do" but (is this a potential tongue twister?) why will depend on how the "do" was done.
Edit2: Following on from discussion in the comments, here is a very contrived example to demonstrate that the work being done is the major factor in determining the relative expense of commit vs rollback as operations.
Create two tables and pack them inefficiently (wasted space per page):
Run a "bad" update query, measuring time taken to do work and the time taken to issue the commit.
Do the same again but issue and measure rollback.
With @Rows=1 I get a reasonably consistent:
Back to the original question. If you're measuring time taken to do work plus the commit, rollback is winning hands down because the majority of that work is spent finding the row to update, not actually modifying data. If you're looking at the commit operation in isolation, it should be clear that commit does very little "work" as such. Commit is "I'm done".
|show 5 more comments|
For Oracle, rollback can take many times longer than the time it took to make the changes that are rolling back. This often does not matter because
For SQL Server I'm not sure if the situation is the same but someone else will say if it isn't...
As for "why", I'd say the
Not all transactions will have their commit activity perform much better than their rollback. One such case is the delete operation in SQL. When a transaction deletes rows, these rows are marked as ghost records. Once a commit is issued and a ghost record cleanup task starts, then only are these records 'deleted'.
If a rollback was issued instead, it just removes the ghost markings from these records, and not the intensive insert statements.
Rollback isn't just "oh, never mind" - in a lot of cases it really does have to undo what it had already done. There is no rule that the rollback operation will always be slower or always be faster than the original operation. If you are waiting I suggest it is safest to just keep waiting.