Sounds like this should be desirable behaviour! Because if you are deleting so many records, and one of your stores reads that deleted record and does something with it, then they'd have invalid data... of course, I'm not totally sure what you are trying to do. Same with inserts - if the store did a report that relied on knowing what you had at a point in time, and it *under* reported, then again they aren't getting accurate info.

Sorry, just realised the way that your table has been put together.

You can actually stop lock escalation in SQL Server... though that seems a bit drastic. But if you look at the following article it will tell you how to do this. Use at your own risk though!

However.. I've never really tried using it, but perhaps snapshot isolation might help? Of course, that would put contention on TempDB... someone might want to chime in here if I'm telling anything misleading 🙂