Snapshot on, still deadlocks, ROWLOCK

Question

I turned snapshot isolation on in my database using the following code

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

and got rid off lots of deadlocks.

But my database still produces deadlocks, when I need to run a script every hour to clean up 100,000+ rows.

• Is there a way I can avoid deadlocks in my cleanup script, do I need to set ROWLOCK specifically in that query?
• Is there a way to increase the number of row level locks that a database uses?
• How are locks promoted? From row level to page level to table level?

My delete script is rather simple:

delete statvalue
from statValue,
(select  dateadd(minute,-60, getdate()) as cutoff_date) cd
where temporaryStat = 1
and entrydate < cutoff_date

Right now I am looking for quick solution, but a long term solution would be even nicer.

Thanks a lot, Patrikc

Answer 1

SNAPSHOT isolation can only mitigate (some) of the deadlocks involving reads, but it does absolutely nothing to avoid write vs. write deadlocks. If you generate 100k+ rows per hour that is ~30 inserts per second, so the delete scan is pretty much guaranteed to conflict with other write operations. If all you do is Insert, never update, then the delete block, but not deadlock at row level lock, but because the table is big enough and the delete is doing a scan, the engine will likely choose a page lock for the delete, hence probably the deadlock you get.

w/o an index on the entrydate the delete has no choice but to scan the entire table. This sort of tables that get frequently inserted at the top and deleted at the bottom are in fact queues and your should organize them by the entrydate. That means entrydate should probably be the leftmost key in the clustered index. This organization allows for a clear separation of the inserts occuring at on end of the table vs. the deletes occuring at the other end. But this is a rather radical change, specially if you use the statvalueid to read these values. I guess right now you have a clustered index based on an auto-increment field (StatValueId). Also I assume that the entrydate and the statvalueid are correlated. If both assumptions are true, then you should delete base don the statvalueid: find the largest id that is safe to delete, then delete everything on the clustered index left of this id:

declare @statvalueidmax int;
select @statvalueidmax = max(statvalueid) 
 from statvalue with (readpast)
 where entrydate <  dateadd(minute,-60, getdate());

delete statvalue
 where statvalueid <= @statvalueidmax;

There are a number of assumptions I made, they may be wrong. But the gist of the idea is that you have to separate the inserts from the deletes so they don't overlap.