CODEWITHSUNDEEP
c#sql-servermultithreadingstored-procedures
Bob Tway
Bob Tway

Reputation: 9603

Can multithreaded stored procedures access each other's temp tables?

I'm primarily a software developer, but like most software developers I have to dip my toes into SQL from time to time. It was my understanding that when you ran a stored procedure, any variables and temp tables created in that procedure belonged to that run where isolated from the others, and disposed of at the end of execution.

In my current application there's a Windows service that picks up several "jobs" from a queue at a time and executes a stored procedure for each. It creates a new thread for each job and runs them simultaneously. This is the code that runs the job:

var job = (Job)e.Argument;
rRepository rThread = new rRepository(); 

try
{
    rThread.spJob(job.JobID);
}
catch (Exception ex)
{
    logging.LogError(ex, "Error Running SP", job.JobID, true);
}

The important thing to note is it instantiates a new rRepository each time. It's Entity Framework, so that should mean a new context/connection for each.

This has run fine for ages, and never given us any problem. But today I fired off a whole lot of these at once during testing, and got this:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
There is already an object named '#Exclusions' in the database.

So #Exclusions is a temp table that's used inside the stored procedure. It looks like these simultaneously running procedures all have access to each other's temp tables.

I found this answer, which suggests that temp tables aren't thread safe: Are temporary tables thread-safe? - but only when multiple users are using the same connection. In my case I've got the same connection string, but multiple contexts. How come I'm having threading issues?

Upvotes: 2

Views: 562

Answers (1)

Edmund Covington
Edmund Covington

Reputation: 521

You can create table variables which are only available to the stored procedure that created them:

DECLARE @t TABLE

Upvotes: 2

Related Questions