question

DaniSQL avatar image
DaniSQL asked

How to avoid/Mitigate Deadlock problems

I have an application that is only used every other weekend and used concurrently by about 60 people. It is a 2005 SP3 environment. I am told that the application was very slow and users had trouble using it effectively. The problem got so bad they had to change the server to a new 16 core server and 16GB at the beginning of these year. Last week the users started to use the application again but the problem is still there and when I do some tracing and I have found that there is a Deadlock situation almost every 10-20 min. Application has no mechanism to trap the error that is thrown by sql server and resubmit the transaction. So users are confused as to where their data goes after they submit their data and the application is painfully slow. On the dead lock graph I captured almost always the dead lock is created when sessions are trying to get exclusive access on indexes and below is an example of one of the select query that is frequently involved:

(@p0 int,@p1 int,@p2 int,@p3 int)

select coursecase0_.Id as Id45_, 
coursecase0_.Code as Code45_, 
coursecase0_.Description as Descript3_45_, 
coursecase0_.Status as Status45_, 
coursecase0_.Priority as Priority45_, 
coursecase0_.OrderNum as OrderNum45_, 
coursecase0_.KeyFindings as KeyFindi7_45_, 
coursecase0_.IsSAM as IsSAM45_, 
coursecase0_.DifficultyId as Difficul9_45_, 
coursecase0_.CourseId as CourseId45_ 
from CourseCase coursecase0_ 
where (coursecase0_.CourseId=@p0 )
    and(coursecase0_.Priority=@p1 )
    and(coursecase0_.Status=@p2 )
    and(not exists(select casetestpa1_.Id 
                    from CaseTestPass casetestpa1_ 
                    where (casetestpa1_.CourseTestPassId=@p3 )and(casetestpa1_.CaseId=coursecase0_.Id )))

I informed the developers to look into their application but in the mean time I need ideas on how to mitigate the problem from my side. I need advise on what to do with indexes to avoid the dead locks or at least decrease the frequency. I would also appreciate it if you also point me to a document about minimizing deadlock by better indexing.

Thanks,

sql-server-2005t-sqlindexingdeadlock
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered

Technically performance and deadlocks have nothing to do with one another. However, in the real world, the slower transactions are, the more likely any type of coding issue is going to run into some other type of coding issue and get a deadlock. So, in addition to trying to identify classic issues, tables being updated in different orders, etc., you should also tune performance so that you're getting less locking and therefore a reduced chance of deadlocks.

That SELECT query by itself shouldn't cause a deadlock. It must be part of a bigger process that also involves data updates of some kind.

Take a look at the execution plan for that query. See if it's doing scans instead of seeks, etc., to see if the indexes are being used appropriately. But, more importantly, figure out what that transaction is doing before or after this select statement that would be leading to a deadlock.

10 |1200 characters needed characters left characters exceeded

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.