Dead Lock on Sql Server

HI, i have been facing dead lock in my database servers, before it was ok, suddenly this issues come up. please help me for this.

SQLState: 40001 Error Code: 1205 Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Transaction (Process ID 332) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Kashif AliAsked:
Who is Participating?
 
pcelbaCommented:
Deadlock is caused by improperly written application so to help without the app knowledge is almost impossible here.
Deadlock can happen e.g. when one part of the application updates Order lines and then Order header (in one transaction) whereas other part of the application or a different application updates Order header and then Order lines (again in a transaction). If these two transactions start at the same time then the first update waits for Order header and the second one waits for Order lines... and SQL Server evaluates this situation as a deadlock.

The chain of updated tables can be much longer and SQL Server does not recognize such situation in some cases. Then you have to kill the head blocking process manually or you have to set up some query timeout.

So you have to identify queries which are causing deadlocks in your apps and fix them.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Nothing much to add to what pcelba's comment besides that you can try to use SQL Profiler to run a trace on the server to catch the queries and understand which ones are the root cause for the deadlocks.
0
 
Kashif AliAuthor Commented:
I have already run sql profiler but i did not found queries which causing dead lock,  my application have continously bulk insert after few seconds, and also read. Can you take rdp session and identify issue please..
Before every thing was working fine.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pcelbaCommented:
Your tables are growing and the processing time is getting slower. This is obvious but it also brings higher probability of deadlocks when the app design is not optimal or even incorrect.

You have to log all queries and then search the results. Deadlocked query should be visible at the first view.

The Profiler setup is described e.g. here: https://www.red-gate.com/simple-talk/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

More readings about deadlocks: https://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

https://technet.microsoft.com/en-us/library/ms177453(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms191242.aspx
https://msdn.microsoft.com/en-us/library/ms178104.aspx
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can enable the trace flag 1222 to log the deadlocks. When you got one then check the log for the information about the query or queries. If you have difficulty to understand the log then share it here with us so we can help you further.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.