Are temporary tables thread safe?

Question

I am using SQL Server 2000, and many of the stored procedures make extensive use of temporary tables. There is a lot of traffic in the database, and I'm worried about thread safety when creating and deleting temporary tables.

Suppose I have a stored procedure that creates several temporary tables, it can even join temporary tables to other temporary tables, etc. And it also allows you to say that two users are simultaneously executing a stored procedure.

• Is it possible for one user to run sp and create a temporary table named #temp, and another user to run the same sp, but stop because a table named #temp already exists in the database?

• How about if the same user executes the same stored procedure twice in the same connection?

• Are there any other strange scenarios that can cause mutual requests from two users?

Answer 1

In the first case, no, this is impossible, because #temp is a local temporary table and therefore is not visible to other connections (it was assumed that your users use separate database connections). The name of the temporary table is assigned to the alias of any name that is created, and you refer to this when referring to your local temp table.

In your case, since you create a local temp table in the stored procedure, the temporary table will be deleted when you exit the procedure procedure (see the "Remarks" section).

For the second case, yes, you will get this error because the table already exists and the table lasts as long as the connection does. If so, then I recommend that you check the availability of the table before trying to create it.

Answer 2

Local temporary tables (with one #) are created with an identifier at the end of them, which makes them unique; multiple callers (even with the same name) should never overlap.

(Try it: create the same temporary table from two connections of the same name. Then query tempdb.dbo.sysobjects to see the actual tables created ...)

Answer 3

Local temporary tables are thread safe because they exist only in the current context. Do not confuse the context with the current connection (from MSDN : "The local temporary table created in the stored procedure is automatically discarded when the stored procedure is completed"), the same connection can safely call two or more times the stored procedure that creates the local tempo table (for example , #TMP ).

You can verify this behavior by executing the following stored procedure from two connections. This SP will wait 30 seconds, so we can be sure that the two threads will work on their own versions of the #TMP table at the same time:

 CREATE PROCEDURE myProc(@n INT) AS BEGIN RAISERROR('running with (%d)', 0, 1, @n); CREATE TABLE #TMP(n INT); INSERT #TMP VALUES(@n); INSERT #TMP VALUES(@n * 10); INSERT #TMP VALUES(@n * 100); WAITFOR DELAY '00:00:30'; SELECT * FROM #TMP; END; 

Answer 4

Temp tables are tied to a session, so if different users start your procedure at the same time, there is no conflict ...

Answer 5

Short answer:

Isolation of temporary tables is guaranteed for each request, and there is nothing to worry about, neither about the stream, nor about the locks, nor about simultaneous access.

I'm not sure why the answers here talk about the meaning of “connections” and threads, as these are programming concepts, while query isolation is handled at the database level.

Local temporary objects are shared by the session on the SQL server. If you have two requests running at the same time, then they are two completely separate sessions and will not interact with each other. Logging in does not matter, for example, if you use the same connection string using ADO.NET (this means that several parallel queries will use the same SQL "login" query), your queries will still be executed in separate sessions. The connection pool is also irrelevant. Local temporary objects (tables and stored procedures) are completely safe for viewing by other sessions.

To clarify how this works; while your code has one common name for local temporary objects, SQL Server adds a unique line for each object in each session to keep them separate. You can see this by doing the following in SSMS:

 CREATE TABLE #T (Col1 INT) SELECT * FROM tempdb.sys.tables WHERE [name] LIKE N'#T%'; 

You will see the following for the name:

T _______________ 00000000001F

Then, without closing this query tab, open a new query tab and paste the same query and run it again. You should now see something like the following:

T _______________ 00000000001F

T _______________ 000000000020

So, every time your code references #T, SQL Server translates it into the corresponding name based on the session. Separation is carried out automatically, magically.

Answer 6

Temp tables are created only in the context of the query or proc that creates them. Each new request receives a context in the database that does not contain temporary temp-tables of requests. Thus, name collision is not a problem.

Answer 7

If you look in the temps database, you can see the temporary tables there, and they have system-generated names. So, besides the usual dead ends, you should be fine.

Answer 8

if you do not use two pound signs ## temp, the temp table will be local and exist only for this local connection to the user

Answer 9

First make sure you use real temporary tables, do they start in C # or ##? If you create actual tables on the fly, and then re-drop them and re-create them, you really will have problems with concurrent users. If you create global temporary tables (those starting with C ##), you may also have problems. If you don't want concurrency problems to use local temp tables (they start with C #). In addition, it’s good practice to explicitly close them at the end of proc (or when proc is no longer needed if you are talking about long multi-step procedures) and check for the existence (and if so) before creating,