O'Reilly logo

Pro SQL Server Internals by Dmitri Korotkevitch

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

CHAPTER 20

image

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required