How to solve blocking issue

Question

How to solve blocking issue

New persopn

SSCarpal Tunnel

Points: 4019
More actions
December 10, 2012 at 7:42 am

#393772

after finding blocking spid.
How to solve that problem.
deadlock and blocking both are same are different.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

You can always kill one spid or the other, but you should know the impact of that first. As to what you meant by "deadlock and blocking both are same are different.", I have no idea what you're trying to say.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 2

Here for blocking we need to kill the sp id.

how to solve the deadlock issue.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

Deadlock is a condition where two or more queries go after resources that the others are using, and in at least 2 cases, an "other query" needs an exclusive lock and can't get it because it's already in use, and because the other query holds an exclusive on something the 1st query needs, neither query can EVER succeed. It's your basic "catch 22" situation. Read the following page, and it does a far better job of describing it than I can:

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

Solving such a problem is often a performance related problem. Get the queries that deadlock to run faster, and there's less opportunity for this to occur. Also, running queries that don't actually need an exclusive lock with query hints that provide one or in ways that cause one can be things to look for and eliminate, if possible. It's not always possible. As SQL Server detects deadlock and randomly chooses a victim, another way to solve the problem is to reschedule things, if that's an option.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

sgmunson (12/10/2012)
You can always kill one spid or the other, but you should know the impact of that first. As to what you meant by "deadlock and blocking both are same are different.", I have no idea what you're trying to say.

Don't do it. Killing a SPID may leave it in a never ending rollback that will consume the better part of an entire CPU. Far better to find the person causing the blocking and have them cancel whatever they're running.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

gantavasu (12/10/2012)
Here for blocking we need to kill the sp id.
how to solve the deadlock issue.

It's normally not a simple thing and it's far too complex to explain on a forum post. Please do the following... From SSMS, press the {f1} key and search for DEADLOCKS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapters 6 and 7

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sgmunson SSC Guru Points: 110639 More actions · Answer 7

Jeff Moden (12/10/2012)
sgmunson (12/10/2012)
You can always kill one spid or the other, but you should know the impact of that first. As to what you meant by "deadlock and blocking both are same are different.", I have no idea what you're trying to say.
Don't do it. Killing a SPID may leave it in a never ending rollback that will consume the better part of an entire CPU. Far better to find the person causing the blocking and have them cancel whatever they're running.

Thanks for the correction. I was not aware of that particular gotcha, and it's always good to learn BEFORE it bites you as opposed to after.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Here is a 3-part blog post about deadlocking from Bart Duncan: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service