Sql-server – create a user defined table type and use it in the same transaction

deadlocksql serversql-server-2008sql-server-2012

When I execute the following (in management studio, GO will separate the commands into batches)

use tempdb

begin tran
go

CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
go

declare @myPK dbo.IntIntSet;
go

rollback

I get a deadlock error message. My process has deadlocked with itself. I've seen this behaviour in 2008, 2008R2 and 2012.

Is there a way to use my newly created type inside the same transaction it was created?

Best Answer

This has been reported no less than four times (but all traces have been removed from the WayBack Machine since Connect was murdered). This one was closed as fixed:

  • connect.microsoft.com/SQLServer/feedback/details/365876/

But that wasn't true. (Also look at the workarounds section - the workaround I suggested is not always going to be acceptable.)

This one was closed as by design / won't fix:

  • connect.microsoft.com/SQLServer/feedback/details/581193/

These two are newer and still active:

  • connect.microsoft.com/SQLServer/feedback/details/800919/ (now closed as Won't Fix)
  • connect.microsoft.com/SQLServer/feedback/details/804365/ (now closed as By Design)

Until Microsoft can be convinced otherwise, you're going to have to find a workaround - just have all the types deployed before running your test, or break it up into multiple tests.

I will try to get confirmation from my contacts about what Umachandar meant by fixed in the earliest item, because obviously that conflicts with later statements.

UPDATE #1 (of, hopefully, exactly 2)

The original bug (that was closed as fixed) involved alias types, but not of type TABLE. It was reported against SQL Server 2005, which obviously didn't have table types and TVPs. It seems UC reported that the bug with non-table alias types was fixed based on how they handle internal transactions, but it did not cover a similar scenario later introduced with table types. I am still waiting on confirmation of whether that original bug should have ever been closed as fixed; I have suggested that all four be closed as by design. This is partly because it is kind of how I expected it to work, and partly because I get the sense from UC that "fixing" it to work in a different way is extremely complex, could break backward compatibility, and would be helpful in a very limited number of use cases. Nothing against you or your use case, but outside of test scenarios I'm not inclined to believe there is much value in this actually working.

UPDATE #2

I've blogged about this issue:

http://www.sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock

Related Question