Locking Backgrounder

A quick post for today.  It's actually more of a storage engine concept, but you'd be surprised how much this can help in understanding update plans.  So, as I'm re-reading some of these to refresh my memory, I'll give you the chance to read along, so to speak.

For those interested in how and why locking works, there's a few various texts you can read.  "Transaction Processing: Concepts and Techniques" by Jim Gray and Andreas Reuter is a book you should read first.  It covers lots of things, but Part 4 (in my edition) is the section on concurrency and isolation levels.

So, after you're done with that short paperback <g>, you should read David Lomet's paper on some of the more interesting details of key range locking in practical, scalable implementations.  It's called "Key Range Locking Strategies for Improved Concurrency".  You can get it here: https://citeseer.ist.psu.edu/lomet93key.html.  While this isn't exactly what SQL Server does, it's close enough to give you a pretty good idea of what's happening under the covers.

I hope to post up a few examples to show how you can use the DMVs with a few of our queries to take a look at the locking behavior.

Enjoy!

Conor Cunningham