sql-server - 新连接的 READPAST 锁问题

Question

我有一个如下所示的存储过程:

USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[ProcName]    Script Date: 10/03/2012 12:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcName](@filter bigint, @n int)
AS
DELETE FROM TableName WITH (READPAST, UPDLOCK, ROWLOCK) OUTPUT(DELETED.ColumnName2)
WHERE TableName.ID in (select top (@n) ID from TableName where TableName.ColumnName1 = @filter)

此过程返回 ColumnName2 的前@n 个值(这些值表示的记录将从表中删除)。我正在使用 vb 方法中的这个存储过程，它通常运行良好。但是，有时，由于我不知道的原因，它会抛出异常:

You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

我的 vb 方法调用这个存储过程，如果它没有获得足够的值，那么它会生成新值并调用这个存储过程。重复此操作，直到有足够的值。本质上，TableName 就像一个队列一样工作，我的 vb 方法运行良好，但是，有时会抛出上述异常。是什么原因导致的？我的问题的解决方案是什么？

我试图仅为调用我的存储过程的代码启动一个新连接，但无济于事，因为再次抛出异常。尽管我已经阅读了以下文章，但我不知道解决方案是什么:

http://www.red-gate.com/messageboard/viewtopic.php?t=13614

.NET READPAST lock error when calling a stored procedure

http://support.microsoft.com/kb/981995

http://blogs.technet.com/b/claudia_silva/archive/2011/08/08/replication-error-quot-you-can-only-specify-the-readpast-lock-in-the-read-committed-or-repeatable-read-isolation-levels-quot-generated-when-altering-published-table-columns.aspx

提前感谢您的帮助，

Lajos Árpád。

Answer 1

这应该可以解决您的问题

ALTER PROCEDURE [dbo].[ProcName](@filter bigint, @n int)
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DELETE FROM TableName WITH (READPAST, UPDLOCK, ROWLOCK) OUTPUT(DELETED.ColumnName2)
WHERE TableName.ID in (select top (@n) ID from TableName where TableName.ColumnName1 = @filter)

由于您正在使用需要此设置的 READPAST，您最好在 SP 中明确设置它，前提是它的范围是安全的。

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked.

引用:SET TRANSACTION ISOLATION LEVEL

至于为什么会这样，一个可能的原因是 SQL 调用之间的连接池和混合事务隔离级别。