SQL Server Lock Escalation

{coding}Sight
Oct 26 · 6 min read
Image for post
Image for post

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):

Image for post
Image for post
Figure 1: Lock Modes and Compatibility (Source: Microsoft)

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

Image for post
Image for post
Table 1: Resources That Can be Locked in SQL Server (Source: Microsoft)

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 TRANuse TSQLV4
GO
UPDATE Production.ProductsI SET unitprice='100.00'
WHERE unitprice='18.00';
ROLLBACK-- Listing 2: Displaying Locks Held in Database TSQLV4USE 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.

Image for post
Image for post
Figure 2: Row and Page Locks (467 Rows)
-- 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.

Image for post
Image for post
-- 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.

Image for post
Image for post
Figure 4: Object Lock

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.

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 EscalationALTER 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 with locking the entire table. Because of the impact on memory, it should be considered as 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.

Originally published on https://codingsight.com/sql-server-lock-escalation/, a community platform for database administrators and Microsoft stack technologies specialists.

Subscribe here to our digest to get SQL Server industry insides 👋

The Startup

Medium's largest active publication, followed by +732K people. Follow to join our community.

{coding}Sight

Written by

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/

The Startup

Medium's largest active publication, followed by +732K people. Follow to join our community.

{coding}Sight

Written by

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/

The Startup

Medium's largest active publication, followed by +732K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store