Following a single-transaction deadlock across SQL Server versions

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:

And there's not much at all to learn from the 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:

		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
	create procedure
	create & exec proc
Table Type	declare local var	N/A
	create procedure
	create & exec proc