Snapshot on, still deadlocks, ROWLOCK
- by Patto
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
