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

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

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

根据尼古拉斯的建议，我添加了

BEGIN TRANSACTION

和

COMMIT

。我将删除循环包装在

BEGIN TRY

/中

BEGINCATCH

。在

BEGIN CATCH

之前，权

ROLLBACK

，我跑

sp_lock

和

sp_who2

。（我在上面的问题中添加了代码更改。）

当我的进程被阻塞时，我看到以下输出：

spid   dbid   ObjId       IndId  Type Resource   Mode     Status------ ------ ----------- ------ ---- -------------------------------- -------- ------20     2      1401108082  0      TAB  IX       GRANT20     2      1401108082  1      PAG  1:102368   X        GRANTSPID  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 LOWDECLARE @Error BITSET @Error = 0DECLARE @ErrMsg VARCHAr(1000)DECLARE @DeletedCount INTSELECT @DeletedCount = 0DECLARE @LockTimeoutCount INTSET @LockTimeoutCount = 0DECLARE @ContinueDeleting BIT,    @LastDeleteSuccessful BITSET @ContinueDeleting = 1SET @LastDeleteSuccessful = 1WHILE @ContinueDeleting = 1BEGIN    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 CATCHENDIF @Error <> 0    RAISERROR('Not all data could be cleaned up.  See previous messages.', 16, 1)