Resoving deadlocks

knaren1975
knaren1975 used Ask the Experts™
on
Hi

 please let me know , how can we resolve deadlock issues. Will it happens automatically or do we need to do anything to overcome this issue?

Thx
Naren
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
a typo error in the question..

Hi

 please let me know , how can we resolve deadlock issues. Will it resolve automatically or do we need to do anything to overcome this issue?

Thx
Naren

Commented:
deadlocks are usually when a user (via query) or a process is accessing a certain table....

Here's an article on how to detect the deadlocks and how to end them if necessary:

http://msdn.microsoft.com/en-us/library/aa213030(v=sql.80).aspx
http://support.microsoft.com/kb/832524

there's even a software if you like a nice gui front end:

http://www.sqlsolutions.com/products/sql-deadlock-detector/index.html
Commented:
I assume you are building an application, and looking for ways to deal with possible deadlock.

Deadlock happens when two or more database tasks lock each other permanently. For instance transaction1 hold a lock on tableA, and about to acquire lock to tableB, on the other side transaction2 hold a lock on tableB, and about to acquire lock to tableB.

SQL Server will automatically resolve deadlock by choosing one of the transaction as deadlock victim.

Deadlock is not always avoidable, but the chance of this happen can be minimized. And application should be designed with exception handling when deadlock happens.

There are several things to minimize deadlock :
- When possible, access tables with the same order in the application.
- Make sure database design is normalized
- Reduce lock time by grabbing lock at the latest possible time, and release as early as possible.
- Don't allow user input during transaction.
- Keep transaction as short as possible. This can be achieved with proper index so that any where clause in the query hit correct index.

To resolve deadlock, when the transaction becomes deadlock victim, it depends on what programming language that you use. If  you use e.g. C#,  it has try catch statement and you should catch the error number 1205. This is the error number raised when SQL Server terminate a session because of a deadlock.

Source :
http://msdn.microsoft.com/en-us/library/aa892828(v=ax.50).aspx
http://www.sql-server-performance.com/2006/deadlocks/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial