The Apprentice: Locks and Blocks and Deadlocks….oh my!

I re-posted SQL 101 Blocking vs. Deadlocking – in English for the Non-DBA the other day for two reasons:

  1.  Its good info for new DBAs struggling to understand the interaction and differences in these terms
  2.  It was next on the list to walk the Apprentice through and he reads my Tweets 🙂

We had about an hour to spend working through this so we briefly covered the article, using Starbucks and Chick-Fil-A interchangeably as something you almost always have to wait in line for.

Things we managed to cover, test, or define in that one hour:

  • Lock: When a customer “Sally” (query1) walks up to the cashier “Ken” (Resource1), she has locked him into taking her order.
    • Ken is a CPU here, or a Row/Page/Table…don’t overthink my analogies 😀
  • Block: The dude “Broseph” (query2) behind Sally (query1) has to wait…he’s blocked.
  • If the manager (Query Optimizer) sees that Sally is ordering 20 drinks for the office, he may open a second register and have Joe (Resource2) starting ringing up some of Sally’s orders.  Brospeh is still in line, waiting.  Sally has gone parallel.
  • If the manager decides Ken needs to work all of Sally’s order himself (MAXDOP1), he may open a second register (Joe/resource2) and move Broseph to that line.
  • If Max (query3) walks in, sees what is going on and decides he doesn’t really want coffee…he just rolls on back out the door and leaves a 1-star review on Yelp (failed transaction, retry?)

Other stuff we covered, in no particular order:

  • Deadlocking and the Mom process (see above link)
  • INSERT statements to create sample blocking
  • IMPLICIT and EXPLICIT transactions, so the tests actually work
  • 4 parts of an object name [instance].[database].[schema].[object]
  • Why a sysadmin cannot directly query Instance2 from Instance1, regardless of his level of sysadmin-ness unless…
  • …Linked Server
  • sp_lock
  • master..sysprocesses (old skool)

Good times were had, jokes were made, stuff was learned.  Oh…and every time we meet I ask him random stuff from previous meetings to gauge retention.   So far so good.

Thanks for reading!

Kevin3NF

Leave a Comment

Sign up for our Newsletter

%d bloggers like this: