Can I create a user defined table type and use it in the same transaction?

Question

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?

Answer 1

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

Answer 2

I was able to reproduce this. The deadlock graph is quite curious :

<deadlock-list>
  <deadlock victim="process47f948">
    <process-list>
      <process id="process47f948" taskpriority="0" logused="0" waitresource="METADATA: database_id = 2 USER_TYPE(user_type_id = 257)" waittime="3607" ownerId="14873" transactionname="@myPK" lasttranstarted="2013-11-06T13:23:12.177" XDES="0x80f6d950" lockMode="Sch-S" schedulerid="1" kpid="2672" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-11-06T13:23:12.167" lastbatchcompleted="2013-11-06T13:23:12.163" clientapp="Microsoft SQL Server Management Studio - Query" hostname="xxxxx" hostpid="5276" loginname="xxxxx\xxxxx" isolationlevel="read committed (2)" xactid="14867" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
        <executionStack>
          <frame procname="adhoc" line="2" sqlhandle="0x010002002d9fe3155066b380000000000000000000000000">
declare @myPK dbo.IntIntSet;     </frame>
        </executionStack>
        <inputbuf>

declare @myPK dbo.IntIntSet;
    </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <metadatalock subresource="USER_TYPE" classid="user_type_id = 257" dbid="2" id="lock8009cc00" mode="Sch-M">
        <owner-list>
          <owner id="process47f948" mode="Sch-M" />
        </owner-list>
        <waiter-list>
          <waiter id="process47f948" mode="Sch-S" requestType="wait" />
        </waiter-list>
      </metadatalock>
    </resource-list>
  </deadlock>
</deadlock-list>

It looks to me like a bug and I would recommend you open a connect item for it.

---

To get around your immediate problem you can use tSQLt.NewConnection (I assume you are using tSQLt)

use tempdb

begin tran
go
EXEC tSQLt.NewConnection '
CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
';
go

declare @myPK dbo.IntIntSet;
go

rollback

---

I still don't understand where the need to create a table type on the fly is coming from and I assume you are over-complicating your test. Send me an email if you want to discuss.

Answer 3

Unless someone knows different, I don't think there's a way to do this in a single transaction. I don't think this is a bug.

First, you need to take a schema modification lock (Sch-M) when you create the type. Since you don't commit the transaction, the lock stays open. Then you try to declare a variable to that type in the same transaction. This tries to take a Schema Stability lock (Sch-S). Those two types are incompatible simultaneously on the same object. Since they're in the same transaction, SQL treats it as a deadlock because the Sch-S can never be granted while the transaction is open.

Run each batch one at a time, and select against sys.dm_tran_locks as soon as you try to declare the variable. You'll see the same process holding an Sch-M and waiting for an Sch-S on the same object.