临时表在同一连接池上的多个请求中是唯一的吗? [英] Temp Table unique across multiple requests on the same connection pool?

问题描述

我有以下存储过程，它使用临时表批量导入数据.我知道每个会话的临时表都是唯一的，但是我想知道我的应用程序是否使用线程并向存储过程发出多个并发请求，使用来自应用程序池的相同 sql 连接，它们最终是否会引用相同的临时表?

I have the following stored proc which uses a temp table to bulk import the data. I understand the temp tables are unique for every session, however i'm wondering if my application uses threads and makes multiple concurrent request to the stored proc, using the same sql connection from the application pool, will they end up referencing the same temp table?

CREATE PROCEDURE [dbo].[Mytestproc]
AS
  BEGIN
      BEGIN TRANSACTION

      CREATE TABLE #Hold
        (
           ID INT,
           VAL NVARCHAR(255)
        )

      BULK INSERT #Hold
        FROM 'C:\data.txt'
        WITH
          (
            FieldTermInAtOr ='|',
            RowTermInAtOr ='\n'
          )

      SELECT *
      FROM   #Hold

      DROP TABLE #Hold

      COMMIT TRANSACTION
  END 

推荐答案

虽然一个线程正在使用一个连接并执行这个存储过程，但连接池不能重用同一个连接 - 因此没有在那里共享的危险.其他线程无法使用此连接，而是会打开新的连接.

Whilst one thread is using a connection and executing this stored procedure, that same connection cannot be reused by the connection pool - so there's no danger of sharing there. Other threads cannot use this connection, and will open new ones instead.

此外，无需在存储过程结束前删除临时表 - 在存储过程中创建的临时表会在该存储过程退出时自动删除.

In addition, there's no need to drop the temp table before the stored procedure ends - temp tables created within a stored proc are dropped automatically when the proc is exited.

这篇关于临时表在同一连接池上的多个请求中是唯一的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！