Deadlock on delete

Question

Deadlock on delete

Feivel

SSCertifiable

Points: 7524
More actions
August 30, 2016 at 10:03 am

#329749

Hi Experts,
I have a situation where the application tries to delete data from a table causing deadlocks during multiple times of the day. It is a simple delete statement with a filter on the id column. I am not sure what is the problem here and need some help in order to understand, and troubleshoot the issue.
Let me know if I do need to post anything else apart from the deadlock information.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You have two different delete statements, each one using a different WHERE clause, but both on the same table. So, first up, what other statements are involved with the transaction? Are you doing searches against the table with one, or both, of the delete statements using the same parameters as the delete statements? Next, what do the execution plans for these deletes look like? Are you getting seeks to the specific value to be deleted, or are you hitting scans? These are the things you need to check.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Feivel SSCertifiable Points: 7524 More actions · Answer 2

Grant Fritchey (8/30/2016)
You have two different delete statements, each one using a different WHERE clause, but both on the same table. So, first up, what other statements are involved with the transaction? Are you doing searches against the table with one, or both, of the delete statements using the same parameters as the delete statements? Next, what do the execution plans for these deletes look like? Are you getting seeks to the specific value to be deleted, or are you hitting scans? These are the things you need to check.

Thanks! Grant. I did run profiler to capture the statement, but I could see no other query in the batch. It is the only ad-hoc query that is running from the 3rd party application. I am not sure what values are getting passed into each of these delete statements, but they are certainly filtered on different columns, there might be a case where they would conflict in terms of the id values, and that is the reason it is not happening continuously, but within spurts. I've attached one of the actual plan, and I've seen pretty much these queries doing seeks on different non-clustered indexes. However, I also noticed the clustered index delete is causing way too much fragmentation everytime this statement runs. It might not be related, but is there way to reduce that?

Appreciate your help.

Thanks