Deletion of rows in table cause LOCKS

问题:

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

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

Any suggestions?

回答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

WaitFor DELAY '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?

And then use following query to delete the rows, assuming that your PrimaryKey column is of type 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

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://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

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.

回答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.

This can be done by using "snapshot isolation" which was introduced with 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.

 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