Is ROLLBACK a fast operation?

Question

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

Answer 1

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

---

In SQL Server 2019 and later, Accelerated Database Recovery (ADR) is available. When enabled, the Logical revert aspect of ADR provides instant transaction rollback and undo for all versioned operations. Undo restores previous values from the Persistent Version Store. All locks are released immediately after transaction abort.

Answer 2

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

Answer 3

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, though even if the original transaction ran in parallel, the rollback is single-threaded. If you are waiting I suggest it is safest to just keep waiting.

This all changes with SQL Server 2019, of course, and Accelerated Database Recovery (which, at a penalty that is also variable, allows for instantaneous rollback regardless of size-of-data).

Answer 4

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.

Answer 5

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