Deadlock on Delete Statement
What I am looking for is why I am getting a DELETE/DELETE deadlock.


It appears the deadlock occurs because:


spid 54 ecid 0 acquires an update (U) page lock on PAGE: 12:1:5147422
spid 166 ecid 3 requests an update (U) page lock on the same page, and is blocked 
spid 54 ecid 2 requests an update (U) page lock on the same page...


Pages are being prefetched for the query, with update locks acquired by ecid 0. That is step 1 above. In step 3, a child thread of the same parallel query (ecid 2) requests the same lock. Normally this would not be a problem. SQL Server knows ecid 0 and ecid 2 are threads of the same parent process. Unfortunately, step 2 gets in the way of this, and a deadlock results.

That said, you shouldn't really care much about why the deadlock occurs, the important question is how do you avoid it. The answer is to provide an efficient access path for the DELETE. The statement needs to find rows WHERE Username = @P1 AND UserDate = @P2, so you should have an index keyed on these columns.

And of course you do have such an index. The real question is why your problems started occurring after you added filtered indexes.

The answer to that is extra column information is needed to locate the filtered index rows to delete (and to check their predicates). If the query uses a narrow/per-row execution plan, the execution engine is unable to fetch the extra columns in the Clustered Index Delete operator, as it would in a wide/per-index plan.

You can find more details about that, and a worked example in this blog post.

In this case, the column information needs to come from the part of the plan to the right of the Clustered Index Delete, and so a parallel clustered index scan is used, and you get a slow query with high deadlock potential.

The answer is to do one of the following:


Remove the filtered indexes
Add filtered index key/include/predicate columns to the existing name/date index
Force a wide update plan (no supported way to do this)
Run the query under snapshot isolation (not RCSI)


Option 2 would be my strong preference.

Option 4 (thanks Jack Douglas) has the advantage of removing deadlocks, and ought not cause any "update conflicts", given the disjoint nature of the changes, but it does require enabling snapshot isolation at the database level, explicitly changing the isolation level, and will not fix the underlying issue: you'll still end up with a wasteful parallel table scan, where a nice index seek is what you really want.