Thursday 2 February 2012

Implicit transactions, cancellation and implicit rollbacks.

Having had some fun at work today tracking down an issue with transactions, I thought I'd run through exactly when a transaction is rolled back or kept open. I'm doing all this in SQL Server Management Studio, and all snippets have been run on Microsoft SQL Server 2008 R2 (SP1) Express Edition.

You are probably familiar with the basic form :

BEGIN TRAN

      /*DO SOMETHING */

COMMIT TRAN

And even the more complicated:

BEGIN TRAN

BEGIN TRY

     /* DO SOMETHING */

     COMMIT TRAN

END TRY

BEGIN CATCH

      /* If we hit an error, rollback the transaction */

      ROLLBACK TRAN

END CATCH


However, did you know what happens to the transaction when queries are cancelled? Let us investigate.

So, I'm going to use the @@TRANCOUNT operator to display how many transactions are open at various points. More information about @@TRANCOUNT, including information on nested transactions can be found on MSDN.


Let's see what happens as standard in a "normal" situation:

SELECT BEGINNING = @@TRANCOUNT

BEGIN TRAN

     SELECT MID_TRAN = @@TRANCOUNT

ROLLBACK TRAN

SELECT AFTER_ROLLBACK = @@TRANCOUNT

This produces the output:
BEGINNING

-----------

0

MID_TRAN

-----------

1

AFTER_ROLLBACK

--------------

0


This is what we would expect - the rollback rolls back all transactions.

But what about if execution is aborted (i.e. the caller presses the stop button)?

Lets see - Try running the following:

BEGIN TRAN

BEGIN TRY

     /* DO SOMETHING */

     WAITFOR DELAY '00:05:00' --Press cancel (stop button) whilst waiting here

     COMMIT TRAN

END TRY

BEGIN CATCH

     /* If we hit an error, rollback the transaction */

     ROLLBACK TRAN

END CATCH

Whilst this query is running, press the stop button. You might expect that the abort / stop would trigger a rollback, either implicitly or via the try/catch block. In fact, neither of these things happens; to check this run :

PRINT @@trancount

You will find that the answer is 1. This means that the transaction is still going.

Ok, so what harm can this cause? Well, let's look at an example scenario. Run the following in a new window:

CREATE TABLE MyScore (PersonID INT, Score INT);

INSERT MyScore (PersonID,Score) values (1,15)

BEGIN TRAN

     UPDATE MyScore SET Score = 10 WHERE PersonID = 1

     WAITFOR DELAY '00:15:00' --Cancel whilst waiting here

COMMIT TRAN

Leave it running this time.
In a new Query window, run the following:

SELECT @@TRANCOUNT

GO

SELECT
* FROM MyScore

This will display a 0 for the trancount, before hanging as the initial transaction is still open. Thus, an open transaction is preventing an unrelated connection from reading data.

Now stop and close this second query, and stop the first one by pressing the stop button. In the first connection (query window), run the following:

BEGIN TRAN

     SELECT first_trancount = @@TRANCOUNT

     UPDATE MyScore SET Score = 11 WHERE PersonID = 1

ROLLBACK TRAN

SELECT second_trancount = @@TRANCOUNT

SELECT * FROM MyScore

This will do a seemingly unrelated update, then run a rollback.

Lets look at the results:

first_trancount

---------------

2


second_trancount

----------------

0


PersonID Score

----------- -----------

1 15

This isn't quite what we expected; the first trancount was 2, showing that both transactions were active at the time. The second trancount was 0, as all active transactions are rolled back by a rollback command. This means that the first update was rolled back too, and we are left with the original table data.

This behaviour, which has been documented at http://support.microsoft.com/kb/295108 can be a particular problem when calling stored procs which contain explicit transactions in them, and is best mitigated with the SET XACT_ABORT ON command. The default setting is off, which means that only the statement which errors will be rolled back, and not the transaction.

This behaviour is also exhibited in client applications which close the connection abruptly; particularly in the case of timeouts, and made all the worse on pooled connections.

This is explored a little more deeply in this post on Dan Guzman's blog.
Whilst transactions certainly have thier place in data updates, you need to be aware of what can happen if the query is cancelled, or a timeout occurs when you are using them, particularly in stored procedures which are called by an application which can time out.

You also should be aware that whilst a COMMIT statement commits the inner most transaction, a ROLLBACK statement will reverse ALL uncommitted transactions on the connection.

Further reading on how to use transactions (both implicit and explicit) can be found at http://msdn.microsoft.com/en-us/library/ms175523.aspx.