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 AliPHP DevleoperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 AliPHP DevleoperAuthor 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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Deadlock

From novice to tech pro — start learning today.