Rollback Gotchas – Part 1

Most people understand what a transaction is and how to set them up. The scope of this post is not to explain how transactions work within SQL Server, but for completeness sake I’ll provide a brief overview:

The basic premise is that SQL Server will run each statement as an implicit transaction unless the BEGIN TRANSACTION command is issued, (implicit transactions can be turned off by using the command SET IMPLICIT_TRANSACTIONS OFF) this statement will tell SQL Server that an explicit transaction is going to be executed. Should no errors occur within the transaction the coder should run the COMMIT TRANSACTION statement to tell SQL Server that this transaction has finished successfully and can indeed be written away, however if there has been an error the coder should cater for this and the ROLLBACK TRANSACTION statement should be issued.

So what are the limitations or more informally “Gotchas” that people need to consider when using explicit transactions?

The first limitation is that a rollback will only affect a logical operation, in order to show the difference between a logical and a physical operation let’s take a look at the following contrived example:

USE tempdb;
go

SET NOCOUNT ON;

CREATE TABLE dbo.RollbackExample1
(RollbackExample1ID INT IDENTITY(1,1)
,Col2 CHAR(999) NOT NULL
,Col3 VARCHAR(10) NULL
,CONSTRAINT PK_RollbackExample1 PRIMARY KEY CLUSTERED (RollbackExample1ID));

GO

INSERT INTO dbo.RollbackExample1 (Col2)
SELECT REPLICATE(‘A’,999);
GO 8

SELECT 
    OBJECT_NAME(OBJECT_ID)[TableName],
    Page_Count,
    avg_page_space_used_in_percent
FROM SYS.dm_db_index_physical_stats
(DB_ID(‘TempDB’),
OBJECT_ID(‘dbo.RollbackExample1′),
NULL,
NULL,
‘SAMPLED’);

BEGIN TRANSACTION;

UPDATE dbo.RollbackExample1
SET Col3 = ‘RollMeBack’
WHERE RollbackExample1ID = 1;

SELECT Col3
FROM dbo.RollbackExample1
WHERE RollbackExample1ID = 1;

ROLLBACK TRANSACTION;

In this example a table was created that completely filled 1 data page once it was populated as shown in sys.dm_db_index_physical_stats. An update statement was issued which increased the size of the record in slot 0 forcing a page split which is a physical operation. Let’s run the sys.dm_db_index_physical_stats query again to see how the clustered index looks now.

SELECT 
    OBJECT_NAME(OBJECT_ID)[TableName],
    Page_Count,
    avg_page_space_used_in_percent
FROM SYS.dm_db_index_physical_stats
(DB_ID(‘TempDB’),
OBJECT_ID(‘dbo.RollbackExample1′),
NULL,
NULL,
‘SAMPLED’);

As you can see from the image below the page split was not rolled back:

In this example only 1 page was created. Imagine if this table contained 10 million rows and a similarly scaled update statement was performed by mistake and needed to be rolled back how many page splits would be created and then how that would affect the fragmentation levels of the clustered index. Even though this mistake will have been rectified at the logical level resulting in the end users data being correct, the physical changes that were made may have the potential to cause a noticeable performance impact. It is imperative that regular maintenance is performed on your databases to keep performance at an optimum level.