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

有没有最佳实践来避免这种并发问题，即在检查其存在性和随后使用它之间不会删除表？

- Ioan H

Answer 1

首先，我要说的是，从长远来看，我会遵循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);

尽管我会称其为“自行决定使用”的解决方案，但它确实可以防止其他会话中对全局临时表的使用影响当前会话中对其的使用，这是我发起此线程的担忧。感谢大家的时间！（从文本格式编辑到回复）

- Ioan H

Answer 2

“全局临时表”和“触发器”的概念似乎不太相符。表是永久数据存储区，它们的属性（包括触发器）也是如此。当服务器重新启动时，临时表将被删除。为什么要设计一个永久代码块（触发器）依赖于临时共享存储机制呢？这似乎是一个失败的方案。

建议使用真实的表替代全局临时表。如果需要，可以在表名前添加有用的前缀，例如“temp_”。如果该表被多个数据库共享，则将其放置在所有代码都可以访问的数据库中。

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

- Gordon Linoff

---

感谢您的快速回答！虽然我承认我没有考虑过永久代码与临时共享存储概念，而您给了我一些思路，但我选择使用全局临时表有我的原因：

• 在我的工作中，我可以自由地使用临时表，但创建真正的表必须经过不同的正式程序和繁琐的过程；我尽量避免这种情况。
• 通过使用临时表，我获得了一个优势，即SQL Server会在没有任何会话再使用它时为我删除它。真正的表将继续存在并继续增长。

- Ioan H

@loadH...哦，唉，官僚主义和奇怪的扭曲方式实际上完成任务。我完全理解。即便如此，也许有可能在某些启动操作期间创建全局临时表，以便触发器可以使用它。我不喜欢在触发器中尝试创建表的想法。 - Gordon Linoff