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

Question

Running SQL Server 2014. I have a stored procedure where I need to write data to a global temporary table. However, it seems that the method OBJECT_ID hangs when the procedure is called inside a transaction.

How do I remove the deadlock without removing the transaction?

Stored procedure:

CREATE PROCEDURE [dbo].[foo]
    @Data [varbinary](max)
WITH ENCRYPTION AS
BEGIN
    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..##TempData') IS NULL
        CREATE TABLE ##TempData
        (
            [ID] [int] IDENTITY(1,1) NOT NULL,
            [Data] [int] NOT NULL UNIQUE
        )

    INSERT INTO ##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 -- This will hang on the call to OBJECT_ID()...
COMMIT TRAN

UPDATE

Here is some C# code that illustrates why I need to have a transaction.

var options = new TransactionOptions();
// options.IsolationLevel = IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.MaximumTimeout;

using (var transaction = new TransactionScope(TransactionScopeOption.Required, options))
{
    // Write to ##TempData. Lengthy operation...
    var foo = Foo(data);

    // These two MUST be inside a transaction!
    var bar = Bar();
    var baz = Baz(); // Consume data in ##TempData

    transaction.Complete();
}

Answer 1

(Hopefully this will be another potential way to look at solving your problem... too long for a comment)

In addition to what was in the comments, I believe that using an actual table might get you somewhat easier of a time to do what you're trying to do (though I'm a bit unclear on what that is still)

if you created your table in this sort of manner:

create table tmpDataThingy (
    connectionUniqueId uniqueIdentifier,
    someOtherColumn int,
    andYetAnother varchar(50)
)

creating the table (the important part is connectionUniqueId) in this manner would allow you to keep track of "Units of work" (probably not the right word) when manipulating data within the table.

Your using statement could become something more like this:

using (var transaction = new TransactionScope(TransactionScopeOption.Required, options))
{
    Guid uniqueId = Guid.NewGuid(); // consider this a "session" identifier for a single instance of the work needed for this call

    // insert your data into tmpDataThingy ensuring connectionUniqueId = uniqueId (the guid created above)
    // consume/manipulate data in tmpDataThingy only for the specified uniqueId
    // remove data from tmpDataThingy for the uniqueId

    transaction.Complete();
}

Doing the above you'll have no potential issues with the table not existing, no potential of multiple users calling the same function with conflicting data due to the uniqueId being generated differently for each call to the function, etc.