Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was rigorously updating a table within a CURSOR.
This is the first time I've seen this error and didn't know how to track and fix it effectively. I tried all the possible ways I know, and finally found that the table which is being updated doesn't have a PRIMARY KEY! luckily it was an identity column.
I later found the developer who scripted database for deployment messed-up. I added a primary key and the problem was solved.
I felt happy and came back to my project, and did some research to found out the reason for that deadlock...
Apparently, it was a circular wait condition that caused the deadlock. Updates apparently take longer without a primary key than with primary key.
I know it isn't a well defined conclusion, that is why I'm posting here...
- Is the missing PRIMARY KEY the problem?
- Are there any other conditions which cause deadlock other than (mutual exclusion, hold and wait, no preemption and circular wait)?
- How do I prevent and track deadlocks?