Pro SQL Server Internals

CHAPTER 20

Lock Escalation

Although row level locking is great from a concurrency standpoint, it is expensive. In memory, lock structure uses 64 bytes in 32-bit and 128 bytes in 64-bit operating systems. Keeping information about millions of row- and page-level locks would require SQL server to allocate gigabytes of RAM to store them.

SQL Server reduces the number of locks held in memory with a technique called Lock Escalation, which we will discuss in this chapter.

Lock Escalation Overview

Once a statement acquires at least 5,000 row- and page-level locks on the same object, SQL Server tries to escalate, or perhaps better said, replace, those ...