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

The way to do this to reduce (or avoid) deadlocks is to delete in batches with a short wait between each batch (using WAITFOR DELAY)

Also, deadlocks can be mitigated by having a covering index.

This code requires a few declares, and it is as an example only (Run at own risk!).

SELECT  @intRowCount = 1,
    @intErrNo = 0

DECLARE @cutoff_date DATETIME

SET @cutoff_date = dateadd(minute,-60, getdate())

SELECT  @intRowsToDelete = COUNT(*) -- Number of rows to be deleted
FROM dbo.statValue 
WHERE temporaryStat = 1
AND  entrydate < @cutoff_date

WHILE @intRowCount > 0 AND @intErrNo = 0
BEGIN

    SET ROWCOUNT @DEL_ROWCOUNT

    delete statvalue
    FROM dbo.statValue 
    WHERE temporaryStat = 1
    AND  entrydate < @cutoff_date

    SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

    SET ROWCOUNT 0 -- Reset batch size to "all"

    SELECT @intRowsToDelete = @intRowsToDelete - @intRowCount

    WAITFOR DELAY '000:00:
 END

I've also included John's suggestion of not repeatedly calculating the date range criteria.

Answer 2

Please re-write your delete query like so:

DECLARE @cutoff_date DATETIME

SET @cutoff_date = dateadd(minute,-60, getdate())

delete statvalue
from statValue
where temporaryStat = 1
and entrydate < @cutoff_date

You will see a cost reduction in your execution plan

Answer 3

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.