December 10, 2012 at 7:42 am
after finding blocking spid.
How to solve that problem.
deadlock and blocking both are same are different.
December 10, 2012 at 7:46 am
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)
December 10, 2012 at 7:49 am
Here for blocking we need to kill the sp id.
how to solve the deadlock issue.
December 10, 2012 at 8:06 am
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)
December 10, 2012 at 8:45 am
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
Change is inevitable... Change for the better is not.
December 10, 2012 at 8:47 am
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
Change is inevitable... Change for the better is not.
December 10, 2012 at 10:36 am
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
December 10, 2012 at 10:58 am
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)
December 11, 2012 at 6:52 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy