READPAST lock issues with new connection

Question

I have a stored procedure which looks like this:

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)

This procedure returns the first @n values of ColumnName2 (the records represented by these values will be deleted from the table). I am using this stored procedure from a vb method and it usually works well. However, sometimes, for reasons not known by me it throws an exception:

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

My vb method calls this stored procedure and if it doesn't get enough values, then it generates new values and calls this stored procedure. This is repeated until there are enough values. In essence, TableName works like a queue and my vb method works well, however, sometimes the exception mentioned above is thrown. What can cause this and what is the solution to my problem?

I have tried to initiate a new connection just for the code which calls my stored procedure, but to no avail, as the exception was thrown again. I have no idea what could be the solution, in spite the fact that I have read the following articles:

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

Thank you in advance for any help,

Lajos Árpád.

Solution 1

This should solve your problem

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)

Since you are using READPAST, which requires this setting, you might as well set it explicitly in the SP, given that it is safely scoped.

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.

Reference: SET TRANSACTION ISOLATION LEVEL

As for why it is happening, a likely reason is connection pooling and mixed transaction isolation levels between SQL calls.

Solution 2

First of all, the DELETE is incorrect. To achieve what you want you need to write it like this:

WITH T AS (
   SELECT TOP (@n)
    ColumnName2
   FROM TableName WITH (READPAST, UPDLOCK, ROWLOCK) 
   WHERE ColumnName1 = @filter)
DELETE FROM T
OUTPUT DELETED.ColumnName2;

This rewrite eliminates the race conditions you have between the UPDATE and the scan for top IDs. In order to work, the WHERE clause must be SARGable (ie. ColumnName1 must be index) otherwise you end up with a scan anyway and the READPAST helps nothing. See Using Tables as Queues for more details.

Now back to your question: what causes a different isolation level? Well, is something in your code which you did not post here. If I'd venture a shot, it must be a TransactionScope object constructed with the default constructor. See Using new TransactionScope() Considered Harmful for a discussion how this happens and why is bad. The article linked contains also the solution: use the explicit constructor of the transaction scope, one that accept a TransactionOptions on which you specify the desired isolation level explicitly.