Lock Escalation - Pro SQL Server Internals - page 409

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 locks with a single table- or, in some cases, partition-level lock. Operation would
succeed if no other sessions held incompatible locks on the object or partition.
When an operation succeeds, SQL Server releases all row- and page-level locks held by the transaction on the
object (or partition), keeping the object- (or partition-) level lock only. If an operation fails, SQL Server continues
to use row-level locking and repeats escalation attempts after about every 1,250 new locks acquired. In addition to
the number of the locks taken, SQL Server can escalate locks when the total number of locks in the instance exceeds
memory or configuration thresholds.

■ The number of locks thresholds, 5,000/1,250, is an approximation. The actual number of acquired locks that
triggers lock escalation may vary.

Note

Let's look at the example. The first session starts the transaction in REPEATABLE READ transaction isolation level
and runs a SELECT statement that counts the number of rows in Delivery.Orders table. As you will remember, in this
isolation level, SQL Server keeps shared (S) locks until the end of transaction.
Let's disable lock escalation for this table with the ALTER TABLE SET (LOCK_ESCALATION=DISABLE) command
(more about this later), and look at the number of locks SQL Server acquires as well as at the memory required to
store them. In addition, while the transaction is still active, let's insert another row from the different session to
demonstrate how lock escalation affects concurrency in the system using a WITH ROWLOCK hint. This hint prevents the
situation in which SQL Server optimizes the locking by acquiring page-level shared (S) locks instead of row-level locks.
Figure 20-1 shows the code and results of the queries.