Deletion of rows in table cause LOCKS

Question

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?

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

Answer 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