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.

Foreign Keys - a quick recap.

What are foreign keys for? A foreign key is used to allow data in one table to be checked against another (reference) table.

A foreign key will also prevent the referenced (master) table from being dropped. Consider this code snippet:

IF EXISTS (SELECT object_id(N'dbo.mst'))

DROP TABLE dbo.mst

CREATE TABLE dbo.mst (

id INT IDENTITY(1,1) PRIMARY KEY

,txt varchar(10))

INSERT dbo.mst (txt) VALUES ('foo'),('bar')

CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY

,name varchar(100)

,mstid INT)

ALTER TABLE [dbo].child  WITH CHECK ADD  CONSTRAINT [FK_CHILD_SINGLE] FOREIGN KEY(mstid)

REFERENCES [dbo].mst ([id])

This code works fine once, as dbo.mst does not exist, so the drop table is not run. However once the check constraint is in place, the run will fail:

Msg 3726, Level 16, State 1, Line 2

Could not drop object 'dbo.mst' because it is referenced by a FOREIGN KEY constraint.

Msg 2714, Level 16, State 6, Line 3

There is already an object named 'mst' in the database.

Foreign key behaviour

Having created the child table, let’s put some data in it:

INSERT dbo.child

        ( name, mstid )

    VALUES

        ( 'Barney', 1 )

(1 row(s) affected)

Great! We can verify that the data has been inserted by selecting records from the child table.

Now,  what if we try to insert a record where the mstid doesn’t exist in the mst table?

INSERT dbo.child

        ( name, mstid )

    VALUES

        ( 'Fred', 0 )

We would not expect this to work, and sure enough:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CHILD_SINGLE". The conflict occurred in database "Test_DB", table "dbo.mst", column 'id'.

The statement has been terminated.

Now, lets say we have the situation where we would like to have a foreign key on a non-mandatory value.

INSERT dbo.child

        ( name, mstid )

    VALUES

        ( 'Wilma', null )

(1 row(s) affected)

So if the field is null, then it isn’t considered to break the foreign key.

Thus, a Foreign Key can be summarised as a check on whether a value that exists is actually part of the referenced set.

What about composite foreign key behaviour?

A quick search of Books Online produces the following (from http://msdn.microsoft.com/en-us/library/ms175464.aspx) :

“A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.”

So, that’s an interesting nugget. Let’s test this. A different table structure is called for:

IF EXISTS (SELECT object_id(N'dbo.child'))

DROP TABLE dbo.child

IF EXISTS (SELECT object_id(N'dbo.mst'))

DROP TABLE dbo.mst

CREATE TABLE dbo.mst (

id INT

,idtwo INT

,txt varchar(10)

,Primary key (id, idtwo)

)

CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY

,name varchar(100)

,mstid INT

,mstidtwo INT

)

ALTER TABLE [dbo].child  WITH CHECK ADD  CONSTRAINT [FK_CHILD_DOUBLE] FOREIGN KEY(mstid,mstidtwo)

REFERENCES [dbo].mst ([id],idtwo)

And some data:

INSERT dbo.mst (id,idtwo,txt) VALUES (1,1,'foo'),(1,2,'bar')

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test both valid', 1, 1 )

         

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test not valid', 1, 3 ) 

         

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test one valid other null', 1, null )        

         

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test both null', null, null )  

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test one valid other null 2', null, 2 )                    

So, what results?

SELECT * FROM dbo.mst

id	idtwo	txt
1	1	foo
1	2	bar

SELECT * FROM dbo.child

id	name	mstid	mstidtwo
1	test both valid	1	1
3	test one valid other null	1	NULL
4	test both null	NULL	NULL
5	test one valid other null 2	NULL	2

That’s a little odd..  The only record that failed was the one that had known wrong (as opposed to null) values.

This is the natural extension of the single-field version, but still a bit of a surprise, and one that could trip you up if you didn’t expect it.

What about if we follow the advice from Books Online, and make the columns non-nullable?

Well, the first thing to note, is that it isn’t the columns in mst that must be non-nullable, it’s the columns in the child table. This is key (if you want to prove it to yourself, try altering the above code to match!)

So, if we change the child table to be

CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY

,name varchar(100)

,mstid INT NOT NULL

,mstidtwo INT NOT NULL

)

And then re-run the insert statements from above, what do we get?

As might be predicted, the output looks like :

 

id	name	mstid	mstidtwo
1	test both valid	1	1

However, let's look at the messages returned:

(2 row(s) affected)

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 11

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CHILD_DOUBLE". The conflict occurred in database "Test_DB", table "dbo.mst".

The statement has been terminated.

Msg 515, Level 16, State 2, Line 19

Cannot insert the value NULL into column 'mstidtwo', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Msg 515, Level 16, State 2, Line 27

Cannot insert the value NULL into column 'mstid', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Msg 515, Level 16, State 2, Line 34

Cannot insert the value NULL into column 'mstid', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.

The statement has been terminated.

(2 row(s) affected)

(1 row(s) affected)

We note that the insert wasn’t refused by the Foreign Key if there were nulls in the table – the ‘nullability’ of the column takes precident over the Foreign Key. This makes sense as it’s quicker to validate when only looking at one table, but still a point of note.

This means our conclusion of “a Foreign Key can be summarised as a check on whether a value that exists is actually part of the referenced set” is still true, but it’s worth being aware of where the model can break down if you allow null values in the set.