带有动态sql的存储过程。默认情况下隔离安全吗？

Question

我希望能确认我在T文档中找到的答案。

我有一个存储过程，它两次引用一个视图。在过程中执行一次，然后在执行动态sql语句时执行第二次。

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM CourseSalesView) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

我的数据库具有Server的默认隔离级别“ReadCommited”。如果在我的过程和sp_executesql之间改变了我的观点，我会有并发问题吗？也就是说，在我看来，另一个用户在正确的时间删除一行会导致这个过程的两个语句之间不匹配吗？

我假设'ReadCommited‘隔离级别可以防止这种情况发生，并且我不需要在存储过程中的任何地方显式声明隔离级别，因为它是从默认设置继承的。

Answer 1

在当前的设置中，每个查询都是一个单独的事务。单独地，他们只读取提交的数据。但没有什么能把它们联系在一起。如果希望跨两个语句为该视图保证相同的底层数据，则需要将它们封装在事务中，并将表提示添加到第一个指定锁定表：WITH (TABLOCK, HOLDLOCK)的查询中。我刚刚在一个视图上测试了这一点，它会阻止访问直到事务完成，除非查询使用了WITH (NOLOCK)提示。

无论如何，您可以对代码进行简单的测试，查看当前代码是如何工作的，以及任何更改可能如何执行您想要的操作：

在EXEC sp_executesql @DynamicPivotQuery行之前，添加以下两行：

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

然后，运行上面发布的代码。当您在Messages选项卡中看到“立即进行更改”时，对基础表进行更改。一旦WAITFOR完成，它将运行动态SQL，您应该会看到更改的效果。

因此，以下几点应能奏效：

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

除了这种基本的互动之外，还有另外两种选择：

• 将视图的结果存储到本地临时表(即#TableName)中。这样做的好处是不锁定视图的底层表(S)，但它必须将视图的全部结果写入tempdb，如果视图返回10k行或更多行，这听起来不像是一个可伸缩的解决方案。
• 启用SNAPSHOT ISOLATION (在Server 2005中引入)，它无需显式锁定表：，而是为您处理此问题
  • Server中的快照隔离
  • 更改数据库集选项
  • 您仍然需要在一个显式事务中包装这两个查询，但不需要表提示
  • 如果不启用READ_COMMITTED_SNAPSHOT，则需要在BEGIN TRAN之前发出SET TRANSACTION ISOLATION LEVEL SNAPSHOT