Aaron Bertrand

Following a single-transaction deadlock across SQL Server versions

SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Erin Stellato is a Principal Consultant with SQLskills and a Microsoft Data Platform MVP.

Erin’s Posts

One of the less common deadlocks is one where there is a single user and they deadlock themselves on some system resource. A recent one I came across is creating an alias type, then declaring a variable of that type, inside the same transaction. Imagine you're trying to run a unit test or pre-deployment test, check for failures, and rollback in any case so that you don't leave any trace of what you'd done. The pattern might look like this:

BEGIN TRANSACTION;
GO
CREATE TYPE EmailAddress FROM VARCHAR(320);
GO
DECLARE @x TABLE (e EmailAddress);
GO
ROLLBACK TRANSACTION;

Or, more likely, a little more complex:

BEGIN TRANSACTION;
GO
CREATE TYPE EmailAddress FROM VARCHAR(320);
GO
CREATE PROCEDURE dbo.foo 
  @param EmailAddress 
AS
BEGIN 
  SET NOCOUNT ON;
  DECLARE @x TABLE (e EmailAddress);
  INSERT @x SELECT @param;
END
GO
DECLARE @x EmailAddress;
SET @x = N'whatever';
EXEC dbo.foo @param = N'whatever';
GO
ROLLBACK TRANSACTION;

The first place I tried this code was SQL Server 2012, and both examples failed with the following error:

Msg 1205, Level 13, State 55, Line 14
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

And there's not much at all to learn from the deadlock graph:

single-tx-deadlock-graph

