SQL Server: Concurrency when using temporary tables within stored procedures

Question

I have an ASP.NET MVC app published on IIS Server. I am using web gardening here, I mean, application pool has more than one worker processes to attend incoming requests. Also this app is being used by a lot of users clients.

This app calls a SP which uses some local temporary tables (#example). As an example:

BEGIN

if OBJECT_ID(N'tempdb..#MyTempTable') IS NOT NULL
BEGIN
  DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
   someField int,
   someFieldMore nvarchar(50)
)

... Use of temp table here
... And then drop table again at the end..

DROP TABLE #MyTempTable

END

I am worried about concurrency, for example, what happens if a user client calls the stored procedure while another previous call is being running at the same time? Can be concurrency issues here?

Temp tables are local to the scope they are created and automatically dropped when they go out-of-scope (you don't need the `DROP` at the end of the proc). Local temp tables will not conflict with other sessions. The only concurrency concern with heavy use of temp tables is from a DBA perspective, where multiple tempdb files can reduce allocation page contention. BTW, SQL 2008 is unsupported and there are performance improvements in later versions, such as temp table meta-data caching. — Dan Guzman, Sep 18 '20 at 09:16

score 0 · Answer 1 · edited Oct 08 '20 at 20:22

In IIS (including most web server), use threads to process requests. Each request will be executed in a new thread which is created in app pool. Unless shared resources, threads will not affect each other.

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login doesn't matter. And if you are using Connection Pooling that also won't matter. Local temporary objects (Tables most often, but also stored procedures) are safe from being seen by other sessions.

Even multiple threads(also requests) want to use a connection and execute stored procedure, the same connection can not be reused by connection pool. It means no danger. Explained in this thread.

Similarly, one thread uses the connection and execute stored procedure, it will not have effect. All calls are using the same stored procedure. They will be queued in sequence until the previous call is executed.

SQL Server: Concurrency when using temporary tables within stored procedures

1 Answers1