Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?

share|improve this question
add comment

5 Answers

up vote 13 down vote accepted

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):

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET NOCOUNT ON;
GO

CREATE TABLE dbo.Foo
(
    col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , col2 CHAR(4000) NOT NULL DEFAULT REPLICATE('A', 4000)
)

CREATE TABLE dbo.Bar
(
    col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , col2 CHAR(4000) NOT NULL DEFAULT REPLICATE('A', 4000)
)
GO

INSERT dbo.Foo DEFAULT VALUES
GO 100000

INSERT dbo.Bar DEFAULT VALUES
GO 100000

Run a "bad" update query, measuring time taken to do work and the time taken to issue the commit.

DECLARE 
    @StartTime DATETIME2
    , @Rows INT

SET @Rows = 1

CHECKPOINT
DBCC DROPCLEANBUFFERS

BEGIN TRANSACTION

SET @StartTime = SYSDATETIME()

UPDATE
    dbo.bar
SET
    col2 = REPLICATE('B', 4000)
FROM
    dbo.bar b
INNER JOIN
    (
    SELECT TOP(@Rows)
        col1
    FROM
        dbo.foo
    ORDER BY
        NEWID()
    ) f
ON  f.col1 = b.col1
OPTION (MAXDOP 1)

SELECT 'Find and update row', DATEDIFF(ms, @StartTime, SYSDATETIME())

SET @StartTime = SYSDATETIME()

COMMIT TRANSACTION

SELECT 'Commit', DATEDIFF(ms, @StartTime, SYSDATETIME())
GO

Do the same again but issue and measure rollback.

    DECLARE 
    @StartTime DATETIME2
    , @Rows INT

SET @Rows = 1

CHECKPOINT
DBCC DROPCLEANBUFFERS

BEGIN TRANSACTION

SET @StartTime = SYSDATETIME()

UPDATE
    dbo.bar
SET
    col2 = REPLICATE('B', 4000)
FROM
    dbo.bar b
INNER JOIN
    (
    SELECT TOP(@Rows)
        col1
    FROM
        dbo.foo
    ORDER BY
        NEWID()
    ) f
ON  f.col1 = b.col1
OPTION (MAXDOP 1)

SELECT 'Find and update row', DATEDIFF(ms, @StartTime, SYSDATETIME())

SET @StartTime = SYSDATETIME()

ROLLBACK TRANSACTION

SELECT 'Rollback', DATEDIFF(ms, @StartTime, SYSDATETIME())
GO

With @Rows=1 I get a reasonably consistent:

  • 5500ms for the find/update
  • 3ms commit
  • 1ms rollback

With @Rows=100:

  • 8500ms find/update
  • 15ms commit
  • 15ms rollback

With @Rows=1000:

  • 15000ms find/update
  • 10ms commit
  • 500ms rollback

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".

share|improve this answer
2  
'less work' is not necessarily 'faster' –  Jack Douglas Aug 31 '11 at 9:17
    
I knew that begin tran just increases counter of transactions. If I understood you, rdbms is doing all tasks (joins rows, generate execution plans...) at COMMIT? –  garik Aug 31 '11 at 9:17
3  
No, all the work is done before commit. The commit operation itself does comparatively little. –  Mark Storey-Smith Aug 31 '11 at 9:32
    
@Mark I've done some rough and ready tests inserting 2m rows and either commiting or rolling back. The overall time including rollback varied from 10s to 30s, versus between 6s and 14s for the overall time including commit. YMMV of course but this indicates that ballpark rollback is nearly as long or longer that the original transaction at least in my environment. –  Jack Douglas Aug 31 '11 at 12:35
2  
If you were to measure the time for the commit operation to complete, I'd expect it was minimal unless a checkpoint happens to be issued at the same time (which is seperate and unrelated). Thats kind of my point, the commit does very little whereas the rollback does everything that happened before the commit plus a bit more. The variance in your tests sugggest other factors at play but I'll certainly try and put some scripts together later. –  Mark Storey-Smith Aug 31 '11 at 12:54
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

  1. No locks are held while the transaction is rolling back
  2. It is handled by a low priority background process

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 rollback should be rare, usually only if something has gone wrong, and of course commit is likely to be much more common - so it makes sense to optimise for commit

share|improve this answer
    
+1 for "why" explanation. –  garik Aug 31 '11 at 9:18
add comment

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.

share|improve this answer
2  
And we've all had that "it's taking ages to rollback, let's reboot it" conversation at some point right? –  Mark Storey-Smith Aug 31 '11 at 13:51
    
I've seen many clients do it. Some come out relatively unscathed, others are much less fortunate. –  Aaron Bertrand Aug 31 '11 at 14:06
1  
@MarkStorey-Smith - If you reboot mid-rollback, doesn't SQL Server have to continue its rollback on startup anyway? –  Nick Chammas Oct 7 '11 at 19:12
2  
@Nick that depends - if the rollback was blocked before the reboot, for example, it might behave a lot quicker after a restart of the service because that other process was just killed. There is a LOT of "what if" in this scenario - anytime you reboot a server or restart a service to "fix" an issue, there are probably some much more serious problems at play. –  Aaron Bertrand Oct 7 '11 at 21:58
2  
@Nick, yes that's exactly what happens. My comment was intended to be "tongue in cheek", in so much that you inevitably end up having to explain that to the trigger happy folk that want to hit reboot whenever something isn't behaving as expected. –  Mark Storey-Smith Oct 8 '11 at 22:25
add comment

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.

share|improve this answer
    
Good example of how certain operations are optimised for rollback. –  Mark Storey-Smith Aug 31 '11 at 13:59
    
+1 perfect! it seams to be true. –  garik Aug 31 '11 at 14:59
add comment

Not all are. PostgreSQL takes no more time to roll back than to commit as the two operations are effectively identical in terms of disk I/O. I don't actually think this is a question of being optimized for commit so much as it is a question of what other queries one is optimizing for.

The basic question is how you address on-disk layout and how this affects commit vs rollback. The major db's which roll back more slowly than committing tend to move data, particularly from clustered tables, out of the main data structures and put it in a rollback segment when updating data. This means that to commit you just drop the rollback segment but to roll back you have to copy all the data back.

For PostgreSQL, all tables are heap tables and indexes are separate. This means that when rolling back or committing, no data has to be re-arranged. This makes commit and rollback both fast.

However, it makes some other things a little slower. A primary key lookup for example, has to traverse an index file and then it has to hit the heap table (assuming no covering indexes that are applicable). This is no a huge deal but it does add an extra page lookup or maybe even a few random page lookups (if lots of updates have occurred on that row) to check for other information and visibility.

The speed here however is not a question of optimization in PostgreSQL for write operations vs read ones. It is an unwillingness to privilege some read operations above others. Consequently PostgreSQL performs on average about as well as the other db's. It is just certain operations that may be faster or slower.

So I think the actual answer is that db's are optimized for certain workloads on the read side and this leads to challenges on the write side. Usually where there is a question, commits are usually, though not always, going to be favored over rollbacks. This however depends on the implications of doing either one (updates are different than deletes).

share|improve this answer
    
Good answer, but one slight quibble: "For PostgreSQL, all tables are heap tables and indexes are separate. This means that when rolling back or committing, no data has to be re-arranged" this is not the reason that no data has to be rearranged, rather it is because "The major db's which roll back more slowly than committing tend to move data", and pg doesn't, as you mentioned. Oracle also defaults to heap storage: the main difference is that Oracle uses 'undo' and reclaims all space on commit/rollback rather than going the 'vacuum' route. –  Jack Douglas Mar 6 at 6:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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