Stepping back a few years, I recall when I first learned about alias types, back in SQL Server 2000 (when they were called User-Defined Data Types). At that time, this deadlock that I came across more recently would not happen (but this is at least partly because you couldn't declare a table variable with an alias type – see here and here). I did run the following code on SQL Server 2000 RTM (8.0.194) and SQL Server 2000 SP4 (8.0.2039), and it ran just fine:

BEGIN TRANSACTION;
GO
EXEC sp_addtype @typename = N'EmailAddress', @phystype = N'VARCHAR(320)';
GO
CREATE PROCEDURE dbo.foo 
  @param EmailAddress 
AS
BEGIN 
  SET NOCOUNT ON;
  SELECT @param;
END
GO
EXEC dbo.foo @param = N'whatever';
GO
DECLARE @x EmailAddress;
SET @x = N'whatever';
EXEC dbo.foo @param = @x;
GO
ROLLBACK TRANSACTION;

Of course, this scenario wasn't very widespread at the time because, after all, not many people used alias types in the first place. While they may make your metadata more self-documenting and data-definition-like, they are a royal pain if you ever want to change them, which may be a topic for another post.

SQL Server 2005 came around, and introduced new DDL syntax for creating alias types: CREATE TYPE. This didn't really solve the problem with changing the types, it just made the syntax a little cleaner. In RTM, all of the above code samples worked just fine with no deadlocks. In SP4, however, they would all deadlock. Therefore, somewhere between RTM and SP4, they changed the internal handling for transactions that involved table variables using alias types.

Fast forward a few years to SQL Server 2008, where table-valued parameters were added (see a good use case here). This made the use of these types much more prevalent, and introduced another case where a transaction that tried to create and use such a type would deadlock:

BEGIN TRANSACTION;
GO
CREATE TYPE dbo.Items AS TABLE(Item INT);
GO
DECLARE @r dbo.Items;
GO
ROLLBACK TRANSACTION;

I checked Connect, and found several related items, one of them claiming that this issue has been fixed in SQL Server 2008 SP2 and 2008 R2 SP1:

Connect #365876 : Deadlock occurs when creating user-defined data type and objects that use it

What this actually referred to was the following scenario, where simply creating a stored procedure that referenced the type in a table variable would deadlock in SQL Server 2008 RTM (10.0.1600) and SQL Server 2008 R2 RTM (10.50.1600):

BEGIN TRANSACTION;
GO
CREATE TYPE EmailAddress FROM VARCHAR(320);
GO
CREATE PROCEDURE dbo.foo 
  @param EmailAddress 
AS
BEGIN 
  SET NOCOUNT ON;
  DECLARE @x TABLE (e EmailAddress);
  INSERT @x SELECT @param;
END
GO
ROLLBACK TRANSACTION;

However, this does not deadlock in SQL Server 2008 SP3 (10.0.5846) or 2008 R2 SP2 (10.50.4295). So I tend to believe the comments on the Connect item – that this portion of the bug was fixed in 2008 SP2 and 2008 R2 SP1, and has never been an issue in more modern versions.

But this still leaves out the ability to actually put the alias type through any sort of true testing. So my unit tests would succeed as long as all I wanted to do was test that I could create the procedure – forget about declaring the type as a local variable or as a column in a local table variable.

The only way to resolve this is to create the table type before starting the transaction, and explicitly drop it afterward (or otherwise break it up into multiple transactions). This could be extremely cumbersome, or even impossible, to have often automated testing frameworks and harnesses completely change the way they operate to account for this limitation.

So I decided to go through some tests in the initial and most recent builds of all of the major versions: SQL Server 2005 RTM, 2005 SP4, 2008 RTM, 2008 SP3, 2008 R2 RTM, 2008 R2 SP2, 2012 RTM, 2012 SP1, and 2014 CTP2 (and yes, I do have them all installed). I had reviewed several Connect items and various comments that left me wondering which use cases were supported and where, and I had a strange compulsion to find out which aspects of this issue had actually been fixed. I tested various potential deadlock scenarios involving alias types, table variables, and table-valued parameters against all of these builds; the code is as follows:

/* 
  alias type - declare in local table variable 
  always deadlocks on 2005 SP4 -> 2014, except in 2005 RTM
*/

BEGIN TRANSACTION;
GO
CREATE TYPE EmailAddress FROM VARCHAR(320)
GO
DECLARE @r TABLE(e EmailAddress);
GO
ROLLBACK TRANSACTION;


/* 
  alias type - create procedure with param & table var 
  sometimes deadlocks - 2005 SP4, 2008 RTM & SP1, 2008 R2 RTM
*/

BEGIN TRANSACTION;
GO
CREATE TYPE EmailAddress FROM VARCHAR(320);
GO
CREATE PROCEDURE dbo.foo 
  @param EmailAddress 
AS
BEGIN 
  SET NOCOUNT ON;
  DECLARE @x TABLE (e EmailAddress);
  INSERT @x SELECT @param;
END
GO
ROLLBACK TRANSACTION;


/* 
  alias type - create procedure, declare & exec 
  always deadlocks on 2005 SP4 -> 2014, except on 2005 RTM
*/

BEGIN TRANSACTION;
GO
CREATE TYPE EmailAddress FROM VARCHAR(320);
GO
CREATE PROCEDURE dbo.foo 
  @param EmailAddress 
AS
BEGIN 
  SET NOCOUNT ON;
  DECLARE @x TABLE (e EmailAddress);
  INSERT @x SELECT @param;
END
GO
DECLARE @x EmailAddress;
SET @x = N'whatever';
EXEC dbo.foo @param = N'whatever';
GO
ROLLBACK TRANSACTION;


/* obviously did not run these on SQL Server 2005 builds */

/* 
  table type - create & declare local variable 
  always deadlocks on 2008 -> 2014
*/

BEGIN TRANSACTION;
GO
CREATE TYPE dbo.Items AS TABLE(Item INT);
GO
DECLARE @r dbo.Items;
GO
ROLLBACK TRANSACTION;


/* 
  table type - create procedure with param and SELECT 
  never deadlocks on 2008 -> 2014
*/
 
BEGIN TRANSACTION;
GO
CREATE TYPE dbo.Items AS TABLE(Item INT);
GO
CREATE PROCEDURE dbo.foo 
  @param dbo.Items READONLY
AS
BEGIN 
  SET NOCOUNT ON;
  SELECT Item FROM @param;
END
GO
ROLLBACK TRANSACTION;


/* 
  table type - create procedure, declare & exec 
  always deadlocks on 2008 -> 2014
*/
 
BEGIN TRANSACTION;
GO
CREATE TYPE dbo.Items AS TABLE(Item INT);
GO
CREATE PROCEDURE dbo.foo 
  @param dbo.Items READONLY
AS
BEGIN 
  SET NOCOUNT ON;
  SELECT Item FROM @param;
END
GO
DECLARE @x dbo.Items;
EXEC dbo.foo @param = @x;
GO
ROLLBACK TRANSACTION;

And the results reflect my story above: SQL Server 2005 RTM did not deadlock on any of the scenarios, but by the time SP4 rolled around, they all deadlocked. This was corrected for the "create a type and create a procedure" scenario, but none of the others, in 2008 SP2 and 2008 R2 SP1. Here is a table showing all of the results:

Legend SQL Server Version / Build #
SQL 2005 SQL 2008 SQL 2008 R2 SQL 2012 SQL 2014
RTM
9.0.1399
SP4
9.0.5324
RTM
10.0.1600
SP3
10.0.5846
RTM
10.50.1600
SP2
10.50.4295
RTM
11.0.2100
SP1
11.0.3381
CTP2
12.0.1524
Alias Type declare in table var no deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock!
create procedure no deadlock! deadlock! deadlock! no deadlock! deadlock! no deadlock! no deadlock! no deadlock! no deadlock!
create & exec proc no deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock!
Table Type declare local var N/A deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock!
create procedure no deadlock! no deadlock! no deadlock! no deadlock! no deadlock! no deadlock! no deadlock!
create & exec proc deadlock! deadlock! deadlock! deadlock! deadlock! deadlock! deadlock!
http://www.flickr.com/photos/cdrummbks/6310702934/

Conclusion

So, the moral of the story is, there is still no fix for the use case described above, where you want to create a table type, create a procedure or function that uses the type, declare a type, test the module, and roll everything back. In any case, here are the other Connect items for you to look at; hopefully you can vote for them and leave comments describing how this deadlock scenario affects your business directly:

  • Connect #581193 : Creating a table type and using it in the same transaction causes a deadlock
  • Connect #800919 : Problem in Create a Function with TableValue Return Type in transaction with user defined type in table which is created in a same transaction scope
  • Connect #804365 : Deadlock occurs when a user-defined table type is created and used in one transaction
  • I fully expect some clarifications to be added to these Connect items in the near future, though I do not know exactly when they'll get pushed through.