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

Question

我正在测试一次删除很多记录的过程。不能TRUNCATE TABLE，因为其中存在需要保留的记录。

由于数量巨大，我将删除操作分成了类似于以下的循环：

-- Do not block if records are locked.
SET LOCK_TIMEOUT 0
-- This process should be chosen as a deadlock victim in the case of a deadlock.
SET DEADLOCK_PRIORITY LOW
SET NOCOUNT ON

DECLARE @Count
SET @Count = 1
WHILE @Count > 0
BEGIN TRY
    BEGIN TRANSACTION -- added per comment below

    DELETE TOP (1000) FROM MyTable WITH (ROWLOCK, READPAST) WHERE MyField = SomeValue
    SET @Count == @@ROWCOUNT

    COMMIT
END TRY
BEGIN CATCH
    exec sp_lock -- added to display the open locks after the timeout
    exec sp_who2 -- shows the active processes

    IF @@TRANCOUNT > 0
        ROLLBACK
    RETURN -- ignoring this error for brevity
END CATCH

MyTable是一个群集表。MyField在聚集索引的第一列中。它表示记录的逻辑分组，因此MyField = SomeValue经常选择许多记录。我不在乎删除它们的顺序，只要一次处理一组即可。该表上没有其他索引。

我添加了ROWLOCK提示以避免尝试在生产中看到锁升级。我添加了READPAST提示，以避免删除其他进程锁定的记录。那永远不会发生，但我正在努力确保安全。

问题：有时，当唯一运行该循环时，该循环会遇到锁定超时1222“超出了锁定请求超时时间”。

我确信在测试此过程时，该系统上没有其他活动，因为它是我自己的开发人员箱，没有其他人连接，没有其他进程在运行，并且探查器未显示任何活动。

一秒钟后，我可以重新运行同一脚本，它从中断处开始取乐，愉快地删除记录-直到下一个锁定超时为止。

我试过BEGIN TRY/BEGIN CATCH来忽略1222错误，然后重试删除，但是由于相同的锁定超时错误，它立即再次失败。如果在重试之前添加了短暂的延迟，它也会再次失败。

我认为锁定超时是由于诸如页面拆分之类的原因，但是我不确定为什么这会与当前循环迭代冲突。先前的delete语句应该已经完成​​，并且我认为这意味着所有页面拆分也已完成。

为什么DELETE循环对自身造成锁定超时？

该过程是否有办法避免此锁定超时或检测到可以安全恢复？

这是在SQL Server 2005上。

- 编辑 -

我将Lock：Timeout事件添加到了探查器。在删除期间，它在PAGELOCK上超时：

Event Class: Lock:Timeout
TextData:    1:15634  (one example of several)
Mode:        7 - IU
Type:        6 - PAGE

DBCC PAGE报告这些页面不在主数据库（ID 1）的范围内。

-编辑2-

我加了一个BEGIN TRY/BEGIN CATCH并exec sp_lock在catch块中运行了一个。这是我所看到的：

spid dbid ObjId      IndId Type Resource Mode Status
19   2    1401108082 1     PAG  1:52841  X    GRANT  (tempdb.dbo.MyTable)
19   2    1401108082 0     TAB           IX   GRANT  (tempdb.dbo.MyTable)
Me   2    1401108082 0     TAB           IX   GRANT  (tempdb.dbo.MyTable)
Me   1    1115151018 0     TAB           IS   GRANT  (master..spt_values)  (?)

SPID 19是一个SQL Server任务管理器。这些任务管理器之一为什么要获取MyTable的锁？

Answer 1

我找到了答案：我的循环删除与ghost清除proc冲突。

根据尼古拉斯的建议，我添加了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删除记录时，它将对它们进行一些设置以仅将它们标记为“幽灵记录”。每隔几分钟，就会运行一个称为“幽灵清理”的内部过程来回收已被完全删除的记录页面（即，所有记录都是幽灵记录）。

这个问题在ServerFault上讨论了鬼清除过程。

这是Paul S. Randal对鬼清除过程的解释。

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

我最终添加了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)