sql server - How to manage global temporary tables in SP run inside transaction? -

running sql server 2014. have stored procedure need write data global temporary table. however, seems method object_id hangs when procedure called inside transaction.

how remove deadlock without removing transaction?

stored procedure:

create procedure [dbo].[foo]     @data [varbinary](max) encryption begin     set nocount on      if object_id('tempdb..##tempdata') null         create table ##tempdata         (             [id] [int] identity(1,1) not null,             [data] [int] not null unique         )      insert ##tempdata ([data]) select @data end go 

first connection:

begin tran exec [foo] @data = 1 waitfor delay '00:00:20' commit tran  drop table ##tempdata 

then in second connection:

begin tran exec [foo] @data = 2 -- hang on call object_id()... commit tran 

update

here c# code illustrates why need have transaction.

var options = new transactionoptions(); // options.isolationlevel = isolationlevel.readcommitted; options.timeout = transactionmanager.maximumtimeout;  using (var transaction = new transactionscope(transactionscopeoption.required, options)) {     // write ##tempdata. lengthy operation...     var foo = foo(data);      // these 2 must inside transaction!     var bar = bar();     var baz = baz(); // consume data in ##tempdata      transaction.complete(); } 

(hopefully potential way @ solving problem... long comment)

in addition in comments, believe using actual table might easier of time you're trying (though i'm bit unclear on still)

if created table in sort of manner:

create table tmpdatathingy (     connectionuniqueid uniqueidentifier,     someothercolumn int,     andyetanother varchar(50) ) 

creating table (the important part connectionuniqueid) in manner allow keep track of "units of work" (probably not right word) when manipulating data within table.

your using statement become more this:

using (var transaction = new transactionscope(transactionscopeoption.required, options)) {     guid uniqueid = guid.newguid(); // consider "session" identifier single instance of work needed call      // insert data tmpdatathingy ensuring connectionuniqueid = uniqueid (the guid created above)     // consume/manipulate data in tmpdatathingy specified uniqueid     // remove data tmpdatathingy uniqueid      transaction.complete(); } 

doing above you'll have no potential issues table not existing, no potential of multiple users calling same function conflicting data due uniqueid being generated differently each call function, etc.