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

我的表是一个聚集表。 MyField是聚集索引中的第一列。它表示记录的逻辑分组，因此MyField = SomeValue经常选择许多记录。我不关心删除顺序，只要一次处理一组即可。该表上没有其他索引。我添加了ROWLOCK提示，以尝试避免我们在生产中看到的锁升级。我添加了READPAST提示以避免删除其他进程锁定的记录。虽然这应该永远不会发生，但我正在尝试保险起见。

问题：有时，当该循环是唯一正在运行的内容时，此循环会出现锁定超时1222“锁定请求超时期已过”的情况。

我可以在一秒钟后重新运行相同的脚本，它会在留下的地方继续进行，快乐地删除记录-直到下一个锁定超时。

我尝试使用BEGIN TRY / BEGIN CATCH来忽略1222错误并重试删除，但是它立即失败并显示相同的锁定超时错误。如果在重试之前增加短暂的延迟，它也会再次失败。

我认为锁定超时是因为像页面拆分之类的原因，但我不确定为什么这会与当前循环迭代冲突。先前的删除语句应该已经完成，我认为这意味着任何页面拆分也已经完成。

为什么删除循环会遇到针对它本身的锁定超时？

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

这是在SQL Server 2005上。-编辑-

我在Profiler中添加了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，并在 catch 块中运行了一个 exec sp_lock。这是我看到的：

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上获取锁定？

- Paul Williams

---

你尝试过在SQL跟踪中跟踪各种锁定事件，看看能否解开发生了什么吗？ - Martin Smith

刚刚完成了，谢谢您提醒。我在上面添加了锁定超时的信息。不确定具体是什么被锁定了。 - Paul Williams

另外一个编辑：在锁定超时之后立即添加了一些sp_lock信息。 - Paul Williams

Answer 1

我找到了答案：我的循环删除与ghost cleanup proc冲突。使用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删除记录时，它会在这些记录上设置一个比特位，仅将其标记为“幽灵记录”。每隔几分钟，一个名为幽灵清理的内部进程会运行，以回收已完全删除的记录页面（即所有记录都是幽灵记录）。

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)

- Paul Williams

---

请在修复后发布您的生产方案，好吗？ - Ronnie Overby

@RonnieOverby 我添加了一个示例解决方案。我们的实际生产代码比这个更复杂，因为它通过动态SQL清理了几个不同的表。上面的代码没有包含那些额外的负担。 - Paul Williams

太棒了。感谢您抽出时间来做这件事。 - Ronnie Overby

Answer 2

您或其他人正在使用该连接，将锁定超时设置为默认值以外的值。有关详细信息，请参见 http://msdn.microsoft.com/en-US/library/ms189470(v=SQL.90).aspx。

默认的锁定时间是-1毫秒，意思是“永久等待”。

行提示很好，但它们是一种代码异味，应该避免使用。让 SQL Server 完成它的工作。它对整个系统拥有比您更多的信息。

首先，您无法控制锁定大小：锁升级会自动发生，基于未解决锁的数量。它从行锁开始。如果你积累了太多的行锁，SQL Server 就会升级到页面锁。获取太多的页面锁，它就会升级到表锁。请参见http://msdn.microsoft.com/en-us/library/ms184286(v=SQL.90).aspx 以获取有关锁升级的详细信息。但是，有几个跟踪标志可以设置，可以防止锁升级：不过，这样会降低 SQL Server 的性能。

另外一件事情是：您应该在事务中包装DELETE语句，特别是在存储过程中。

DECLARE @Count INT
SET @Count = 1
WHILE @Count > 0
  BEGIN
    BEGIN TRANSACTION
    DELETE TOP (1000) FROM MyTable WITH (ROWLOCK, READPAST) WHERE MyField = SomeValue
    SET @Count = @@ROWCOUNT
    COMMIT TRANSACTION
  END

这可以明确您的意图，并确保在应该释放锁时释放锁。

- Nicholas Carey

---

SQL不会将行锁升级为页锁，而是直接升级为表锁。http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2330)-lock-escalation.aspx - Quantum Elf

你说得对，代码将LOCK_TIMEOUT设置为0。我刚刚在上面包含了它；很抱歉之前没有提到它。 - Paul Williams

将此代码放入事务中有助于在锁超时时识别打开的锁。请参见上面的编辑。 - Paul Williams