Plain ‘ol English….
You walk into Starbucks and there’s 1 guy ordering. He finishes, then you order. That’s blocking, and its perfectly normal, expected and acceptable.
You walk into Starbucks and there’s 1 guy ordering for 15 people in his office. You’re willing to wait, but not forever. That’s moderate blocking.
You walk into Starbucks and there’s 1 guy ordering, 7 people in line and they are all ordering drinks with names too long to remember….you walk out. That’s excessive blocking with a failed transaction (you).
Blocking in SQL Server is just transactions that need the same resource lining up to take their turn. Its by design and is a good thing. Excessive blocking needs to be fixed.
You have 2 children. Each has a toy, and at the same time they each grab for the toy the other one has, but won’t let go of the first one. This is a Deadlock. SQL Server has a built-in process to resolve this. They gave it an official name, but I call it “Mom.” Mom steps in and breaks it up…automatically. This is a code issue much of the time…profile it, trace flag it, whatever..but find and fix your application code. If its not your app code, it could be just performance related…tune or rebuild some indexes may get you there. Start HERE for troubleshooting deadlocks.
Does that help?
They are NOT the same thing…Deadlock is bad, Blocking is part of the engine…to a point.
Next up (maybe): Replication and Log Shipping are not the same thing…quit using them interchangeably, especially when you mean “Clustered”
Have a nice day!
Mary Elizabeth McNeely says
I use ladders to explain deadlocks: People can walk up the ladder or down the ladder, but everyone has to choose the same direction on any given ladder. If someone walks up the ladder from the bottom while someone else is walks down from the top, when they meet, either they'll be stuck indefinitely, or someone has to back out. Row access works the same way – if everyone walks/locks rows/ladder rungs in the same order, you'll have the above-described Starbucks situation, at the worst.
That said, using young children with toys as analogies is valid for many problems, both modern and of-old.
I enjoy your posts. Hope you don't mind an Oracle DBA reading your stuff. 🙂