Deadlock occurs when a user-defined table type is created and used in one transaction - by Jon Seigel

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 804365 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 10/2/2013 1:16:16 PM
Access Restriction Public


If a user-defined table type is created within a transaction and a subsequent statement within that transaction attempts to use the type, the latter statement creates an internal deadlock, and the transaction is aborted.

I tested against the following versions, all of which produce the same behaviour:

2008 RTM, SP2
2008 R2 RTM, SP1
2012 RTM, SP1
Sign in to post a comment.
Posted by Jon Seigel on 11/25/2013 at 9:07 PM

Should I report that UDDTs are broken because they don't follow scalar variable semantics?

    CREATE TYPE [dbo].[blah] FROM int;
    EXEC sys.sp_executesql N'DECLARE @a [dbo].[blah];';

(This runs successfully on 2008 R2 RTM.)

Now can we please get on with a fix for UDTTs?
Posted by Manbeen [MSFT] on 11/25/2013 at 6:23 PM
Thanks for submitting this feedback. The behavior that you have described is by-design and is consistent with table variable semantics.

A table variable behaves like a local variable. It has a well-defined scope - which is the function, stored procedure, or batch that it is declared in. Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, since table variables have a limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.

Removing the table variable declaration outside the transaction will fix the issue that you are running into:

CREATE TYPE [dbo].[blah2] AS table(id int);

DECLARE @a [dbo].[blah2];

Thanks for your continued support in improving our products.