Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
I must second Grant's suggestion - SQL Server 2008 Internals by Kalen Delaney is an AWESOME resource! It sits on my desk at all times. –  OliverAsmus Feb 13 '12 at 19:31
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.