question

MSPSDBA avatar image
MSPSDBA asked

How should I avoid this deadlock?

Hi I am using SQL Server 2008 r2. I am using Microsoft Project Server that is creating deadlocks. As it is written by Microsoft, I cannot modify the code and want to deal with it on the SQL Server side. Could you please look into the attached deadlock diagrams and suggest me the solution for it? Thanks! ![alt text][1] ![alt text][2] [1]: /storage/temp/422-deadlock1.jpg [2]: /storage/temp/423-deadlock2.jpg
sql-server-2008sql-server-2005sql-server-2008-r2deadlocklocking
deadlock1.jpg (77.5 KiB)
deadlock2.jpg (86.0 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
What the 2 sets of queries running at the time to cause the deadlock?
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
Without the ability to see the actual queries, suggesting how to solve the deadlock itself will be very difficult. Remember that deadlocks at their base are performance issues. Despite the fact that evidently two different queries are written in such a way that they're accessing data in different orders, the most common cause of deadlocks, the main error occurs because the queries are too slow. The single best approach to fixing deadlocks is fixing the code. I know you say you can't change it, but that means you're going to have a harder time addressing the core issue. You can look at the queries and see if it's possible to put an index in place to speed things up. You can also look at instituting snapshot isolation as a means to reduce locking on the system. But you probably need to get the code adjusted.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.