Home Dashboard Directory Help
Search

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


Status: 

Resolved
 as By Design Help for as By Design


4
0
Sign in
to vote
Type: Bug
ID: 804365
Opened: 10/2/2013 1:16:16 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

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
Details
Sign in to post a comment.
Posted by Jon Seigel on 11/25/2013 at 9:07 PM
lol

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

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

(This runs successfully on 2008 R2 RTM.)


Now can we please get on with a fix for UDTTs?
Posted by Microsoft 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);

BEGIN TRANSACTION;
GO
DECLARE @a [dbo].[blah2];
ROLLBACK;
GO

Thanks for your continued support in improving our products.
Sign in to post a workaround.