删除表中的行导致锁。

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

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

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).

这张桌子很常用。但是，它正在删除记录，但是它也导致了一些记录的锁定，从而向用户抛出错误(这在我们所处的环境中是不可接受的)。

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).

如何删除不引起锁的旧记录?我应该把批次的数量从10000条减少到1000条吗?这将如何影响日志大小(我们只有很少的硬盘空间留给大日志增长)。

Any suggestions?

有什么建议吗?

7 个解决方案

#1

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

我在过去也遇到过类似的零星问题，即使是小批的0f 5000记录，也仍然会发生锁定。在我们的例子中，每个删除/更新都包含在它自己的Begin Tran中。提交循环。为了纠正这个问题，逻辑是。

WaitFor DELAY '00:00:00:01'

等待延迟“00:00:00:01”

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

被放置在每个循环的顶部，这就纠正了问题。

#2

First of all - it looks like your DELETE performing Clustered Index Scan, i recommend to do the following:

首先，它看起来像你的删除执行聚集索引扫描，我建议做如下:

create index [IX.IndexName] ON t1(YearProcessed, PrimaryKey)

Second - is there any needs to join t2 table?

第二，是否需要加入t2表?

And then use following query to delete the rows, assuming that your PrimaryKey column is of type INT:

然后使用以下查询删除行，假设PrimaryKey列类型为INT:

declare @ids TABLE(PrimaryKey INT)
WHILE 1=1
    BEGIN
        INSERT @ids 
        SELECT top 10000 DISTINCT t1.PrimaryKey
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007

        IF @@ROWCOUNT = 0 BREAK

        DELETE  t1
        WHERE PrimaryKey in (Select PrimaryKey from @ids)

        delete from @ids

    END

And do not forget to remove t2 table from join if it is not needed

如果不需要，请不要忘记从join中删除t2表

If it still causes locks - then lower the amount of rows deleted in each round

如果仍然导致锁——那么降低每一轮中删除的行数

#3

I think you're on the right track.

我觉得你说得对。

Look at these two articles, too:

看看这两篇文章:

http://support.microsoft.com/kb/323630

http://support.microsoft.com/kb/323630
http://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Improve-Performance.htm

http://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Improve-Performance.htm

and:

和:

http://www.dbforums.com/microsoft-sql-server/985516-deleting-without-locking.html

http://www.dbforums.com/microsoft-sql-server/985516-deleting-without-locking.html

Before you run the delete, check the estimated query plan to see if it is doing an index seek for the delete, or still doing a full table scan/access.

在运行delete之前，检查估计的查询计划，看看它是在为删除执行索引搜索，还是仍然在执行完整的表扫描/访问。

#4

In addition to the other suggestions (that aim at reducing the work done during deletion) you can also configure SQL Server to not block other readers while doing deletes on a table.

除了其他建议(旨在减少删除过程中完成的工作)之外，还可以配置SQL Server在删除表时不阻塞其他读取器。

This can be done by using "snapshot isolation" which was introduced with SQL Server 2005:

这可以通过使用SQL Server 2005引入的“快照隔离”来实现:

http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

#5

If you have anything with cascading deletes make sure they are indexed.

如果有级联删除，请确保它们已被索引。

Highlighting the DELETE query and clicking Display estimated execution plan will show suggested indexes - which in my case included some cascading deletes.

高亮显示删除查询和点击显示执行计划将显示建议的索引——在我的例子中包括一些级联删除。

Adding indexes for those made the delete a lot faster - but I still wouldn't try to delete all rows at once.

为它们添加索引使删除变得更快——但是我仍然不会尝试一次删除所有的行。

#6

the best way that I have found is form asp.net DeleteExpiredSessions . you do a READUNCOMMITTED select and put the records in a temp table , than delete the record using a CURSOR.

我发现的最好的方法是使用asp.net DeleteExpiredSessions。您执行一个READUNCOMMITTED select并将记录放在一个临时表中，而不是使用游标删除记录。

 ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
    AS
        SET NOCOUNT ON
        SET DEADLOCK_PRIORITY LOW 

        DECLARE @now datetime
        SET @now = GETUTCDATE() 

        CREATE TABLE #tblExpiredSessions 
        ( 
            SessionID nvarchar(88) NOT NULL PRIMARY KEY
        )

        INSERT #tblExpiredSessions (SessionID)
            SELECT SessionID
            FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
            WHERE Expires < @now

        IF @@ROWCOUNT <> 0 
        BEGIN 
            DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT SessionID FROM #tblExpiredSessions 

            DECLARE @SessionID nvarchar(88)

            OPEN ExpiredSessionCursor

            FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

            WHILE @@FETCH_STATUS = 0 
                BEGIN
                    DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
                    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
                END

            CLOSE ExpiredSessionCursor

            DEALLOCATE ExpiredSessionCursor

        END 

        DROP TABLE #tblExpiredSessions

    RETURN 0

#7

Try this,

试试这个,

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

        IF (@RowCount < 10000) BREAK
    END

#1