Resources for understanding SQL Server locking and concurrency?

Question

As demonstrated by a recent question of mine locking and concurrency are HARD.

Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that would, properly learned, enable better navigation of all the inherent pitfalls in this area?

I'm thinking of all kinds of resources--tutorials, blogs, manual pages, PASS sessions, or anything.

Answer 1

Best book on the subject, and the most complete resource, is Kalen Delaney's SQL Server 2008 Internals. You really can't do any better. Another good book on the subject is Chris Bolton's SQL Server 2008 Internals and Troubleshooting. I don't think it's as complete as Kalen's book, but it does cover things pretty well, especially around locking and concurrency.

Answer 2

• Transaction Processing: Concepts and Techniques.
• Readings in Database Systems
• Principles of Transaction Processing

While product specific books do a great job at explaining how to use the products (and Kalen Delaney's series is awesome!), they don't really stand a chance against the 'black book' and the 'red book' of the database systems. Specially the black book (the first link) is pretty much a mandatory reading if you want to understand these concepts. The red book is a collection of research papers, many available online. I keep a page with links to some of them. The third book linked is basically a more up-to-date rewrite of the 'black book' (also cheaper and available on Kindle...)

There are also several good blogs, I would first and foremost recommend the SQL Server CSS blog.

Answer 3

Locking and concurrency is a topic I learnt most about through testing and observation.

Start by reading Isolation Levels in the Database Engine and Concurrency Effects, so you have an understanding of the relationship between the two. Now you can experiment:

• Construct the SQL for a test transaction. Something simple and self-contained with a context/domain you understand e.g. shopping cart checkout or bank transfer. Ensure you have a method of verifying that the data is in the expected state after testing.
• Run your scenario, observing the type, duration and sequence of locks applied by the database engine.
• Alter the isolation level or apply hints and observe the change to the locks taken.
• When you think you have a transaction that will survive concurrency, load test it.

I tend to check lock sequences with trace flag 1200 or a Profiler trace. I believe extended events could also be used but I've not tried myself as yet.

Answer 4

One other resource is Kendra Little's clever poster on isolation levels. There are also links to her presentation, resources and some discussion.