Background and context
In today’s application landscape, where scalability and concurrency are essential requirements, delivering high performance while maintaining data consistency is a complex challenge. SQL Server 2025 introduces several enhancements to the Engine to improve performance and scalability. In this article, after an initial overview of the new features introduced in the database Engine, we will focus on Optimized Locking, the feature with a magical name that changes how DML statements acquire locks. Initially introduced in Azure SQL, it is now available in SQL Server 2025.
Overview of Engine enhancements
The 2025 version of SQL Server reflects significant investments in the Engine. The picture below highlights the areas of innovation where these efforts have been focused.
Key improvements aim to increase transaction concurrency, enhance performance, and boost service availability in terms of high availability (HA) and disaster recovery (DR).
New tempdb optimization capabilities make it possible to:
- Configure Resource Governor to set a limit on the total amount of space used by an application or workload in tempdb. When a query attempts to exceed this limit, Resource Governor stops its execution. The application will receive a specific error indicating that the resource limit for the workload has been exceeded. The goal is to prevent disruptions by blocking uncontrolled workloads from consuming excessive tempdb space
- Enable Accelerated Database Recovery (ADR) on the tempdb system database. Transactions involving temporary tables, table variables, or non-temporary tables created in tempdb can be negatively impacted by rollbacks and excessive transaction log usage. Enabling ADR in tempdb allows for instant transaction log truncation, even for transactions affecting tempdb objects
- Enable tempdb on tmpfs for SQL Server on Linux
SQL Server 2025 reinforces the feature known as Persisted Statistics for Readable Secondaries, which was first introduced in SQL Server 2022 but was disabled by default and required trace flag 12606 to be enabled. In the latest version of SQL Server, Query Store for readable secondaries is enabled by default.
Change Tracking has also been improved in SQL Server 2025. The automatic cleanup process has been enhanced with a new approach called adaptive shallow cleanup. This new method removes data that has reached its retention period in incremental steps, continuing until all expired data is deleted. In the latest version of SQL Server, adaptive shallow cleanup is enabled by default.
These are just some of the innovations introduced in the SQL Server 2025 database Engine. In fact, across the three major investment areas: Security, Performance, and HADR, there are over 40 new features!
In the next section, I will dive into the details of Optimized Locking, the feature with a magical name that changes how DML statements acquire locks.
Optimized Locking
Optimized Locking is a new feature in the Engine of SQL Server 2025. It is designed to reduce the memory used for lock management, minimize the occurrence of lock escalation, and increase workload concurrency. Before diving into how Optimized Locking works, it’s important to take a look at concurrency models and locking mechanisms.
Concurrency models and lock modes
Locking is a mechanism that prevents multiple transactions from modifying the same data simultaneously, thereby ensuring compliance with the ACID properties of transactions.
When a transaction needs to modify data, the Engine requests a lock on the target data. This lock is granted only if there are no incompatible locks already held on the same data set, allowing the transaction to proceed with the update. If another lock is already present, the transaction must wait for it to be released before continuing. Locking is one of the mechanisms the SQL Engine uses to enforce transaction isolation according to the selected isolation level. SQL Server database Engine supports two concurrency models:
- Pessimistic concurrency model
- Optimistic concurrency model
In the pessimistic model, locks acquired by read transactions can block transactions that attempt to acquire write locks, and vice versa. In the optimistic model, locks acquired by read transactions do not block write locks; however, write locks are still incompatible with each other.
Each transaction specifies an isolation level that determines how isolated it is from other transactions.
For the pessimistic concurrency model, there are four isolation levels:
- Read Uncommitted
- Read Committed
- Repeatable Reads
- Serializable
For the optimistic concurrency model, there are two isolation levels:
- Snapshot
- Read Committed Snapshot
Not everyone knows that Read Committed Snapshot is not a distinct isolation level, but rather a behavioral change to the Read Committed isolation level when the READ_COMMITTED_SNAPSHOT option is enabled.
Depending on the chosen isolation level, the SQL Engine locks resources using different lock modes, which determine how those resources can (or cannot) be accessed by concurrent transactions.
Technologies behind Optimized Locking
Optimized Locking depends on two technologies that have long been part of the SQL Server Engine.
The first one is Accelerated Database Recovery (ADR), and it is a required prerequisite for enabling Optimized Locking. Accelerated Database Recovery must be enabled at the database level and improves transaction recovery by speeding up rollback operations and handling uncommitted changes more efficiently. It is not enabled by default on on-premises SQL Server databases.
The second technology is the Read Committed Snapshot Isolation (RCSI). While not a strict requirement, enabling RCSI allows full benefit from Optimized Locking.
The following T-SQL snippet checks the current status of Accelerated Database Recovery, Read Committed Snapshot, and Optimized Locking for the current database.
-- Optimized locking builds on other database features:
-- 1. Accelerated database recovery (ADR)
-- 2. Read committed snapshot isolation level (RCSI) for the most benefit
SELECT
IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn')
,RCSI = is_read_committed_snapshot_on
,ADR = is_accelerated_database_recovery_on
FROM
sys.databases
WHERE
(name = DB_NAME());
Optimized Locking is based on two key mechanisms:
- Transaction ID (TID) locking
- Lock After Qualification (LAQ)
Transaction ID (TID) locking
When using the Read Committed Snapshot isolation level, or when Accelerated Database Recovery is enabled, each row in the database internally contains a Transaction ID (TID). When a transaction modifies a row, it assigns its own TID to that row.
With TID locking, instead of acquiring a lock on the row key, the lock is acquired on the row’s TID. The transaction modifying the row holds an exclusive (X) lock on its own TID. Other transactions that need to modify the same row acquire a shared (S) lock on that same TID to wait until the first transaction is completed.
With TID locking, page and row locks are still acquired during modifications, but each page and row lock is released immediately after the change, even if the transaction has not yet been committed or rolled back. The only lock held until the end of the transaction is the single exclusive (X) lock on the TID resource, effectively replacing multiple page and row (key) locks. Let’s consider the following T-SQL code snippet, executed on a SQL Server 2025 database where Optimized Locking is enabled. The sample table dbo.SensorReadings is created with two columns: the SensorID column of type Integer, which is the primary key, and the ReadingValue column of type Integer, used to store some sample values.
DROP TABLE IF EXISTS dbo.SensorReadings;
CREATE TABLE dbo.SensorReadings
(
SensorID INTEGER PRIMARY KEY NOT NULL,
ReadingValue INTEGER NOT NULL
);
INSERT INTO dbo.SensorReadings VALUES (1, 10), (2, 20), (3, 30);
GO
Let’s perform an update on the values stored in ReadingValue, while monitoring the acquired locks using the DMV sys.dm_tran_locks.
-- Inspect locks with sys.dm_tran_locks on updated rows
BEGIN TRANSACTION;
UPDATE
dbo.SensorReadings
SET
ReadingValue = ReadingValue + 10;
SELECT
*
FROM
sys.dm_tran_locks
WHERE
request_session_id = @@SPID
AND
resource_type IN ('PAGE', 'RID', 'KEY', 'XACT');
COMMIT TRANSACTION;
GO
The only lock observed is the one acquired on the TID, as shown in the picture below.
The same operation, executed on the same table but in a database with Optimized Locking disabled, shows exclusive (X) locks on the rows and an intent exclusive (IX) lock on the page containing those rows. These locks will remain in place until the transaction is completed, as shown in the picture below.
Lock After Qualification (LAQ)
One of the main causes of slowdowns in DML operations is the acquisition of locks during the search for rows that satisfy a query’s conditions (row qualification). Lock After Qualification in SQL Server 2025 changes how DML statements (UPDATE, DELETE, MERGE) acquire locks.
When Optimized Locking is not enabled, query predicates are evaluated row-by-row during a scan. An update (U) lock is first acquired on each row, which is then converted into an exclusive (X) lock immediately before updating the row, if the predicate is satisfied. The exclusive (X) lock is held until the transaction completes.
When Optimized Locking is enabled, predicates are evaluated on the most recent committed version of the row without acquiring any lock. If the predicate is not satisfied, the scan moves on to the next row. If the predicate is satisfied, an exclusive (X) lock is acquired to perform the update. This lock is released immediately after the row is updated and before the transaction ends.
Since predicate evaluation occurs without locking, concurrent queries that modify different rows do not block each other.
Let’s consider the following T-SQL code snippet, executed on a SQL Server 2025 database where Optimized Locking is enabled. The sample table dbo.EntityCounters is created with two columns: the EntityID (Integer), which is the primary key, and the CounterValue (Integer), used to store some values.
DROP TABLE IF EXISTS dbo.EntityCounters;
CREATE TABLE dbo.EntityCounters
(
EntityID INTEGER NOT NULL,
CounterValue INTEGER NULL
);
INSERT INTO dbo.EntityCounters VALUES (1, 10), (2, 20), (3, 30);
GO
Let’s consider two concurrent queries, executed in two different sessions, in the sequence outlined in the table below.
+-----------+------------------------------------------+-----------------------------------------+
| Time | Session 1 | Session 2 |
+-----------+------------------------------------------+-----------------------------------------+
| t1 | BEGIN TRANSACTION; | |
| | | |
| | UPDATE | |
| | dbo.TableB | |
| | SET | |
| | CounterValue = CounterValue + 10 | |
| | WHERE | |
| | ID = 1; | |
| | | |
| t2 | | BEGIN TRANSACTION; |
| | | |
| | | UPDATE |
| | | dbo.TableB |
| | | SET |
| | | CounterValue = CounterValue + 10 |
| | | WHERE |
| | | ID = 2; |
| | | |
| t3 | SELECT | |
| | resource_type | |
| | ,resource_database_id | |
| | ,resource_description | |
| | ,request_mode | |
| | ,request_type | |
| | ,request_status | |
| | ,request_session_id | |
| | ,resource_associated_entity_id | |
| | FROM | |
| | sys.dm_tran_locks | |
| | WHERE | |
| | request_session_id IN | |
| | (spid_sessione_1, spid_sessione_2) | |
| | AND resource_type IN | |
| | ('PAGE', 'RID', 'KEY', 'XACT'); | |
| | | |
+-----------+------------------------------------------+-----------------------------------------+
When Optimized Locking is not enabled, the query executed in session 2 is blocked because the query in session 1 has acquired an update (U) lock on the row that session 2 is attempting to update.
When an UPDATE query scans a table, it first acquires an update (U) lock on each examined row to ensure that no other transaction can modify it concurrently. If the predicate in the WHERE clause is satisfied, the update (U) lock is converted into an exclusive (X) lock and the row is updated. If the predicate is not satisfied, the update (U) lock is released without modifying the row.
This behavior causes blocking, because if another query (session 2) tries to update the same row, it must wait for the first query (session 1) to release the update (U) lock, even if the first query ultimately does not modify the row affected by the update lock.
The following picture shows the locks acquired at time t3 when Optimized Locking is not enabled.
When Optimized Locking is enabled, the query in session 2 is not blocked because update (U) locks are not acquired. Additionally, in the most recent committed version of row 1, the EntityID column equals 1, which does not satisfy the predicate of the query in session 2. The predicate evaluation occurs on the most recent committed version of the row.
The figure below illustrates the locks acquired at time t3 when Optimized Locking is enabled.
Summary
Optimized Locking in SQL Server 2025 and Azure SQL Database represents a significant advancement in concurrency management. Thanks to Transaction ID (TID) locking and Lock After Qualification (LAQ), Optimized Locking reduces the memory consumption needed for lock management and minimizes the situation known as lock escalation by reducing locks between concurrent transactions.
