I have a stored procedure that first checks for a temp table (#temp_table), deletes it if it exists, uses the table, then finally drops it when it's done. This SP is called randomly when a user does something to trigger it and it is likely that sometimes the SP will be executed at the same time - or with some overlap.
我有一个存储过程,首先检查临时表(#temp_table),删除它(如果它存在),使用表,然后最后删除它完成后。当用户做某事来触发它时,该SP被随机调用,并且有时SP可能会同时执行 - 或者有些重叠。
Lets say I have Exec1 and Exec2 both of the same SP creating, altering, and dropping the #temp table, and they are running within milliseconds of each other.
假设我的Exec1和Exec2都是相同的SP创建,更改和删除#temp表,并且它们在相隔毫秒内运行。
What happens? Will Exec1 lock the #temp_table and Exec2 waits while Exec1 finishes? That would obviously be desirable in my case. I wouldn't want both Exec1 & 2 to use the table at the sametime, nor would I want Exec2 fail because Exec1 is already using the table.
怎么了?在Exec1完成时,Exec1会锁定#temp_table和Exec2等待吗?在我的情况下,这显然是可取的。我不希望Exec1和2同时使用该表,也不希望Exec2失败,因为Exec1已经在使用该表。
[EDIT] Should I just convert my temp table to a table variable?
[编辑]我应该将临时表转换为表变量吗?
3 个解决方案
#1
5
In sql server if you create a local temp table it is with a single # sign sql server uses a few under-score and some ID in the back end. Say you create a Temp table with the name #Temp sql server in temp db Creates a Table with name #Temp_______10912098 , every Temp table created in separate connections will have their on ID in the end of the name.
在sql server中,如果你创建一个本地临时表,它是一个单一的#signs服务器使用一些低分和后端的一些ID。假设您在临时数据库中创建名为#Temp sql server的临时表创建名为#Temp _______ 10912098的表,在单独连接中创建的每个临时表都将在名称末尾具有其ID。
These are all the Temp Tables Created in different Connections all has the name #Temp but are appended with some underscores and a unique id sql server uses to differentiate between them.
这些是在不同Connections中创建的所有临时表都具有名称#Temp,但附加了一些下划线和sql server用于区分它们的唯一ID。
#2
4
The scope of the temp table #table is limited to your session, so it should not be a problem.
临时表#table的范围仅限于您的会话,因此它不应该是一个问题。
If you used a ##table, then that's global and you would have issues.
如果您使用了##表,那么这是全局的,您会遇到问题。
See here: MSDN SQL Tables
请参见此处:MSDN SQL表
Specifically this bit:
特别是这一点:
If a database session creates the local temporary table
#employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.如果数据库会话创建本地临时表#employees,则只有会话可以使用该表,并且会话断开连接时将删除该会话。如果创建全局临时表## employees,则数据库中的任何用户都可以使用此表。如果在创建表后没有其他用户使用此表,则断开连接时将删除该表。如果其他用户在创建表之后使用该表,则SQL Server会在断开连接后以及所有其他会话不再主动使用它之后将其删除。
#3
3
Temp tables named with a hash # are specific to the individual connection.
以散列#命名的临时表特定于单个连接。
So if two connections (also known as "processes" or "SPIDs") both reference a temp table by the same #tablename they will actually be referencing different tables.
因此,如果两个连接(也称为“进程”或“SPID”)都通过相同的#tablename引用临时表,则它们实际上将引用不同的表。
You can see this if you look in tempdb. There will be multiple tables named things like #748B826C. These are in reality temporary table variables like declare @t table (ix int identity primary key) and temp tables name with a hash.
如果你查看tempdb,你可以看到这个。会有多个名为#748B826C的表。这些实际上是临时表变量,例如声明@t表(ix int identity主键)和带有散列的临时表名。
So provided these are different connections rather than recursive triggers, there should be no issue.
因此,如果这些是不同的连接而不是递归触发器,那么应该没有问题。
however if you are concerned about the possibility of recursive triggers you should use table variables instead. These are restricted to the scope of the batch or stored proc.
但是,如果您担心递归触发器的可能性,则应该使用表变量。这些仅限于批处理或存储过程的范围。
#1
5
In sql server if you create a local temp table it is with a single # sign sql server uses a few under-score and some ID in the back end. Say you create a Temp table with the name #Temp sql server in temp db Creates a Table with name #Temp_______10912098 , every Temp table created in separate connections will have their on ID in the end of the name.
在sql server中,如果你创建一个本地临时表,它是一个单一的#signs服务器使用一些低分和后端的一些ID。假设您在临时数据库中创建名为#Temp sql server的临时表创建名为#Temp _______ 10912098的表,在单独连接中创建的每个临时表都将在名称末尾具有其ID。
These are all the Temp Tables Created in different Connections all has the name #Temp but are appended with some underscores and a unique id sql server uses to differentiate between them.
这些是在不同Connections中创建的所有临时表都具有名称#Temp,但附加了一些下划线和sql server用于区分它们的唯一ID。
#2
4
The scope of the temp table #table is limited to your session, so it should not be a problem.
临时表#table的范围仅限于您的会话,因此它不应该是一个问题。
If you used a ##table, then that's global and you would have issues.
如果您使用了##表,那么这是全局的,您会遇到问题。
See here: MSDN SQL Tables
请参见此处:MSDN SQL表
Specifically this bit:
特别是这一点:
If a database session creates the local temporary table
#employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.如果数据库会话创建本地临时表#employees,则只有会话可以使用该表,并且会话断开连接时将删除该会话。如果创建全局临时表## employees,则数据库中的任何用户都可以使用此表。如果在创建表后没有其他用户使用此表,则断开连接时将删除该表。如果其他用户在创建表之后使用该表,则SQL Server会在断开连接后以及所有其他会话不再主动使用它之后将其删除。
#3
3
Temp tables named with a hash # are specific to the individual connection.
以散列#命名的临时表特定于单个连接。
So if two connections (also known as "processes" or "SPIDs") both reference a temp table by the same #tablename they will actually be referencing different tables.
因此,如果两个连接(也称为“进程”或“SPID”)都通过相同的#tablename引用临时表,则它们实际上将引用不同的表。
You can see this if you look in tempdb. There will be multiple tables named things like #748B826C. These are in reality temporary table variables like declare @t table (ix int identity primary key) and temp tables name with a hash.
如果你查看tempdb,你可以看到这个。会有多个名为#748B826C的表。这些实际上是临时表变量,例如声明@t表(ix int identity主键)和带有散列的临时表名。
So provided these are different connections rather than recursive triggers, there should be no issue.
因此,如果这些是不同的连接而不是递归触发器,那么应该没有问题。
however if you are concerned about the possibility of recursive triggers you should use table variables instead. These are restricted to the scope of the batch or stored proc.
但是,如果您担心递归触发器的可能性,则应该使用表变量。这些仅限于批处理或存储过程的范围。