临时表是线程安全的吗？

Question

我正在使用SQL Server 2000，它的许多存储过程广泛使用临时表。数据库的流量很大，我担心创建和删除临时表的线程安全性。

假设我有一个存储过程，它创建了几个临时表，甚至可以将临时表连接到其他临时表等。并假设两个用户同时执行该存储过程。

• 是否可能一个用户运行存储过程并创建名为#temp的临时表，而另一个用户运行同样的存储过程但因为数据库中已经存在名称为#temp的表而被停止？

• 如果同一用户在同一连接上执行同一存储过程两次呢？

• 是否存在其他奇怪的情况可能会导致两个用户的查询互相干扰？

- Princess

Answer 1

对于第一个情况，不，不可能，因为#temp是一个本地临时表，因此对其他连接不可见（假定用户使用单独的数据库连接）。临时表名被别名为生成的随机名称，当引用本地临时表时，您引用该名称。

在您的情况下，由于您正在存储过程中创建本地临时表，当退出过程范围时，该临时表将被删除（请参阅“备注”部分）。

在存储过程中创建的本地临时表在存储过程完成时会自动删除。该表可以由由创建表的存储过程执行的任何嵌套存储过程引用。不能由调用创建表的存储过程的进程引用该表。

对于第二种情况，是的，您会收到此错误，因为该表已经存在，并且该表的持续时间与连接一样长。如果是这种情况，则建议在尝试创建它之前检查表的存在性。

- casperOne

---

你如何在一个连接中并行执行相同的存储过程？ - SQLMenace

您可以在同一连接上执行相同的存储过程两次（不是并行执行），并且在第一次运行存储过程后不删除临时表。在这种情况下，临时表仍然存在。 - Jeroen Landheer

抱歉，@CasperOne，但您的信息不准确：在存储过程内创建的临时表在SP完成时会自动删除。在同一连接上两次调用此类SP没有问题。 - Gerardo Lima

@GerardoLima，已更新答案以反映您的评论，并附上了文档参考。 - casperOne

Answer 2

本地范围的临时表（只有一个#）在其末尾创建了一个标识符，使它们成为唯一的；多个调用者（即使使用相同的登录）永远不应该重叠。

（试试：从两个连接和相同的登录创建相同的临时表。然后查询tempdb.dbo.sysobjects以查看实际创建的表...）

- Joe

Answer 3

本地临时表是线程安全的，因为它们只存在于当前上下文中。请不要将上下文与当前连接混淆（来自MSDN：“存储过程中创建的本地临时表在存储过程完成时会自动删除”），同一连接可以安全地调用多次创建本地临时表的存储过程（如#TMP）。

您可以通过从两个连接执行以下存储过程来测试此行为。该SP将等待30秒，以便我们可以确保两个线程同时运行其自己版本的#TMP表：

CREATE PROCEDURE myProc(@n INT)
AS BEGIN
    RAISERROR('running with (%d)', 0, 1, @n);
    CREATE TABLE #TMP(n INT);
    INSERT #TMP VALUES(@n);
    INSERT #TMP VALUES(@n * 10);
    INSERT #TMP VALUES(@n * 100);
    WAITFOR DELAY '00:00:30';
    SELECT * FROM #TMP;
END;

- Gerardo Lima

---

你提供的例子似乎并不能证明你的观点: 本地临时表只存在于当前上下文中。请不要将上下文与当前连接混淆。如果您所描述的工作原理，则应在同一会话内多次调用该过程而不会出现问题，只要相关的本地临时表是在该过程的上下文中创建的，而不是在该过程之外？ - Ben

谢谢，你提供的链接中的临时表部分已经为我解决了所有问题：https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017#temporary-tables - Ben

Answer 4

简短的答案是：

临时表的隔离性是“每个查询保证一次”，无需担心线程、锁或并发访问方面的问题。

我不确定为什么这里的答案会涉及到“连接”和“线程”的重要性，因为这些都是编程概念，而查询隔离是在数据库层级上处理的。

在 SQL Server 中，本地临时对象是按“会话”隔离的。如果您同时运行两个查询，则它们是完全分开的，不会相互干扰。登录信息并不重要，例如，如果您使用一个单一的 ADO.NET 连接字符串（这意味着多个并发查询将使用相同的 SQL Server“登录”），则所有查询都将“仍然在不同的会话中运行”。连接池也并不重要。本地临时对象（表和存储过程）“完全安全，不会被其他会话看到”。

为了澄清如何工作，虽然您的代码对本地临时对象有一个共同的名称，但 SQL Server 会对每个会话的每个对象附加一个唯一的字符串以使它们分开。您可以通过在 SSMS 中运行以下命令来查看：

CREATE TABLE #T (Col1 INT)

SELECT * FROM tempdb.sys.tables WHERE [name] LIKE N'#T%';

您将看到类似以下名称的内容：

T_______________00000000001F

然后，在不关闭该查询选项卡的情况下，打开一个新的查询选项卡并粘贴相同的查询，再次运行它。现在您应该看到类似以下内容：

T_______________00000000001F

T_______________000000000020

因此，每次您的代码引用＃T时，SQL Server都会根据会话将其转换为正确的名称。分离全部由自动处理。

- Chris Halcrow

Answer 5

临时表与会话绑定，因此如果不同用户同时运行您的过程，则不会发生冲突...

- JPunyon

Answer 6

临时表只在创建它们的查询或过程的上下文中创建。每个新查询都会在数据库上获得一个无其他查询临时表的上下文。因此，名称冲突不是问题。

- Jekke

---

我认为你可以针对每个过程这样说，但不能针对每个查询。否则，将无法稍后查询相同的临时表。除非你指的是查询“窗口”？ - Ben

Answer 7

如果你查看temps数据库，你可以看到临时表，它们具有系统生成的名称。因此，除了常规死锁之外，你应该没问题。

- Otávio Décio

Answer 8

除非您使用两个井号##temp，否则临时表将是本地的，并且仅存在于用户与本地连接之间。

- SQLMenace

Answer 9

首先，让我们确保您正在使用真正的临时表，它们是否以 # 或 ## 开头？如果您正在动态创建实际的表，然后重复删除和重新创建它们，那么您确实会遇到并发用户的问题。如果您创建全局临时表（以 ## 开头的表），也可能会出现问题。如果您不想出现并发问题，请使用本地临时表（它们以 # 开头）。在存储过程结束时（或者在长时间的多步存储过程中不再需要它们时），显式关闭它们并检查它们是否存在（如果存在，则删除）也是一个好习惯。

- HLGEM