November 22, 2002 at 6:38 pm
When SQL Server does its deadlock detection thing, does it check all the involved processes to see that they have no lock timeout set? Or does it just notice a blocked process with interlocked resources?
Other than just running profiler and saving all queries up to a deadlock time, then looking at each, is there any automation floating around that will identify the queries of each involved processes when a deadlock is detected? I don't even think the error message tells you what the other processes are, does it?
November 23, 2002 at 3:59 am
you would need to check for spids that are blocking each other in query analyser and also for spids that have are in the same distributed transaction.
Lock timeout is irrelevant in a deadlock because by definition it is a deadlock even if both processes waited 100 years they still wouldn't complete.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 23, 2002 at 7:16 am
quote:
Lock timeout is irrelevant in a deadlock because by definition it is a deadlock even if both processes waited 100 years they still wouldn't complete.
I disagree. Consider that if one of the processes involved in a potential deadlock has a timeout set, it will automatically resolve the deadlock when it times out and (presumably) fails, rolls back and releases the locks. It might be a mechanism one could use to "avoid" deadlock situations by ensuring one particular one would be the one to give up.
However, I think I've answered my own question. I set up two query analyzer sessions and created a deadlock situation with and without a timeout set. Both detected the deadlock and exited. So it appears in SQL Server a deadlock is defined as interlocking lock waits even if one is going to time out soon.
November 23, 2002 at 8:59 am
It's my opinion that both of you are correct.
Simonsabin is correct, in that by definition, which is what he says, it is a deadlock even if both processes waited 100 years they still wouldn't complete.
Ferguson, you are correct as well. Sql detects this condition (although I've found not 100% of the time), and corrects for it.
The best information I've found on this came from Inside Microsoft SQL Server 2000, and has very detailed schema diagrams and definitions of the locking architecture. The deadlock detection is performed by the LOCK_MONITOR thread every 5 seconds. There is an internal counter called a deadlock detection counter which starts out at 3 and is reset to 3 if a deadlock condition is detected, or is decremented by one if none is detected. If the counter has a value greater than 0, the lock manager requests that the lock monitor also check all the locks for a deadlock cycle if a process requests a lock resource and is blocked. So, after 20 seconds of not findong any deadlocks, the counter is 0 and the lock manager stops requesting deadlock detection every time a process blocks on a lock.
Keep in mind that deadlocks can occur on resources other than locks, as well. threads, communication buffers, and memory resource shortages can cause them.
The commonly chosen victim will be the one which costs the least to roll back considering the work ALREADY performed. This is not always the case, as there are exceptions, and this behavior can be changed with the SET DEADLOCK PRIORITY (LOW / NORMAL), so that threads are able to sacrifice themselves.
November 23, 2002 at 4:49 pm
The only situation I am aware that SQL does not detect this is when you have distributed transactions across spids. This was not detected in SQL 7 (big probelm with MTS/COM+ and bad coding). This is now fixed in SQL 2000.
What happens in this situation is taht the comamndtimeout or the MTS/COM+ transaction timeout kick in and kill one of the transactions
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply