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
Best Answer
It appears the deadlock occurs because:
spid 54 ecid 0
acquires an update (U
) page lock onPAGE: 12:1:5147422
spid 166 ecid 3
requests an update (U
) page lock on the same page, and is blockedspid 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 knowsecid 0
andecid 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 rowsWHERE 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:
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.