Persisting Data Following Rollback

We recently looked at the impact of rolling back transactions on statistics and I thought it would be worth following this up to look at some other objects to see how they behave when a rollback occurs.

When rolling back transactions we expect any changes to be rolled back. This isn’t always the case with data, just as we saw it wasn’t the case with statistics previously. Let’s try a few examples out and see how they go.

Regular tables

When we roll back modifications for a regular persisted table we would expect those changes to be reverted. For example, a simple transaction may look like this:

CREATE TABLE dbo.RollbackTable (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	FruitName VARCHAR(20)
);

INSERT INTO dbo.RollbackTable
	(FruitName)
VALUES ('Apple');

BEGIN TRAN

	INSERT INTO dbo.RollbackTable
		(FruitName)
	VALUES ('Banana');

	SELECT *
	FROM dbo.RollbackTable;

	ROLLBACK

SELECT *
FROM dbo.RollbackTable;

There we have it, the data we modified inside the transaction has been rolled back and our data is in the same state as prior to the transaction starting.

Nice and easy, let’s try something else.

Temporary tables

Let’s stay with tables and now look at temporary tables. As these are tables created for our session they don’t persist in our database however they are created in TempDb for their lifetime. How will they react to a rollback?

CREATE TABLE #TempTable (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	FruitName VARCHAR(20)
);

INSERT INTO #TempTable
	(FruitName)
VALUES ('Apple');

BEGIN TRAN

	INSERT INTO #TempTable
		(FruitName)
	VALUES ('Banana');

	SELECT *
	FROM #TempTable;

	ROLLBACK

SELECT *
FROM #TempTable;

As we can see the temporary tables follow what we saw with regular tables. This is due to them being stored in TempDb so our transaction will also cover objects modified in that database too.

Table variables

Next up we’ll look at the table variables. Table variables are backed by TempDb like the temporary tables above, however they are only scoped for the batch they’re executed in. Let’s repeat the same test as above and see if the result is also the same:

DECLARE @TableVar TABLE (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	FruitName VARCHAR(20)
);

INSERT INTO @TableVar
	(FruitName)
VALUES ('Apple');

BEGIN TRAN

	INSERT INTO @TableVar
		(FruitName)
	VALUES ('Banana');

	SELECT *
	FROM @TableVar;

	ROLLBACK

SELECT *
FROM @TableVar;

Now this one is a little different, the rollback isn’t impacting the table variable.

This is one of the wonderful advantages of a table variable. They can be great for catching the details of issues which may occur during execution. You could use them to hold logging details regarding an error, or stage the problem data to use elsewhere as part of exception handing.

Regular variables

We’ve seen with the table variable that the changes aren’t being rolled back. We should also check the same with regular variables which we might use as part of a transaction:

DECLARE @Counter INT = 1;

BEGIN TRAN

	SET @Counter = 2;

	SELECT [@Counter] = @Counter;

	ROLLBACK

SELECT [@Counter] = @Counter;

Again we can see that the variables aren’t being impacted by the rollback. Similarly to the table variable this can again be helpful in recording elements which might otherwise be rolled back, for example an error counter which may need to fire an alert if a given threshold is passed.

Indirect changes

Finally let’s look at changes not directly made as a statement within our transaction. How about if we had a stored procedure which changed the data in a table and then a rollback was instigated?

CREATE TABLE dbo.RollbackTable (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	FruitName VARCHAR(20)
);
GO

CREATE PROCEDURE dbo.UpdateProc AS
BEGIN
	INSERT INTO dbo.RollbackTable
		(FruitName)
	VALUES ('Banana');
END
GO

INSERT INTO dbo.RollbackTable
	(FruitName)
VALUES ('Apple');

SELECT *
FROM dbo.RollbackTable;

BEGIN TRAN

	EXEC dbo.UpdateProc;

	SELECT *
	FROM dbo.RollbackTable;

	ROLLBACK

SELECT *
FROM dbo.RollbackTable;

As with our first example where we change the data in a regular table, this also rolls back. Any changes by code called within the transaction – such as the procedure in this case – will also be covered in the same scope as our transaction. Due to that we’ll also see rollback of the same data.

Wrap up

Here we’ve looked at rolling back changes to data which we’ve made in various types of tables and variables. We’ve seen that rollbacks on table variables and regular variables are not reverted when a rollback takes place.

The fact that these elements are not regressed can be particularly useful when rollbacks are needed. They can be used to store information about the reason for rollback which will persist until a later point in the batch. This can be beneficial with large batches or fast moving data where isolating the reason for the issue can be tough retrospectively.

Did you know that a rollback didn’t impact some of these objects? Is this a technique you’ve used before to persist data through a rollback? Would you like to see any more content around this area? Any and all responses welcome!