Is ROLLBACK a fast operation?
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".
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
- No locks are held while the transaction is rolling back
- 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
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).