I re-posted SQL 101 Blocking vs. Deadlocking – in English for the Non-DBA the other day for two reasons:
- Its good info for new DBAs struggling to understand the interaction and differences in these terms
- 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!