2

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?

Community
  • 1
  • 1
Bob Tway
  • 8,793
  • 12
  • 72
  • 144
  • you cross check whether table is already exists in db or not by `if(OBJECT_ID('tempdb..#Exclusions')) is not null drop table #Exclusions` – Ganesh_Devlekar Oct 24 '14 at 15:09
  • 1
    These two errors are possibly related differently than is being assumed here. Rather than the temp table error causing the transaction error, it could be that the transaction error is leading to the temp table error, or some issue is causing them both. Temp table _are_ thread safe: I have worked on a system with 5000+ transactions _per second_ using many temp tables and have never seen this error. Also, connections aren't shared and pools are unique to the security context and connection string. What else is going on in these procs? Do you use a .Net transaction? XACT_ABORT? – Solomon Rutzky Oct 24 '14 at 15:25
  • @srutzky Good point - I've never seen this happen before either, and have worked on some fairly intensive systems. No, there are no .net transactions being used. XACT_ABORT is off. – Bob Tway Oct 24 '14 at 15:27
  • 1
    Are you using TRY / CATCH in the proc? setting a different TRANSACTION ISOLATION LEVEL? Using nested transactions in the proc? Any nested proc calls? Is there another spot in this (or any sub-procs, if there are any) that tries to create the same temp table name? I think something is happening to a transaction that is cascading to the temp table somehow. – Solomon Rutzky Oct 24 '14 at 15:34
  • @srutzky Yes, there's a TRY CATCH. We're not changing the isolation level. The sproc contains a cursor (it's not my sproc, don't blame me!) - the temp table is created outside the cursor, truncated inside it and then populated. – Bob Tway Oct 24 '14 at 15:44
  • @MattThrower : don't apologize. I am not anti-CURSOR, am just pro-proper-usage of them ;-). Is the temp table created before the TRY or inside of? Is it explicitly dropped anywhere? If so, inside the catch and/or after END CATCH? Is the cursor selecting from a temp table or created as STATIC? – Solomon Rutzky Oct 24 '14 at 15:48
  • @srutzky It's created inside the TRY. It's not explicitly dropped anywhere - if I did, and this isn't thread safe, then we're going to have a different problem. The cursor is not STATIC nor is it created from temp table data. I am leaving the office shortly so will have to drop this conversation, but I very much appreciate you trying to help. – Bob Tway Oct 24 '14 at 15:50
  • @MattThrower : np. I asked about the cursor because I am wondering if, due to it not being STATIC, it somehow fails with an error that disrupts the transactions. Can you declare it as STATIC? It would reduce locking/contention on the tables it is selecting from. Also, with a sensitive (i.e. _not_ STATIC) cursor, you can have "missing" rows. Is there an explicit transaction in the proc? created inside or before the TRY? – Solomon Rutzky Oct 24 '14 at 15:56

1 Answers1

2

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

DECLARE @t TABLE

Edmund Covington
  • 517
  • 6
  • 17
  • Thanks - it's useful to know that table variables are only accessible to their creating procedures. But I don't really want to have to go through all the threadable sprocs and replace my temp tables with table variables. Plus some of them have indexes, which I can't put on a table variable. I'd like to understand this behaviour a bit better to see if there's another solution. – Bob Tway Oct 24 '14 at 15:04