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.
