Deadlock in SqlServer

Question

Hi friends, What are deadlocks in sqlserver? How we can handle them ?

Aarti · Answer

Hi Michell, A deadlock is a situation wherein two transactions wait for each other to give up their respective locks. Here are some tips on how to avoid deadlocking on your SQL Server: •Ensure the database design is properly normalized. •Have the application access server objects in the same order each time. •During transactions, don't allow any user input. Collect it before the transaction begins. •Avoid cursors. •Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server. •Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time. •If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK. •Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often. •If appropriate, use as low of an isolation level as possible for the user connection running the transaction. •Consider using bound connections. Please refer below link . I hope it will help you to handle Deadlocks. http://weblogs.sqlteam.com/mladenp/archive/2008/05/21/SQL-Server-2005-Immediate-Deadlock-notifications.aspx Thanks. Please mark it as accepted answer.

Aarti · Answer

Hi , There is nothing to prove about it. And this is not the place where i have to give explaination to others. I am just the member of this site and i am using this site for my knowledge enhancement and for fun. I don't want to be the part of such political and cheap discussions. So guys keep going with discussion and show your talent with these unprofessional and cheap chat. Thanks

Mayur Gujrathi · Answer

What I told is ony after considering things which are crystal clear If you are innocent then prove it

Aarti · Answer

Hi Mayur, I saw that link and I am really hurt alot that such type of remours are spreading about me. this is the bad part for this site.where people are doing such type of bad post. and last this is my biggest bad experience with this site. here people are busy in fighting ourselfves not for knowledge.

Sam Hobbs · Answer

Also see this .

Mayur Gujrathi · Answer

Aarti Have you visit thread exposing your bad work http://www.c-sharpcorner.com/Forums/Thread/149366/is-questions-and-answers-are-made-by-same-user.aspx

Javeed M Shaikh · Answer

Hi Michell, Please refer to the following article on deadlocks in SQL Server and how to avoid the same: http://support.microsoft.com/kb/169960 Please do not forget to mark "Accepted Answer".

Deadlock in SqlServer

Answers (7)