Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners

© Dmitri Korotkevitch 2018

Dmitri KorotkevitchExpert SQL Server Transactions and Lockinghttps://doi.org/10.1007/978-1-4842-3957-5_7

7. Lock Escalation

Dmitri Korotkevitch¹ 

(1)

Land O Lakes, Florida, USA

 

Although row-level locking is great from a concurrency standpoint, it is expensive. In memory, a 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 use gigabytes of memory.

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

SQL Server tries to reduce memory consumption and the overhead of lock management by using the simple technique called lock ...