Is SQL server's deadlock detection complete

Or more generally what are the basic features/restrictions of the
deadlock detection and killing mechanism of SQL server 2000.
Case in point:
I was able to simulate simple deadlocks of up to 6 processes within the
SQL Analyzer env., each time the deadlock formed it was got killed
immediately by the SQL Server, with a victim being chosen (I guess that
pattern may hold for more than 6 processes.)
On the other hand, sometimes we can find deadlocks in real production or
development testing that hang there without being auto killed.
How to explain this?
Thx for answers/comments.
cyu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SQL Server can only detect deadlocks on internal resources. If a deadlock
exists but some of the resources are outside of the SQL server, then you are
out of luck. SQL detects deadlocks by finding cycles within locks and lock
wait chains. A cycle or loop indicates a deadlock. The number of processes
in the loop is irrelevant as long as it is 2 or more. The victim is
supposed to be the process with the least resources expended (thus
theoretically, the easiest to rollback.)
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cyu" <cyu@.devdex.com> wrote in message
news:e107qZKBFHA.904@.TK2MSFTNGP12.phx.gbl...
> Or more generally what are the basic features/restrictions of the
> deadlock detection and killing mechanism of SQL server 2000.
> Case in point:
> I was able to simulate simple deadlocks of up to 6 processes within the
> SQL Analyzer env., each time the deadlock formed it was got killed
> immediately by the SQL Server, with a victim being chosen (I guess that
> pattern may hold for more than 6 processes.)
> On the other hand, sometimes we can find deadlocks in real production or
> development testing that hang there without being auto killed.
> How to explain this?
> Thx for answers/comments.
> cyu
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!