MS SQL Server-安全并发使用全局临时表? [英] MS SQL Server - safe concurrent use of global temp table?

问题描述

在MS SQL Server中，我使用全局临时表存储客户端传递的与会话相关的信息，然后在触发器中使用该信息.

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);

问题是，在检查表存在与MERGE语句之间，如果之前使用它的所有会话恰好在该确切实例中关闭，则SQL Server可能会删除临时表(这实际上发生在我测试).

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?

推荐答案

我将首先说，从长远来看，我将遵循Gordon的建议，即，我将采取必要的步骤在表格中引入一个普通表.数据库来存储需要在触发器中访问的客户端应用程序信息.

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.

但是，由于时间限制，现在真的不可能(需要花费数周的时间才能获得新的普通表的正式批准)，因此我想出了一种防止SQL Server在两次调用之间删除全局临时表的解决方案.检查其存在和MERGE语句.

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.

这里有一些有关SQL Server何时删除全局临时表的信息.我的个人测试表明，SQL Server在创建会话的会话关闭并在其他会话中启动的其他事务(更改了该表中的数据)完成后立即删除了全局临时表.

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.

我的解决方案是在检查全局临时表上的数据之前就对它们进行伪造.如果该表当时存在，则SQL Server将知道它需要保留该表，直到当前事务完成为止，并且在检查其存在之后不能再将其删除.该代码现在看起来像这样(正确注释，因为它有点像hack):

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)

这篇关于MS SQL Server-安全并发使用全局临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！