Professional SQL Server 2012 Internals and Troubleshooting

Chapter 6

Locking and Concurrency

WHAT’S IN THIS CHAPTER?

• 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

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at www.wrox.com/remtitle.cgi?isbn=1118177657 on the Download Code tab. The code is in the Chapter 6 download and individually named according to the names within the chapter.

OVERVIEW

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 ...