Delete statements locks table

Question

Im running a cleanup job(every hour) on a table which constantly grows in rows.The job ran for about a week without any problems. Today i saw that the job started locking the entire table.

Is this expected behaviour ? Could it be that after a specific ammount of rows that need to be deleted it locks the entire table instead of only the specific rows that need to be deleted ?

Thanks in advance !

Answer 1

One possibility is that you need to index the column by which you are searching the table for rows to delete. If you do not have an index, then SQL Server will acquire many more locks while it searches for the rows to delete.

I highly recommend deleting small chunks of rows in a loop. As others have pointed out, if you try to delete more than about 5,000 rows at once, SQL Server will escalate the row locks into a table lock. Deleting fewer records at a time-- say, 1,000-- avoids locking the entire table. Your job can continue looping over the deletes until it is done.

The pseudocode for a looped delete looks like this:

declare @MoreRowsToDelete bit
set @MoreRowsToDelete = 1
while @MoreRowsToDelete = 1
begin
    delete top (1000) MyTable from MyTable where MyColumn = SomeCriteria
    if not exists (select top 1 * from MyTable where MyColumn = SomeCriteria)
        set @MoreRowsToDelete = 0
end

Alternatively, you could look at the @@ROWCOUNT and use READPAST hint to avoid locked rows:

declare @RowCount int
set @RowCount = 1 -- priming the loop
while @RowCount > 0
begin
    delete top (1000) MyTable from MyTable with (readpast) where MyColumn = SomeCriteria
    set @RowCount = @@ROWCOUNT
end

Note that the lock escalation threshold depends on other factors like concurrent activity. If you regularly have so much activity that even a 1,000 deletion will escalate to a table lock, you can lower the number of rows deleted at once.

See the Microsoft documentation on lock escalation for more information.

Answer 2

If your query affects 5000 rows or more in the same table, that table gets locked during the operation. This is standard SQL Server behavior. Basically every DELETE causes a lock on that row and every 5000 row locks on the same table cause a Lock Escalation from row to table.