SQL Temporary Table Concurrency

[Adam W. Saxton]

We have a few existing stored procedures which create a Global Temporary
Table (##), do some work on the table and then delete the table.

The issue we have is that if our Server application is using these is
running and then our Web application fires off, the Temp table is getting
deleted.

My thought was to change the Global Temporary Table to a local temporary
table (# instead of ##) to minimize the amount of change to these
procedures. My question though is in reference to the ASP.NET Web
application and whether the issue with the global table would still be an
issue with the local table.

I wasn't sure how connection pooling would play into this. I know that a
local temporary table is created for each connection and can only be
reference by that connection but i was wondering if in the ASP.NET
application is that if i call the procedure by two separate users in two
separate sessions if it would use the same connection and thus share the
same local temporary table.

 

[David Browne]

We have a few existing stored procedures which create a Global Temporary
Table (##), do some work on the table and then delete the table.

The issue we have is that if our Server application is using these is
running and then our Web application fires off, the Temp table is getting
deleted.

My thought was to change the Global Temporary Table to a local temporary
table (# instead of ##) to minimize the amount of change to these
procedures. My question though is in reference to the ASP.NET Web
application and whether the issue with the global table would still be an
issue with the local table.

I wasn't sure how connection pooling would play into this. I know that a
local temporary table is created for each connection and can only be
reference by that connection but i was wondering if in the ASP.NET
application is that if i call the procedure by two separate users in two
separate sessions if it would use the same connection and thus share the
same local temporary table.

Each user will have exclusive access to the session so long as they hold the
Connection open.

So no user will see another's data, but you might see what another user left
in the temporary table.

So if the clients did

CREATE TABLE #FOO(I INT)
INSERT INTO #FOO(I) VALUES (1)

Then the second client would get an error trying to create #FOO because it
already existed.
But if each client runs

if not object_id('tempdb..#FOO') is null DROP TABLE #FOO
CREATE TABLE #FOO(I INT)
INSERT INTO #FOO(I) VALUES (1)

It's ok. You just have to account for other users leaving the temporary
table there.

David

 

[Adam W. Saxton]

That's what worries me.

If User A creates #FOO and then User B comes in before User A is done would
User B see some data that User A placed in #Foo and also if User A Drops the
table before User B is done, would User B get an error saying that #Foo does
not exists which i would think that would be true, and that is what was
happening with the Global table.

 

[David Browne]

That's what worries me.

If User A creates #FOO and then User B comes in before User A is done would
User B see some data that User A placed in #Foo and also if User A Drops the
table before User B is done, would User B get an error saying that #Foo does
not exists

No. That would not happen. There will be a seperate #FOO for each session.

which i would think that would be true, and that is what was
happening with the Global table.

That is the difference between regular temporary tables (#) and global
temporary tables (##).

David

 

[bruce barker]

this is not correct.

#temp tables are tied to a spid (sqlconnection). they live until the spid
dies or the spid drops the table.

if you use connection pooling and don't create and drop the #temp table in
one batch (execute), then you can have the problem of two users accessing
the same #temp table (or one deleteing before the other is done).

sqlserver currently has no knowledge of connectiion pooling, future
versions, may impliment a connection reset, so cleanup can be done when the
connection is returned to the pool. this would mean the #temp table would
disappear as soon a conection went into the pool, even on the same page
request.

you can also safely create the #temp in a stored proc, becuase the sp will
delete it at exit, so its never shared.


-- bruce (sqlwork.com)

 

[David Browne]

this is not correct.

#temp tables are tied to a spid (sqlconnection). they live until the spid
dies or the spid drops the table.

if you use connection pooling and don't create and drop the #temp table in
one batch (execute), then you can have the problem of two users accessing
the same #temp table (or one deleteing before the other is done).

Not quite. You are guaranteed to be accessing the same temp table across
batches, so long as you are using the same SqlConnection object. When you
..Close the SqlConnection, the connection will return to the pool, and a
later user can retrieve that connection and "see" the same temp table. But
as I explained, you just have to account for the possibility that the temp
table may already exist in the session.

So it is safe to:
1. Open a "new" connection.
2. Drop the temp table (if it exists)
3. Create the temp table
4. Use the temp table in any number of batches
4a. (best practice) drop the temp table.
5. Close the connection


David