使用Nicholas的建议,我添加了一个BEGIN TRANSACTION和一个COMMIT.我将删除循环包含在BEGIN TRY / BEGIN CATCH中.在BEGIN CATCH中,就在ROLLBACK之前,我运行了sp_lock和sp_who2. (我在上面的问题中添加了代码更改.)

当我的进程被阻止时,我看到了以下输出：

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
20     2      1401108082  0      TAB                                   IX       GRANT
20     2      1401108082  1      PAG  1:102368                         X        GRANT

SPID  Status     Login HostName BlkBy DBName Command       cpuTime DiskIO
----  ---------- ----- -------- ----- ------ ------------- ------- ------
20    BACKGROUND sa    .        .     tempdb GHOST CLEANUP 31      0

为了将来参考,当sql Server删除记录时,它会在它们上设置一点,只是将它们标记为“ghost记录”.每隔几分钟,就会运行一个名为ghost cleanup的内部进程来回收已完全删除的记录页(即所有记录都是ghost记录).

The ghost cleanup process was discussed on ServerFault in this question.

Here is Paul S. Randal’s explanation of the ghost cleanup process.

It is possible to disable the ghost cleanup process with a trace flag.但在这种情况下我没有必要这样做.

我最终添加了一个100毫秒的锁等待超时.这会导致鬼记录清理过程中偶尔发生锁定等待超时,但这是可以接受的.我还添加了一个循环,重试锁定超时最多5次.通过这两项更改,我的流程现在通常会完成.现在只有在一个非常长的进程推送大量数据来获取表或页面锁定我的进程需要清理的数据时,它才会超时.

编辑2016-07-20

最终代码如下所示：

-- Do not block long if records are locked.
SET LOCK_TIMEOUT 100

-- This process volunteers to be a deadlock victim in the case of a deadlock.
SET DEADLOCK_PRIORITY LOW

DECLARE @Error BIT
SET @Error = 0

DECLARE @ErrMsg VARCHAR(1000)
DECLARE @DeletedCount INT
SELECT @DeletedCount = 0

DECLARE @LockTimeoutCount INT
SET @LockTimeoutCount = 0

DECLARE @ContinueDeleting BIT,@LastDeleteSuccessful BIT

SET @ContinueDeleting = 1
SET @LastDeleteSuccessful = 1

WHILE @ContinueDeleting = 1
BEGIN
    DECLARE @RowCount INT
    SET @RowCount = 0

    BEGIN TRY

        BEGIN TRANSACTION

        -- The READPAST below attempts to skip over locked records.
        -- However,it might still cause a lock wait error (1222) if a page or index is locked,because the delete has to modify indexes.
        -- The threshold for row lock escalation to table locks is around 5,000 records,-- so keep the deleted number smaller than this limit in case we are deleting a large chunk of data.
        -- Table name,field,and value are all set dynamically in the actual script.
        SET @sql = N'DELETE TOP (1000) MyTable WITH(ROWLOCK,READPAST) WHERE MyField = SomeValue' 
        EXEC sp_executesql @sql,N'@ProcGuid uniqueidentifier',@ProcGUID

        SET @RowCount = @@ROWCOUNT

        COMMIT

        SET @LastDeleteSuccessful = 1

        SET @DeletedCount = @DeletedCount + @RowCount
        IF @RowCount = 0
        BEGIN
            SET @ContinueDeleting = 0
        END

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
            ROLLBACK

        IF Error_Number() = 1222 -- Lock timeout
        BEGIN

            IF @LastDeleteSuccessful = 1
            BEGIN
                -- If we hit a lock timeout,and we had already deleted something successfully,try again.
                SET @LastDeleteSuccessful = 0
            END
            ELSE
            BEGIN
                -- The last delete Failed,too.  Give up for now.  The job will run again shortly.
                SET @ContinueDeleting = 0
            END
        END
        ELSE -- On anything other than a lock timeout,report an error.
        BEGIN       
            SET @ErrMsg = 'An error occurred cleaning up data.  Table: MyTable Column: MyColumn Value: SomeValue.  Message: ' + ERROR_MESSAGE() + ' Error Number: ' + CONVERT(VARCHAR(20),ERROR_NUMBER()) + ' Line: ' + CONVERT(VARCHAR(20),ERROR_LINE())
            PRINT @ErrMsg -- this error message will be included in the sql Server job history
            SET @Error = 1
            SET @ContinueDeleting = 0
        END

    END CATCH

END

IF @Error <> 0
    RAISERROR('Not all data could be cleaned up.  See prevIoUs messages.',16,1)