删除表中的行会导致 LOCKS [英] Deletion of rows in table cause LOCKS

问题描述

我正在运行以下命令从一个大表(1.5 亿行)中批量删除行:

I am running the following command to delete rows in batches out of a large table (150 million rows):

DECLARE @RowCount int
WHILE 1=1
    BEGIN
        DELETE TOP (10000) t1
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007

        SET @RowCount = @@ROWCOUNT

        IF (@RowCount < 10000) BREAK
    END

这张表被广泛使用.但是，它在删除记录的同时，也会导致某些记录被锁定，从而向用户抛出错误(这在我们所处的环境中是不可接受的).

This table is HIGHLY used. However, it is deleting records, but it is also causing locking on some records, thus throwing errors to the user (which is not acceptable in the environment we're in).

如何在不导致锁定的情况下删除旧记录?我应该将批次的大小从 10000 条记录减少到 1000 条吗?这将如何影响日志大小(我们几乎没有剩余的硬盘空间可用于大量日志增长).

How can I delete older records without causing locks? Should I reduce the size of the batch from 10000 records to 1000? How will this effect log sizes (we have very little hard drive space left for large log growth).

有什么建议吗?

推荐答案

我在过去看到过类似的零星问题，即使在小批量 0f 5000 条记录中，锁定仍然会发生.在我们的例子中，每个删除/更新都包含在它自己的 Begin Tran...Commit 循环中.纠正这个问题，

I have seen similar sporadic problems in the past where even in small batches 0f 5000 records, locking would still happen. In our case, each delete/update was contained in its own Begin Tran...Commit loop. To correct the problem, the logic of

等待延迟'00:00:00:01'

WaitFor DELAY '00:00:00:01'

被放置在每个循环的顶部并纠正了问题.

was placed at the top of each loop through and that corrected the problem.

这篇关于删除表中的行会导致 LOCKS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！