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