Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why are you doing this inside a transaction? Are you hoping for a 'temporary' UDT? –  Max Vernon Nov 6 '13 at 17:47
2  
I knew I would get this question. It's part of an integration test. The integration test framework performs everything in a single transaction. –  Michael J Swart Nov 6 '13 at 17:49
1  
An obvious workaround would be to create the types necessary for the test prior to executing the test. Clearly, that doesn't help you automate the testing, however. –  Max Vernon Nov 6 '13 at 17:52
    
@MichaelJSwart, could you elaborate a little more, what you are trying to achieve? Table types are so restrictive,that I can't quite see were you are going with this. –  Sebastian Meine Nov 6 '13 at 18:05
1  
FYI I blogged about this sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock –  Aaron Bertrand Nov 13 '13 at 14:05

3 Answers 3

up vote 10 down vote accepted

This has been reported no less than four times. This one was closed as fixed:

http://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:

http://connect.microsoft.com/SQLServer/feedback/details/581193/

These two are newer and still active:

http://connect.microsoft.com/SQLServer/feedback/details/800919/

http://connect.microsoft.com/SQLServer/feedback/details/804365/

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

share|improve this answer
    
Thanks Aaron... This answers my question: Can I? ==> No, I can't –  Michael J Swart Nov 6 '13 at 18:59
    
@MichaelJSwart Yeah, sorry about that, eh? :-) –  Aaron Bertrand Nov 6 '13 at 18:59
    
I see you found my bug report. :) We hit this in our database deployment... They fixed the bug where the type was used in a function/sproc parameter in (going from memory here) 2008 SP2 and 2008 R2 SP1, so we bumped our minimum version requirement, but those versions (and later) still won't let you use the type if it's declared in the same transaction. You can imagine how annoyed I was when I found that out... –  Jon Seigel Nov 6 '13 at 23:30
    
Thanks @Jon, I am mid-conversation about this and will update what I've found soon. –  Aaron Bertrand Nov 6 '13 at 23:37
2  
Thanks for following up. We already worked around the issue here by issuing an extra COMMIT between chunks of work, but of course that wasn't ideal. –  Jon Seigel Nov 11 '13 at 16:17

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.

share|improve this answer
2  
Thanks for your help Sebastian. Unfortunately, I'm not using tSQLt. You don't understand where the need to create a type on the fly comes from because I didn't explain it. I'm not overcomplicating things, but I don't feel the need to demonstrate it. –  Michael J Swart Nov 6 '13 at 18:48
    
Well, look at the tSQLt source code of how tSQLt.NewConnection is implemented. It's fairly straight forward and should work in your framework too. –  Sebastian Meine Nov 6 '13 at 18:58
1  
Any attempt to create the type and then use it in the same transaction results in deadlock (see my bug report, linked in Aaron's post -- last link); this workaround won't work (well, assuming it doesn't do something dumb like commit an open transaction before executing the input statement). –  Jon Seigel Nov 6 '13 at 23:37

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.

share|improve this answer
3  
The types are incompatible, but I thought that the same process wouldn't have to wait on itself. For example, I can add a column to a table and then use it in the same transaction. –  Michael J Swart Nov 6 '13 at 18:52
3  
The lock manager should figure out that the process is holding a (actually stronger) lock on the resource already and the process should not have to wait. You can after all first update a row and then read it back in too - same situation. –  Sebastian Meine Nov 6 '13 at 18:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.