Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm an accidental DBA and still learning.

When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?

Should I simply kill those processes or is there a way to "release" or maybe preemptively preempt this blocking?

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

KILL

Sometimes, ultra violence is the answer

Anyway, a block is just a lock that goes on for too long. "Too long" is variable of course. if you see blocking via sysprocesses or the DMVs then it's almost always "too long" as blocks should be transient and very short

If you do have measurable blocking, then you need to investigate why eg long running transaction

share|improve this answer
2  
Highly quotable answer. –  Nick Chammas Aug 19 '11 at 19:26
    
Thanks, when the blocking is killed, is the transaction rolled back (as is it never occurred) and the data (e.g blocked INSERT statement) will not be written into the database? –  jrara Aug 19 '11 at 19:31
    
@jrara: yes and yes –  gbn Aug 19 '11 at 19:35
add comment

Despite this question having already being answered and the answer accepted, I'm going to try and make a case for the contrary approach...

DO NOT EVER BLINDLY KILL A SPID

Unless you know what work that SPID has done, you have no understanding of the scale of the rollback you're about to initiate.

The worst kind of blocking chains are those which have unexpectedly brought your 24/7 system to its knees. 99% of the time, the query at the head of that blocking chain is going to be a plain old select query gone awry, queueing up any and all write activity behind it. In these circumstances, a KILL will save the day.

The other 1% of the time, that rogue query will be the last step in a long running transaction that will take as long to rollback as it did to get to the stage it's at now. Taking a moment to understand how you got here will 99% of the time be unnecessary. 1% of the time, you could be saving minutes/hours/days of downtime.

share|improve this answer
1  
+1. It is a good point to identify which process to kill due to the rollback issue. –  StanleyJohns Aug 20 '11 at 2:49
    
A long running txn isn't usually a big one : it can be caused say by a client commandtimeout event. And you still have to kill the SPID and it still needs to rollback in all cases. –  gbn Aug 20 '11 at 5:14
    
You still need to KILL it unless you can release the block, say by a commit. If you can't, you don't have many choices: unless you want to stop SQL Server, delete the MDF/LDF, restart SQL Server, restore the database. –  gbn Aug 20 '11 at 11:00
1  
I disagree @gbn. If the process at the head of a blocking chain is close to completion, why kill it and force a rollback? My point is that rather than just killing it, its worth taking a moment to assess whether it's about to complete and therefore release its blocking locks. It may be that kill is the best option but it shouldn't be the default reaction. –  Mark Storey-Smith Aug 20 '11 at 12:15
add comment

Blocking, in and of itself, isn't bad.

The problem comes when you have performance issues from long-running transactions that are blocking other transactions.

In this case, what I would do is to try to identify any long-running queries/transactions and try to fix those problems.

If you're having performance issues, try to fix the root cause, not the symptom.


Having said that, if you're having an issue on production and you absolutely need to stop it, use the KILL routine. (as gbn mentioned).

share|improve this answer
add comment

Locking is normal. It happens all the time with conflicts. It's the constant deadlocking that is an issue, as the database engine chooses a victim process to kill and rollback the transaction. Statistics prove that this is bound to happen, but if it is happening constantly then you need to drill down into the causes and rewrite queries and code logic.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.