0

Why would sql-server lock monitor thread would not resolve deadlocks?

I don't have much details about this, but my friends complain about deadlocks occuring from time to time on SQL Server (2005 & 2008) databases.

As I know, Sql Server deadlock detection mechanism handles deadlock scenarios pretty well by choosing one of the processing as the victim and terminating/rolling it back, so the other process can complete, so no deadlocks would be left there to resolve.

Are there any conditions where SQL Server deadlock handling might fail, or it might be turned off?

thanks in advance

hazimdikenli
  • 5,709
  • 8
  • 37
  • 67
  • How did your friends determine that there was deadlock as opposed to just normal blocking. Perhaps from a transaction that was left hanging open indefinitely? – Martin Smith May 25 '11 at 12:28
  • yes, nice question, they detect locks with long durations, I dont know, if you can figure out from a lock info if it is causing a deadlock, may be they are just assuming those locks were caused by deadlocks:) – hazimdikenli May 25 '11 at 12:35

1 Answers1

1

Deadlock handling can only result in one connection being killed.

This is the only resolution to a deadlock sitution, which happens when 2 process are blocking each other. So neither can proceed and one must be aborted

gbn
  • 422,506
  • 82
  • 585
  • 676
  • so any reasons why there would be any deadlocks on the server? any reason for SQL Server to fail to resolve a deadlock situation? – hazimdikenli May 25 '11 at 12:23
  • @hazimdikenli: Deadlocks are only resolved by killing a connection. That is, SQL Server resolved a deadlock by killing a connection. It is that simple. Deadlocks **can not** be resolved silently and will always happen on RDBMS with locking regimes like SQL Server and Sybase – gbn May 25 '11 at 12:28
  • @hazim - There's tons and tons of materials on this. http://stackoverflow.com/questions/111707/zero-sql-deadlock-by-design-any-coding-patterns http://stackoverflow.com/questions/3374167/deadlocks-will-this-really-help – JNK May 25 '11 at 12:29
  • I understand the connection killing part, and as a result of that, victim's locks are gone, and the other process's transaction completes, there should not be any locks left over, maybe they are misintrepeting the locks as deadlocks. – hazimdikenli May 25 '11 at 12:37
  • 1
    @hazimdikenli: the SPID number will be re-used by SQL Server so it can appear that locks still exist for the same SPID number, even though it's completely new connection – gbn May 25 '11 at 12:48