Are temporary tables thread-safe?

Question

I'm using SQL Server 2000, and many of the stored procedures it use temp tables extensively. The database has a lot of traffic, and I'm concerned about the thread-safety of creating and dropping temp tables.

Lets say I have a stored procedure which creates a few temp tables, it may even join temp tables to other temp tables, etc. And lets also say that two users execute the stored procedure at the same time.

• Is it possible for one user to run the sp and which creates a temp table called #temp, and the another user runs the same sp but gets stopped because a table called #temp already exists in the database?

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

• Are there any other weird scenarios that might cause two users queries to interfere with one another?

Answer 1

Temp tables are tied to the session, so if different users run your procedure simultaneously there's no conflict...

Answer 2

Temp tables are created only in the context of the query or proc that creates them. Each new query gets a context on the database that is free of other queries' temp tables. As such, name collision is not a problem.

Answer 3

If you look in the temps database you can see the temporary tables there, and they have system generated names. So other than regular deadlocks you should be OK.

Answer 4

For the first case, no, it is not possible, because #temp is a local temporary table, and therefore not visible to other connections (it's assumed that your users are using separate database connections). The temp table name is aliased to a random name that is generated and you reference that when you reference your local temp table.

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

Answer 5

Local-scope temp tables (with a single #) are created with an identifier at the end of them that makes them unique; multiple callers (even with the same login) should never overlap.

(Try it: create the same temp table from two connections and same login. Then query tempdb.dbo.sysobjects to see the actual tables created...)

Answer 6

unless you use two pound signs ##temp the temp table will be local and only exists for that local connection to the user

Answer 7

First let's make sure you are using real temp tables, do they start with # or ##? If you are creating actual tables on the fly and then dropping and recreating them repeatedly, you will indeed have problems with concurrent users. If you are createing global temp tables (ones that start with ##) you can also have issues. If you do not want concurrency issues use local temp tables (They start with #). It is also a good practice to explicitly close them at the end of the proc (or when they are no longer needed by the proc if you are talking long multi-step procs) and to check for existence (and drop if so) before creating.