临时表是线程安全的吗? [英] Are temporary tables thread-safe?

问题描述

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

可以说我有一个存储过程，该存储过程创建了一些临时表，它甚至可以将临时表联接到其他临时表，等等.还可以说两个用户同时执行该存储过程.

• 一个用户是否有可能运行sp并创建一个称为#temp的临时表，而另一个用户运行同一个sp但由于数据库中已经存在一个名为#temp的表而被停止了? /p>

• 如果同一用户在同一连接上两次执行同​​一存储过程，该怎么办?

• 还有其他可能导致两个用户查询相互干扰的怪异场景吗?

解决方案

对于第一种情况，不可以，因为#temp是本地临时表，因此对其他连接不可见(假定您的用户正在使用单独的数据库连接).临时表名称的别名是生成的随机名称，引用本地临时表时会引用该名称.

在您的情况下，由于您正在存储过程中创建本地临时表，因此

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

I'm using SQL Server 2000, and many of the stored procedures it use temp tables extensively. The database has a lot of traffic, and I'm concerned about the thread-safety of creating and dropping temp tables.

Lets say I have a stored procedure which creates a few temp tables, it may even join temp tables to other temp tables, etc. And lets also say that two users execute the stored procedure at the same time.

• Is it possible for one user to run the sp and which creates a temp table called #temp, and the another user runs the same sp but gets stopped because a table called #temp already exists in the database?

• How about if the same user executes the same stored procedure twice on the same connection?

• Are there any other weird scenarios that might cause two users queries to interfere with one another?

解决方案

For the first case, no, it is not possible, because #temp is a local temporary table, and therefore not visible to other connections (it's assumed that your users are using separate database connections). The temp table name is aliased to a random name that is generated and you reference that when you reference your local temp table.

In your case, since you are creating a local temp table in a stored procedure, that temp table will be dropped when the scope of the procedure is exited (see the "remarks section").

For the second case, yes, you will get this error, because the table already exists, and the table lasts for as long as the connection does. If this is the case, then I recommend you check for the existence of the table before you try to create it.

这篇关于临时表是线程安全的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！