Table Size Influences Deadlock Susceptibility
When a heap is small, say under a few thousand rows, SQL Server primarily relies on exclusive (X) RID locks and a few intent exclusive (IX) or (UIX) page locks during data deletion, of course, depending on the amount of data deleted in the transaction. With this smaller size, you typically don’t encounter deadlocks unless you access the table in a pattern that tends to generate them.
However, when a heap grows to many thousands of rows, SQL takes more locks. If the threshold of lock escalation is met, an object (table) lock occurs. These factors all contribute to rampant deadlocks.
This is why one day you start to see deadlocks on a table that didn’t experience them even without any code changes.
If possible, consider adding a clustered index to see if it helps. Heaps are great for many things, but are not usually ideal for updating and deleting thousands of rows.
To view the locks in action, take a look at the DMV sys.dm_tran_locks.
