Minimizing locking while archiving rows from a main table in SQL Server T-SQL

I've mentioned before that I really don't mind working with large tables. They're only slow to use if you're looking through all the rows when trying to find the data that you want. But there is still sometimes a valid need to archive data from a main table, across into an archive table. And people often run into problems, particularly blocking problems, when they try to do that.

Let's start with an example pair of tables (much simplified for clarity):

The wrong way

What I see most people do when they need to archive the data is something like this:

There are two basic problems with this:

  • If the SELECT picks up more than 5000 rows, then by default, a lock is taken on the whole dbo.Transactions table. (I had a suitable covering index but the same locking issue would apply if there wasn't one) If that happens, there will be issues for other people using the system, including people trying to insert new transactions. So they'll probably only be allowed to do this at weird hours of the day.
  • It's much more subtle, but unless you're certain that the rows being archived cannot change, you aren't 100% sure you're deleting the same rows as you are selecting.

Sometimes they might even wrap the whole thing in a transaction. That might cause them to have both tables tied up in locks.

A better way

Both of these issues can be avoided this way:

Limiting the deletions to 1000 rows will avoid the table locks, as long as there is a suitable index to support the delete.

Using the OUTPUT clause of the DELETE statement makes sure that the rows you delete are the same ones that you archive.

If there's any chance you might have other locking contending with this, you might try using READPAST:

There aren't many places where I use READPAST but this is one of them. READPAST says if you come across a row that's locked, just skip over it instead of blocking.

A final note is that instead of an appropriate non-clustered index like I've used here, if you can just select blocks of data using whatever the clustering key is instead, that would be even better.


2 thoughts on “Minimizing locking while archiving rows from a main table in SQL Server T-SQL”

  1. Hi Greg,

    What happens with READPAST and the rowcount check if lots/most rows are locked. Worst case would be a table lock. If all rows are skipped and rowcount is zero does the check break the loop?

    Could we end up in a situation where rows are never being archived (again worst case scenario)?


    1. Hi Kent, yes, but it's hard to imagine that you're achieving anything either way if someone else has a table lock.

Leave a Reply

Your email address will not be published.