我正在测试一种批量删除记录的过程。由于其中有需要保留的记录,因此无法使用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上获取锁定?