SQL Server锁定超时超出循环中的删除记录

我找到了答案:我的循环删除操作与虚影清理程序冲突.

I've found the answer: my looped delete is conflicting with the ghost cleanup proc.

根据尼古拉斯的建议，我添加了BEGIN TRANSACTION和COMMIT.我将删除循环包装在BEGIN TRY/BEGIN CATCH中.在BEGIN CATCH中，在ROLLBACK之前，我分别运行sp_lock和sp_who2. (我在上面的问题中添加了代码更改.)

Using Nicholas's suggestion, I added a BEGIN TRANSACTION and a COMMIT. I wrapped the delete loop in a BEGIN TRY / BEGIN CATCH. In the BEGIN CATCH, right before a ROLLBACK, I ran sp_lock and sp_who2. (I added the code changes in the question above.)

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

When my process blocked, I saw the following output:

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删除记录时，它会在记录上设置一些位，以将其标记为幽灵记录".每隔几分钟，就会运行一个称为幽灵清理"的内部过程，以回收已完全删除的记录页面(即，所有记录都是幽灵记录).

For future reference, when SQL Server deletes records, it sets a bit on them to just mark them as "ghost records". Every few minutes, an internal process called ghost cleanup runs to reclaim pages of records that have been fully deleted (i.e. all records are ghost records).

此问题中有关ServerFault的幻影清理过程已得到讨论.

此处是Paul S. Randal对鬼清除过程的解释.

可以使用跟踪标志来禁用幻影清理过程.但是在这种情况下，我不必这样做.

It is possible to disable the ghost cleanup process with a trace flag. But I did not have to do so in this case.

我最终添加了100毫秒的锁定等待超时.这会导致在虚影记录清理过程中偶尔出现锁定等待超时，但这是可以接受的.我还添加了一个我们的循环，该循环最多可重试5次锁定超时.有了这两个更改，我的过程现在通常可以完成.现在，只有在有一个非常长的过程将大量数据推入到表或页面锁的过程中，超时才会导致超时.

I ended up adding a lock wait timeout of 100 ms. This causes occasional lock wait timeouts in the ghost record cleanup process, but that is acceptable. I also added an our loop that retries lock timeouts up to 5 times. With these two changes, my process now usually completes. Now it only gets a timeout if there is a very long process pushing lots of data around that acquires table or page locks on the data that my process needs to clean up.

编辑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)