SQL Server Lock Escalation

Introduction

Relational databases follow the ACID properties in how they implement transactions – Atomicity, Consistency, Isolation, and Durability. Isolation is necessary to ensure that multiple transactions can’t cause changes to data and leave the eventual results inconsistent. To guarantee that the operations remain isolated, SQL Server applies Locking mechanisms.

Lock Modes and Hierarchy

SQL Server’s mechanism for concurrency control is involved. To optimize performance in terms of lock waits, deadlocks, and the like, you have to make a decision based on the specific scenario. 

In SQL Server, locks can be held in various ways, and at several levels of granularity. Lock Modes are the specific ways to do is, and their levels are Lock Hierarchy. 

Figure 1 shows the Lock Modes available in SQL Server for the default Transaction Isolation level (READ COMMITTED): 

Overview of Lock Escalation

SQL Server can lock resources at several levels. It depends on the most efficient acts according to the nature of the workload. Table 1 shows the resources that can be locked.

• Locks at a more granular level (e.g., Row Level locks) allow higher concurrency and less blocking. 
• Locks at a higher level (e.g., Table Level Lock) reduce concurrency. They may cause more blocking, depending on how long the actual statement lasts.

SQL Server chooses the necessary locking level according to internal metrics. 

A Lock Escalation happens when a lock is converted from a finer level of granularity to a coarser level. 

E.g., converting a row lock to a table lock (See Table 1).

The Rationale for Lock Escalation

Locks in SQL Server can be quite expensive. For each lock the Lock Manager acquires, SQL Server must reserve memory – 64 bytes or 128 bytes. The amount depends on whether we are dealing with a 32-bit or 64-bit system, respectively. 

As the number of row locks on a table increases, SQL Server must acquire more and more memory. Hence, other processes are starving, out of memory.

It makes sense to convert row locks and page locks into a single table (object) level lock. It happens when the number of locks for that table exceeds 5000. 

The compromise occurs when the entire table is no longer available to other sessions in the transaction process.

Demonstrating Lock Escalation

We can demonstrate Lock Escalation using the code in Listing 1. 

Let’s first describe the table a little. Production.ProductsI is a relatively small table carrying about 7777 rows. The building elements are the same set of 77 rows duplicated 101 times. The Code in Listing 1 consists of three versions of the same update statement, each enclosed in a transaction.

-- Listing 1: Demonstrating Lock Escalation

-- Update very few rows
BEGIN TRAN

use TSQLV4
GO
UPDATE Production.ProductsI SET unitprice='100.00'
WHERE unitprice='18.00';

ROLLBACK

-- Update a large number of rows
BEGIN TRAN

use TSQLV4
GO
UPDATE Production.ProductsI SET unitprice='100.00'
WHERE unitprice>'18.00';

ROLLBACK

-- Update over 5000 rows
BEGIN TRAN

use TSQLV4
GO
UPDATE Production.ProductsI SET unitprice='100.00';

ROLLBACK 

For more clarity, we shall break down the contents of Listing 1. 

Before that, let’s observe Listing 2 – a query to display the locks held in the TSQLV4 database. 

Our first action is to execute Listing 1a. Then, we use Listing 2 to examine how the Lock Manager performs locking in the scenario. We execute Listing 1a without issuing the rollback statement. This way, we preserve the locks long enough, so that the query in Listing 2 can capture them.

-- Listing 1a: Demonstrating Lock Escalation
-- Update very few rows

BEGIN TRAN

use TSQLV4
GO
UPDATE Production.ProductsI SET unitprice='100.00'
WHERE unitprice='18.00';

ROLLBACK

-- Listing 2: Displaying Locks Held in Database TSQLV4

USE TSQLV4
GO
SELECT 
resource_type
, DB_NAME (resource_database_id) database_name
--, OBJECT_NAME(resource_associated_entity_id) resource_name
, request_mode
, request_type
, request_status
, request_reference_count
, request_session_id
, resource_associated_entity_id
, OBJECT_NAME(resource_associated_entity_id) [object_name] --small obj ids
, getuser.login_name
FROM sys.dm_tran_locks
CROSS APPLY dmv.dbo.getuser(request_session_id) as getuser
WHERE DB_NAME (resource_database_id)='TSQLV4';

When we run the query in Listing 1a, and then check the locks using the query in Listing 2, SQL Server returns the result shown in Figure 2. 

404 rows in the table have unitprice=’18.00’. The Lock Manager locks these rows along with the other locks of any level needed. It brings the Figure 2 row count to 467.

-- Listing 1b: Demonstrating Lock Escalation
-- Update a large number of rows
BEGIN TRAN

use TSQLV4
GO
UPDATE Production.ProductsI SET unitprice='100.00'
WHERE unitprice>'18.00';

ROLLBACK

We observe similar behavior when we execute the query in Listing 1b. This time, we are dealing with 4406 rows.  It reflects the number of rows on table Production.ProductI having unitprice>18.00. 

-- Listing 1c: Demonstrating Lock Escalation
-- Update over 5000 rows
BEGIN TRAN

use TSQLV4
GO
UPDATE Production.ProductsI SET unitprice='100.00';

ROLLBACK

When we go further and execute the code in Listing 1c, we view a different behavior (see Figure 4). 

Listing 1c attempts to update all 7777 rows in the Production.ProductI table. SQL Server determines that locking so many rows is no longer efficient to guarantee isolation. Instead, the entire table is locked. 

More on Lock Escalation

The table lock implies that no other session can modify its rows for the transaction duration, which may happen even when a blocking session does not manipulate all rows in the table.

It is also worth mentioning that other factors can affect how locks are acquired and escalated in SQL Server. Those are the isolation level configured, indexing, and trace flags.

Trace flags T1211 and T1224 can apply to disable Lock Escalation entirely. Lock Escalation can also get disabled and enabled for a specific table with the following code:

-- Listing 5: Disable and Enable Lock Escalation

ALTER TABLE Production.ProductsI SET (LOCK_ESCALATION=DISABLE);

ALTER TABLE Production.ProductsI SET (LOCK_ESCALATION=TABLE);

One may want to do it to reduce blocking associated locking the entire table. Because of the impact on memory, it should be considered on a temporary measure.

Conclusion

SQL Server uses Lock Escalation to control the impact of more granular locking on server resources. To display the way of these locks’ occurrences – row locks, page locks, object locks, etc. – query the sys.dm_tran_locks dynamic management view. It provides a lot of information about locking, besides Lock Escalation. 

While it is possible to manipulate the Lock Manager’s behavior, it is essential to do it with great care. It is also crucial to know the precise performance impact of any effort directed at making such modifications.

References

1. Korotkevitch, D., 2016. Pro SQL Server Internals. Florida: Dmitri Korotkevitch
2. Lock Scenarios Using Sys.dm_tran_locks
3. Transaction Locking and Row Versioning Guide