Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 6. Locking and Latches

WHAT'S IN THIS CHAPTER

• SQL Server's transactional model, with particular focus on isolation

• Locking internals and architecture

• Extensive coverage of both pessimistic and optimistic concurrency

• An analysis of the internals of SQL Server's version store

• An in-depth look at latches

Ask yourself this question: What is a lock trying to achieve? Any process that your application has to go through prior to getting to the data represents added overhead, so what is a lock's purpose?

In Chapter 1 the ACID properties of a transaction were described as the stepping-stone for understanding the life span of a query. The chapter then walked you step-by-step through an example of the sequence of events that a solitary select would need to negotiate prior to a user receiving data. This was followed by a similar pattern for the next example, that of an update. These examples were run through sequentially, as you'd expect in a book, and so the data from one query to the next was always isolated from the other as each query took its turn to execute. In this scenario there is no need for locking; a single view of the data is maintained by synchronously executing the examples.

However, suppose there were 10 different queries in the chapter, or 100 or even 1,000, but this time they are all running at once. Imagine if everyone reading the book at this very moment (here's hoping that's a lot of people) were sending their example queries to a central database. Everyone would be reading ...