Deadlock on Delete Statement

Question

I get a deadlock when a SQL Server Job runs. The deadlock occurs on a simple DELETE statement. I would have thought there would have to be a SELECT/UPDATE query running to cause the deadlock? But looks like it is DELETE/DELETE deadlock...

What I am looking for is why I am getting a DELETE/DELETE deadlock. It is (to my knowledge) passing in different parameters.

Any ideas? Thanks.

deadlock-list
2014-05-20 07:30:09.66 spid25s      deadlock victim=process409048
2014-05-20 07:30:09.66 spid25s       process-list
2014-05-20 07:30:09.66 spid25s        process id=process409048 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127294 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x397219620 lockMode=U schedulerid=5 kpid=3792 status=suspended spid=150 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)     
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown     
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process432e08 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=2648 ownerId=629859744 transactionname=DELETE lasttranstarted=2014-05-20T07:30:04.833 XDES=0x4c3426b50 lockMode=U schedulerid=6 kpid=5988 status=suspended spid=146 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:04.833 lastbatchcompleted=2014-05-20T07:30:04.820 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629859744 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)     
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown     
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process39ea562c8 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x13e0e4b50 lockMode=U schedulerid=2 kpid=7124 status=suspended spid=150 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2

Answer 1

What I am looking for is why I am getting a DELETE/DELETE deadlock.

It appears the deadlock occurs because:

1. spid 54 ecid 0 acquires an update (U) page lock on PAGE: 12:1:5147422
2. spid 166 ecid 3 requests an update (U) page lock on the same page, and is blocked
3. 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:

1. Remove the filtered indexes
2. Add filtered index key/include/predicate columns to the existing name/date index
3. Force a wide update plan (no supported way to do this)
4. 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.