Rectify Deadlock

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that could result in a deadlock.

Causes to Deadlock

Locks
Worker threads
Memory
Parallel query execution-related resources
Multiple Active Result Sets (MARS) resources

All the above listed may cause to Deadlock.

The default interval is 5 seconds.

If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.

If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.

You can find some more information regarding Deadlock

http://msdn.microsoft.com/en-us/library/ms178104.aspx

  2 Users have rated as useful.

  

Login to rate this answer.

the steps necessary to identify and rectify code causing deadlocks are:

* Locate the error messages in the alert log.
* Locate the relevant trace file(s).
* Identify the SQL statements in both the current session and the waiting session(s).
* Use these SQL statements to identify the particular piece of code that is having problems.
* Alter the application code to prevent deadlocks by always locking rows in the tables in the same order.