Plain ‘ol English….
Blocking:
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.
Deadlocking:
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!
Kevin3NF
