0

For testing purposes, I am creating a user-defined table type and then use it to pass some testing data in a table-valued parameter to a query. However, if I do so in the same transaction, I always get a deadlock error. Unfortunately, I need to do them in the same transaction so that I can rollback the tests' side-effects.

Any suggestions or work-arounds?

geeko
  • 2,649
  • 4
  • 32
  • 59
  • 2
    If it's all happening in one transaction, why not just a table variable? What's the UDTT adding here? – Damien_The_Unbeliever Apr 22 '20 at 12:28
  • You're deadlocking on a table you're creating? Is this a parallelism deadlock? Because, I'm trying to envision any other cause for a deadlock that involves a single transaction, a single thread, and no external processes and I can't. – Grant Fritchey Apr 22 '20 at 12:37
  • I believe I've seen this issue several times historically on MS Connect but can't find it now (since it's retired), and that it was closed as both 'wont fix' and 'by design' - but I don't remember the details. – Cedersved Apr 22 '20 at 12:42
  • See this blog post https://sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock – Cedersved Apr 22 '20 at 12:43
  • Hi @Damien_The_Unbeliever. I do not know how to pass my IEnumerale as a table variable to my query using Dapper. – geeko Apr 22 '20 at 13:02
  • There is no workaround except don't do that in the same transaction. The system transaction that creates the instance of the table in `tempdb` is incompatible with the user transaction. So you need separate code to create it outside your tran and add explicit code to drop it rather than just relying on rollback to sort it out – Martin Smith Apr 22 '20 at 13:02
  • @GrantFritchey It is one thread and Cedersved is correct. – geeko Apr 22 '20 at 13:03

1 Answers1

1

I believe it will work if you create the user defined table type outside of the transaction. Based on your question, I guess this could be an option as the creation of a new user defined table type is unlikely to cause any side-effects - or am I missing something?

  1. Create user defined table type
  2. Begin tran
  3. Rollback tran
  4. Drop user defined table type
Cedersved
  • 1,015
  • 1
  • 7
  • 21
  • Hi Cedersved. The UDTT is only temporary for the testing and I want to roll it back when done. – geeko Apr 22 '20 at 12:55