• Skip to content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

Header Right

  • Home
  • Blog
  • Services
  • Pocket DBA™
  • Speaking
  • Contact
  • About

SQL 101 Blocking vs. Deadlocking – in English for the Non-DBA

October 30, 2015 by Kevin3NF 2 Comments

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

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: Uncategorized

About Kevin3NF

Reader Interactions

Comments

  1. Mary Elizabeth McNeely says

    April 18, 2016 at 12:39 pm

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

    Reply

Trackbacks

  1. The Apprentice: Locks and Blocks and Deadlocks….oh my! says:
    March 30, 2017 at 2:31 pm

    […] re-posted SQL 101 Blocking vs. Deadlocking – in English for the Non-DBA the other day for two […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • Index
  • Install
  • IRL
  • Management
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • PowerShell
  • Puzzle
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • vendor code modify
  • video
  • vNext

Copyright © 2019 · Genesis Sample on Genesis Framework · WordPress · Log in

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.