从 CTE 中批量删除记录

Question

我需要从最近写入的表中删除旧记录。我的问题是，当另一个进程尝试读取或写入表时，我的 delete 语句失败并出现死锁。 它是大型存储过程的一部分，该过程以Transaction isolation level read uncommitted和deadlockpriority low开始。

如何转换此 cte 和删除语句以部分删除记录？例如，如果我可以选择 cte (x( 中为 x/500 记录运行删除语句的记录计数，那么一段时间将是完美的。

;with chargesToDelete(id, ciid) as (
select c.id, ci.Id from @chargeids c
left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
where ci.id is null
)
delete from xxx.dbo.charges
where Id in (select id from chargesToDelete);

Answer 1

您可以使用会话变量循环@@ROWCOUNT在DELETE上带有TOP N。

SELECT 1 -- Forces @@ROWCOUNT = 1
WHILE @@ROWCOUNT > 0
BEGIN
delete TOP (500) I from 
xxx.dbo.charges AS I
where 
exists (
select 
'to delete' 
from 
@chargeids c
left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
where 
ci.id is null AND
c.id = I.Id)
END

如果不想使用哨兵SELECT(这会将结果返回给客户端，如果有(，则可以使用变量。

DECLARE @ForceStart BIT = 1
WHILE @@ROWCOUNT > 0 OR @ForceStart = 1
BEGIN
SET @ForceStart = 0
delete TOP (500) I from 
xxx.dbo.charges AS I
where 
exists (
select 
'to delete' 
from 
@chargeids c
left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
where 
ci.id is null AND
c.id = I.Id)
END

如果子查询需要很长时间来处理，则可能需要创建一个临时表，其中包含要删除的 ID 并在循环中与其联接。

作为旁注，如果您正在检查xxx.dbo.chargeitems上不存在的记录，则执行NOT EXISTS将比使用IS NULL的LEFT JOIN更快。

---

编辑：使用临时表保存 ID：

-- Create temporary table
IF OBJECT_ID('tempdb..#ChargesToDelete') IS NOT NULL
DROP TABLE #ChargesToDelete
SELECT DISTINCT
c.id
INTO
#ChargesToDelete
from 
@chargeids c
left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
where 
ci.id is null
CREATE CLUSTERED INDEX CI_ChargesToDelete ON #ChargesToDelete (id)

-- Delete rows in batches
SELECT 1 -- Forces @@ROWCOUNT = 1
WHILE @@ROWCOUNT > 0
BEGIN
delete TOP (500) I from 
xxx.dbo.charges AS I
where 
exists (select 'to delete' from #ChargesToDelete AS C where c.id = I.Id)
END

Answer 2

您可以尝试以下代码：

declare @deleted table (id int)
declare @amountToDelete int = 2
delete from @deleted
delete top (@amountToDelete) from xxx.dbo.charges
output deleted.* into @deleted
where not exists(select 1 from xxx.dbo.chargeitems
where charge_id = xxx.dbo.charges.id)
while (select count(*) from @deleted) = @amountToDelete
begin
delete from @deleted
delete top (@amountToDelete) from xxx.dbo.charges
output deleted.* into @deleted
where not exists(select 1 from xxx.dbo.chargeitems
where charge_id = xxx.dbo.charges.id)
end

@amountToDelete表示一次要删除的一批记录的大小。另一个变量，类型为table，在循环运行一次时保存已删除的id。停止循环的条件是删除的记录少于应该删除的记录(这意味着有最后要删除的记录并且它们已被删除(，这对应于以下条件：

while (select count(*) from @deleted) = @amountToDelete

第一次删除是在循环之前(外部(执行的。如果一开始删除了所有 recrod，则代码不会进入循环。