SQL server SPID blocking all processes

[siituser]

Hi there,

We are running SQL server SP3 and a Coldfusion server on the same box. We manage our client variables in Coldfusion by using a SQL database. In the last 2 weeks, there have been very regular occurances of one SPID blocking all the others and hanging our applications.

If I manually kill the SPIDs that are updating or deleting, I can get the app the run again.

Is there a way to have SQL server automatiicaly kill a process that is blocking or being blocked for x period of time?

 

[miq]

HI,

first of all yes, sqlserver does have a deadlock monitor which detects are automatically kill deadlock victim. Usually, the victim is the one which is least expensive in terms of resource consumption. Victim transaction is rollbacked and an error message is returned with number 1205. So, you should program your application to check for this error and redo work or whatever suitable.
You cannot tell sqlserver who should be deadlock victim but you can suggest by using DEADLOCK_PRIORITY.

To avoid deadlocks make sure you release locks as soon as possible and proper lock should be held not higher one.
Like, for simeple select statements don't use updlock hint instead use nolock.

B.R,
miq

 

[miq]

HI,
you can also use SET LOCK_TIMEOUT delay_timeout to allow your batch timeout after specified period. @@LOCK_TIMEOUT to check the current time_delay setting.

give a consideration to sp_dboption 'query_wait' as well.

The best thing is to check/audit which process is blocking, what statement(s) that process was trying to execute and who is the victim.

This way you can easily find culprit statemet or action query. This can doen using sp_who and sp_lock

B.R,
miq