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).
|RID||The row identifier used to lock a single row within a heap.|
|KEY||The row lock within an index used to protect key ranges in serializable transactions.|
|PAGE||The 8-kilobyte (KB) page in a database, such as data or index pages.|
|EXTENT||The contiguous group of eight pages, such as data or index pages.|
|HoBT||The heap or B-tree. The lock is protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.|
|TABLE||The entire table, including all data and indexes.|
|FILE||The database file.|
|APPLICATION||The application-specified resource.|
|ALLOCATION_UNIT||The allocation unit.|
|DATABASE||The entire database.|
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.
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.
- Korotkevitch, D., 2016. Pro SQL Server Internals. Florida: Dmitri Korotkevitch
- Lock Scenarios Using Sys.dm_tran_locks
- Transaction Locking and Row Versioning Guide
- Deploying a Certificate for Encrypted Connection SQL Server - September 16, 2020
- How to Configure Database Mail in SQL Server - September 14, 2020
- Configuring AlwaysOn Availability Groups on SQL Server - September 3, 2020