Re: Global Temp Table & Multiple Users

From: Scott Morris (bogus_at_bogus.com)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 15:25:55 -0400

Force each connection to do all of the processing related to the table
within a transaction, and at the start of the transaction get an exclusive
lock on the table. That will prevent mutliple connections from interfering
with the population and use of the table - but it will serialize them. Your
ideal design will require a pessimistic locking approach where the
application does some sort of checkout / reservation logic - nothing in sql
server that I know of that will do this for you.

The best approach is to avoid the global temp table, as Adam indicated.

"Steven" <anonymous@discussions.microsoft.com> wrote in message
news:0d9001c49c10$bc124420$a301280a@phx.gbl...
> I have a stored procedure that *must* create a global
> temporary table (##myTable), but the problem is that
> there are multiple users which could be using this stored
> procedure.
>
> What best practices are there for dealing with this
> problem?
>
> Ideally, I want to queue up the execution if multiple
> users all run the proc at the same time, rather than
> return an IsRunning status of sorts and then waiting in
> the application.
>
> Any suggestions?
>
> NOTE: I cannot change the name of the table dynamically
> (i.e. '##myTable' + @UserID) because of how I have to
> deal with the data in the procedure (and nested
> procedures).
>
>

Relevant Pages