7

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?

4 Answers 4

5

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

3
  • 2
    Highly quotable answer. Aug 19, 2011 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, 2011 at 19:31
  • @jrara: yes and yes
    – gbn
    Aug 19, 2011 at 19:35
7

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.

4
  • 1
    +1. It is a good point to identify which process to kill due to the rollback issue. Aug 20, 2011 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, 2011 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, 2011 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. Aug 20, 2011 at 12:15
4

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).

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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