MS SQL Server语言 - 安全并发使用全局临时表

Question

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

由于同一个全局临时表

可以在不同的会话中使用，并且当我想写入它时它可能存在也可能不存在（取决于之前使用它的所有先前会话是否都已关闭），因此我正在检查全局临时表的存在，在写入之前我基于该表创建全局临时表。

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 可能会删除临时表（这实际上发生在我的测试中）。

是否有关于如何避免此类并发问题的最佳实践，即在检查表是否存在和后续使用之间不会删除表？

Answer 1

"全局临时表"和"触发器"的概念只是不点击。 表是永久数据存储，其属性（包括触发器）也是如此。 重新启动服务器时，将删除临时表。 为什么有人会设计一个永久代码块（触发器）依赖于临时共享存储机制的系统？ 这似乎是失败的秘诀。

使用实表而不是全局临时表。 如果您愿意，请在名称前面加上有用的前缀，例如temp_。 如果表由数据库共享，则将其放在所有代码都可以访问的数据库中。

创建表一次并将其保留在那里（删除行就可以了），以便触发器代码可以访问它。

Answer 2

首先，我将

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

但是由于时间限制（需要数周时间才能获得新常态表的必要正式批准），因此我想出了一个解决方案，用于防止SQL Server在检查全局临时表是否存在和MERGE语句之间删除全局临时表。

有一些关于SQL Server何时删除全局临时表

的信息;我的个人测试表明，SQL Server在创建全局临时表的会话关闭时删除了全局临时表，并且在其他会话中启动的任何其他事务更改了该表中的数据。

我的解决方案是在检查全局临时表是否存在之前伪造全局临时表上的数据更改。如果该表当时存在，则 SQL Server 将知道它需要保留它，直到当前事务完成，并且在检查其是否存在后无法再删除该表。代码现在看起来像这样（正确注释，因为它是一种黑客）：

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

尽管我将其称为"自担风险"解决方案，但它确实可以防止在其他会话中使用全局临时表影响其在当前会话中的使用，这是促使我启动此线程的问题。

谢谢大家抽出宝贵时间！（从文本格式编辑到回复）