MS SQL Server - safe concurrent use of global temp table?

Question

In MS SQL Server, I'm using a global temp table to store session related information passed by the client and then I use that information inside triggers.

Since the same global temp table can be used in different sessions and it may or may not exist when I want to write into it (depending on whether all the previous sessions which used it before are closed), I'm doing a check for the global temp table existence based on which I create before I write into it.

IF OBJECT_ID('tempdb..##VTT_CONTEXT_INFO_USER_TASK') IS NULL
  CREATE TABLE ##VTT_CONTEXT_INFO_USER_TASK (
    session_id    smallint,
    login_time    datetime,
    HstryUserName VDT_USERNAME,
    HstryTaskName VDT_TASKNAME,
  )

MERGE ##VTT_CONTEXT_INFO_USER_TASK As target
USING (SELECT @@SPID, @HstryUserName, @HstryTaskName) as source (session_id, HstryUserName, HstryTaskName)
ON (target.session_id = source.session_id)
WHEN MATCHED THEN
  UPDATE SET HstryUserName = source.HstryUserName, HstryTaskName = source.HstryTaskName
WHEN NOT MATCHED THEN
  INSERT VALUES (@@SPID, @LoginTime, source.HstryUserName, source.HstryTaskName);

The problem is that between my check for the table existence and the MERGE statement, SQL Server may drop the temp table if all the sessions which were using it before happen to close in that exact instance (this actually happened in my tests).

Is there a best practice on how to avoid this kind of concurrency issues, that a table is not dropped between the check for its existence and its subsequent use?

Answer 1

The notion of "global temporary table" and "trigger" just do not click. Tables are permanent data stores, as are their attributes -- including triggers. Temporary tables are dropped when the server is re-started. Why would anyone design a system where a permanent block of code (trigger) depends on a temporary shared storage mechanism? It seems like a recipe for failure.

Instead of a global temporary table, use a real table. If you like, put a helpful prefix such as temp_ in front of the name. If the table is being shared by databases, then put it in a database where all code has access.

Create the table once and leave it there (deleting the rows is fine) so the trigger code can access it.

Answer 2

I'll start by saying that, on the long term, I will follow Gordon's advice, i.e. I will take the necessary steps to introduce a normal table in the database to store client application information which needs to be accessible in the triggers.

But since this was not really possible now because of time constrains (it takes weeks to get the necessary formal approvals for a new normal table), I came up with a solution for preventing SQL Server from dropping the global temp table between the check for its existence and the MERGE statement.

There is some information out there about when a global temp table is dropped by SQL Server; my personal tests showed that SQL Server drops a global temp table the moment the session which created it is closed and any other transactions started in other sessions which changed data in that table are finished.

My solution was to fake data changes on the global temp table even before I check for its existence. If the table exists at that moment, SQL Server will then know that it needs to keep it until the current transaction finishes, and it cannot be dropped anymore after the check for its existence. The code looks now like this (properly commented, since it is kind of a hack):

-- Faking a delete on the table ensures that SQL Server will keep the table until the end of the transaction
-- Since ##VTT_CONTEXT_INFO_USER_TASK may actually not exist, we need to fake the delete inside TRY .. CATCH
-- FUTURE 2016, Feb 03: A cleaner solution would use a real table instead of a global temp table. 
BEGIN TRY
  -- Because schema errors are checked during compile, they cannot be caught using TRY, this can be done by wrapping the query in sp_executesql
  DECLARE @QueryText NVARCHAR(100) = 'DELETE ##VTT_CONTEXT_INFO_USER_TASK WHERE 0 = 1'
  EXEC sp_executesql @QueryText
END TRY
BEGIN CATCH
-- nothing to do here (see comment above)
END CATCH

IF OBJECT_ID('tempdb..##VTT_CONTEXT_INFO_USER_TASK') IS NULL
  CREATE TABLE ##VTT_CONTEXT_INFO_USER_TASK (
    session_id    smallint,
    login_time    datetime,
    HstryUserName VDT_USERNAME,
    HstryTaskName VDT_TASKNAME,
  )

MERGE ##VTT_CONTEXT_INFO_USER_TASK As target
USING (SELECT @@SPID, @HstryUserName, @HstryTaskName) as source (session_id, HstryUserName, HstryTaskName)
ON (target.session_id = source.session_id)
WHEN MATCHED THEN
  UPDATE SET HstryUserName = source.HstryUserName, HstryTaskName = source.HstryTaskName
WHEN NOT MATCHED THEN
  INSERT VALUES (@@SPID, @LoginTime, source.HstryUserName, source.HstryTaskName);

Although I would call it a "use it at your own risk" solution, it does prevent that the use of the global temp table in other sessions affects its use in the current one, which was the concern that made me start this thread.

Thanks all for your time! (from text formatting edits to replies)