skumar.t15

 Profile Answers by skumar.t15 

• Aug 5th, 2010

 

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

kari

• Oct 16th, 2011

 

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.