Chapter 6

Locking and Concurrency


  • ACID: the properties of transactions worth protecting
  • Avoiding concurrency dangers with locks
  • Lock resources and modes
  • A look at how lock escalation works
  • A brief description of deadlocks
  • Understanding how isolation levels affect locking behavior


The code downloads for this chapter are found at on the Download Code tab. The code is in the Chapter 6 download and individually named according to the names within the chapter.


Tommy Cooper, the late great comic magician, did a trick in which he put two handkerchiefs, one white and one blue, into a bag. He said a magic word, pulled them out again, and then stated that the white one had turned blue, and the blue one had turned white. It’s an excellent trick, though perhaps misunderstood, because the audience gets the impression that no change has occurred at all, and that he is simply pretending that the colors have swapped.

All joking aside, when you put something into a database, you have a certain level of expectation. You want to be assured that any data that has been entered can be retrieved in the same state, notwithstanding another process coming along and explicitly changing or deleting it. You don’t want any magic to wreak havoc while you’re looking the other way. In short, you want your transaction to be protected.

This is a challenge that transactional database vendors ...

Get Professional SQL Server 2012 Internals and Troubleshooting now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.