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();
}

When you try in parallel with WAITFOR DELAY. Second connection will insert the data into global temporary table. But after 20 secs only you could able to see the data of FIRST and SECOND connection in GT table. — knkarthick24, Jul 22 '15 at 12:37
what exactly are you hoping will happen? The first connection is holding a lock onto the table due to the active transaction. The second table can't access the resource that is the global temp table until it has been released from the first connection's transaction, at least that is what seems like would happen. — Kritner, Jul 22 '15 at 12:38
I need to have a transaction in order for subsequent SPs to have access to the newly created temporary table. — l33t, Jul 22 '15 at 12:41
@Dunno, global temporary tables are only live through the session. Since I'm using the connection pool to allow for "connect, do stuff, disconnect" logic, the transaction is indeed needed. — l33t, Jul 22 '15 at 12:42
@l33t why not put the entirety of the logic into a single connection, transaction and stored procedure if that's the case? Global temporary tables are risky enough without trying to mix in multiple connections and holding a transaction open for *hopefully* the entire duration of the subsequent procs that are called to manipulate the global table data. — Kritner, Jul 22 '15 at 12:44
@Kritner, see my update. Are you saying it would be better to create a normal table to hold my temporary data? — l33t, Jul 22 '15 at 12:48
@l33t it might, what do you suppose would happen to your function if multiple users were to call it subsequently? There could only be one global temp table for the entire application, so would you be worried about overlapping/conflicting data? Additionally, I can't remember exactly where I've read this, but attempting to manage transactions in both c# and sql for the same process has its own set of issues. — Kritner, Jul 22 '15 at 12:50
@l33t this sounds like a case of the [XY Problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). You have a problem with X, think Y is the solution, so you ask about Y when you run into trouble. What is the actual *use case* you are trying to solve? ETL, OLTP, analytical, something else? Eg, storing data into a temporary table that you aren't going to use until later doesn't make much sense - just wait to generate the data until you need it. So why are you doing this? — Panagiotis Kanavos, Jul 22 '15 at 12:54
Also nested transactions is asking for trouble. Holding a transaction open for 20 seconds is disastrous - this also holds any locks acquired by any statement that uses the same connection — Panagiotis Kanavos, Jul 22 '15 at 12:56
A (normal, non-global) temporary table created within a stored procedure is dropped when the stored procedure ends - *but*, a stored procedure *can* access a temporary table created outside of it - is it not feasible to create the (non-global) temporary table directly, then invoke `Foo` to populate it, then call `Bar` and `Baz`? — Damien_The_Unbeliever, Jul 22 '15 at 13:35
@PanagiotisKanavos, the 20-second transaction is for demonstration purposes. I need to upload large amounts of data and insert that together with complicated business metadata (partially defined by the client). That implies the need for a transaction - and to minimize the transaction time I want to upload the data before any locks are acquired. Also, "read uncommitted" cannot be used in this case. — l33t, Jul 22 '15 at 13:36

score 2 · Accepted Answer · answered Jul 22 '15 at 12:59

(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.

I solved it using a regular table. I suppose global temporary tables are not suitable for my use case. — l33t, Jul 22 '15 at 14:01

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

UPDATE

1 Answers1