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

Question

In MS SQL Server, I use the global temp table to store session-related information sent by the client, and then I use this information inside the 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 it (depending on whether all previous sessions that were previously used are closed), I do check for the existence of a global temporary the table on the basis of which I create before writing to 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 checking for the existence of the table and the MERGE statement, SQL Server can drop the temporary table if all the sessions that used it before are closed in this exact instance (this really happened in my tests).

Is there a best practice on how to avoid such concurrency problems so that the table is not discarded between checking its existence and its subsequent use?

Answer 1

To begin with, in the long run I will follow Gordon's advice, that is, I will take the necessary steps to enter a regular table in the database to store information about client applications, which should be available in triggers.

But since this was not possible now due to time limitations (it takes several weeks to get the necessary approvals for the new normal table), I came up with a solution to prevent SQL Server from dropping the global table tempo between check its existence and the MERGE .

There is some information about when SQL Server drops the global temp table; my personal tests showed that SQL Server throws the global temporary table at the moment when the session that created it is closed and any other transactions started in other sessions that changed the data in this table.

My solution was to fake data changes in the global temp table before I even checked its existence. If the table exists at that moment, SQL Server will know that it must support it until the current transaction is complete and cannot be discarded after checking its existence. Now the code looks like this (correctly commented out, since this is a kind of 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 "use it at your own peril and risk," it prevents the global temp table from being used in other sessions in its current use, which bothered me the thread.

Thank you all for your time! (from text editing to answers)

Answer 2

The concepts of “global temporary table” and “trigger” are simply not clicked. Tables are persistent data stores, as well as their attributes, including triggers. Temporary tables are deleted when the server is restarted. Why would anyone create a system where the permanent block of code (trigger) depends on the mechanism of temporary joint storage? This is like a recipe for failure.

Instead of a global temporary table, use a real table. If you want, put a useful prefix, for example temp_ in front of the name. If the table is shared by databases, then put it in a database where all the code is available.

Create the table once and leave it there (delete the rows in order) so that the startup code can access it.