Applies to: Azure SQL Database
This article introduces the optimized locking feature, a new SQL Server Database Engine capability that offers an improved transaction locking mechanism that reduces lock memory consumption and blocking amongst concurrent transactions.
What is optimized locking?
Optimized locking helps to reduce lock memory as very few locks are held for large transactions. In addition, optimized locking also avoids lock escalations. This allows more concurrent access to the table.
Optimized locking is composed of two primary components: Transaction ID (TID) locking and lock after qualification (LAQ).
- A transaction ID (TID) is a unique identifier of a transaction. Each row is labeled with the last TID that modified it. Instead of potentially many key or row identifier locks, a single lock on the TID is used. For more information, review the section on Transaction ID (TID) locking.
- Lock after qualification (LAQ) is an optimization that evaluates predicates of a query on the latest committed version of the row without acquiring a lock, thus improving concurrency. For more information, review the section on Lock after qualification (LAQ).
- Without optimized locking, updating 1 million rows in a table may require 1 million exclusive (X) row locks held until the end of the transaction.
- With optimized locking, updating 1 million rows in a table may require 1 million X row locks but each lock is released as soon as each row is updated, and only one TID lock will be held until the end of the transaction.
This article covers these two core concepts of optimized locking in detail.
Currently, optimized locking is available in Azure SQL Database only in the following Azure regions:
- West Europe
- UK South
- Canada Central
- Brazil South
- West Central US
Only in these regions, optimized locking is on by default in both new and existing databases.
Is optimized locking enabled?
Optimized locking is enabled per user database. Connect to your database, then use the following query to check if optimized locking is enabled on your database:
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');
If you are not connected to the database specified in
DATABASEPROPERTYEX, the result will be
NULL. You should receive
0 (optimized locking is disabled) or
Optimized locking builds on other database features:
- Optimized locking requires accelerated database recovery (ADR) to be enabled on the database.
- For the most benefit from optimized locking, read committed snapshot isolation (RCSI) should be enabled for the database.
Both ADR and RCSI are enabled by default in Azure SQL Database. To verify that these options are enabled for your current database, use the following T-SQL query:
SELECT name , is_read_committed_snapshot_on , is_accelerated_database_recovery_on FROM sys.databases WHERE name = db_name();
This is a short summary of the behavior when optimized locking is not enabled. For more information, review the Transaction locking and row versioning guide.
In the Database Engine, locking is a mechanism that prevents multiple transactions from updating the same data simultaneously, in order to protect data integrity and consistency.
When a transaction needs to modify data, it can request a lock on the data. The lock is granted if no other conflicting locks are held on the data, and the transaction can proceed with the modification. If another conflicting lock is held on the data, the transaction must wait for the lock to be released before it can proceed.
When multiple transactions are allowed to access the same data concurrently, the Database Engine must resolve potentially complex conflicts with concurrent reads and writes. Locking is one of the mechanisms by which the database engine can provide the semantics for the ANSI SQL transaction isolation levels. Although locking in databases is essential, reduced concurrency, deadlocks, complexity, and lock overhead can impact performance and scalability.
Optimized locking and transaction ID (TID) locking
Every row in the Database Engine internally contains a transaction ID (TID) when row versioning is in use. This TID is persisted on disk. Every transaction modifying a row will stamp that row with its TID.
With TID locking, instead of taking the lock on the key of the row, a lock is taken on the TID of the row. The modifying transaction will hold an X lock on its TID. Other transactions will acquire an S lock on the TID to check if the first transaction is still active. With TID locking, page and row locks continue to be taken for updates, but each page and row lock is released as soon as each row is updated. The only lock held until end of transaction is the X lock on the TID resource, replacing page and row (key) locks as demonstrated in the next demo. (Other standard database and object locks are not affected by optimized locking.)
Optimized locking helps to reduce lock memory as very few locks are held for large transactions. In addition, optimized locking also avoids lock escalations. This allows other concurrent transactions to access the table.
Consider the following T-SQL sample scenario that looks for locks on the user's current session:
CREATE TABLE t0 (a int PRIMARY KEY not null ,b int null); INSERT INTO t0 VALUES (1,10),(2,20),(3,30); GO BEGIN TRAN UPDATE t0 SET b=b+10; SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_type in ('PAGE','RID','KEY','XACT'); COMMIT TRAN GO DROP TABLE IF EXISTS t0;
The same query without the benefit of optimized locking creates four locks:
The sys.dm_tran_locks dynamic management view (DMV) can be useful in examining or troubleshooting locking issues, including observing optimized locking in action.
Optimized locking and lock after qualification (LAQ)
Building on the TID infrastructure, optimized locking changes how query predicates secure locks.
Without optimized locking, predicates from queries are checked row by row in a scan by first taking an update (U) row lock. If the predicate is satisfied, an X row lock is taken before updating the row.
With optimized locking, and when the read committed snapshot isolation level (RCSI) is enabled, predicates are applied on latest committed version without taking any row locks. If the predicate does not satisfy, the query moves to the next row in the scan. If the predicate is satisfied, an X row lock is taken to actually update the row. The X row lock is released as soon as the row update is complete, before the end of the transaction.
Since predicate evaluation is performed without acquiring any locks, concurrent queries modifying different rows will not block each other.
CREATE TABLE t1 (a int not null ,b int null); INSERT INTO t1 VALUES (1,10),(2,20),(3,30); GO
|Session 1||Session 2|
Note that the behavior of blocking changes with optimized locking in the previous example. Without optimized locking, Session 2 will be blocked.
However, with optimized locking, Session 2 will not be blocked as the latest committed version of row 1 contains a=1, which does not satisfy the predicate of Session 2.
If the predicate is satisfied, we wait for any active transaction on the row to finish. If we had to wait for the S TID lock, the row might have changed, and the latest committed version might have changed. In that case, instead of aborting the transaction due to an update conflict, the Database Engine will retry the predicate evaluation on the same row. If the predicate qualifies upon retry, the row will be updated.
Consider the following example when a predicate change is automatically retried:
CREATE TABLE t2 (a int not null ,b int null); INSERT INTO t2 VALUES (1,10),(2,20),(3,30); GO
|Session 1||Session 2|
Best practices with optimized locking
Enable read committed snapshot isolation (RCSI)
To maximize the benefits of optimized locking, it is recommended to enable read committed snapshot isolation (RCSI) on the database and use read committed isolation as the default isolation level. If not enabled, enable RCSI using the following sample:
ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;
In Azure SQL Database, RCSI is enabled by default and read committed is the default isolation level. With RCSI enabled and when using read committed isolation level, readers don't block writers and writers don't block readers. Readers read a version of the row from the snapshot taken at the start of the query. With LAQ, writers will qualify rows per the predicate based on the latest committed version of the row without acquiring U locks. With LAQ, a query will wait only if the row qualifies and there is an active write transaction on that row. Qualifying based on the latest committed version and locking only the qualified rows reduces blocking and increases concurrency.
In addition to reduced blocking, the lock memory required will be reduced. This is because readers don't take any locks, and writers take only short duration locks, instead of locks that expire at the end of the transaction. When using stricter isolation levels like repeatable read or serializable, the Database Engine is forced to hold row and page locks until the end of the transaction, for both readers and writers, resulting in increased blocking and lock memory.
Avoid locking hints
While table and query hints are honored, they reduce the benefit of optimized locking. Lock hints like UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, etc., in your queries reduce the full benefits of optimized locking. Having such lock hints in the queries forces the Database Engine to take row/page locks and hold them until the end of the transaction, to honor the intent of the lock hints. Some applications have logic where lock hints are needed, for example when reading a row with select with UPDLOCK and then updating it later. We recommend using lock hints only where needed.
With optimized locking, there are no restrictions on existing queries and queries do not need to be rewritten. Queries that are not using hints will benefit most from optimized locking.
A table hint on one table in a query will not disable optimized locking for other tables in the same query. Further, optimized locking only affects the locking behavior of tables being updated by an UPDATE statement. For example:
CREATE TABLE t3 (a int not null , b int not null); CREATE TABLE t4 (a int not null , b int not null); GO INSERT INTO t3 VALUES (1,10),(2,20),(3,30); INSERT INTO t4 VALUES (1,10),(2,20),(3,30); GO UPDATE t3 SET t3.b = t4.b FROM t3 INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;
In the previous query example, only table
t4 will be affected by the locking hint, while
t3 can still benefit from optimized locking.
UPDATE t3 SET t3.b = t4.b FROM t3 WITH (REPEATABLEREAD) INNER JOIN t4 ON t3.a = t4.a;
In the previous query example, only table
t3 will use the repeatable read isolation level, and will hold locks until the end of the transaction. Other updates to
t3 can still benefit from optimized locking. The same applies to the HOLDLOCK hint.
Frequently asked questions (FAQ)
Where is optimized locking currently available?
Currently, optimized locking is available in Azure SQL Database only in limited Azure regions.
Optimized locking is available in the following service tiers:
- all DTU service tiers
- all vCore service tiers, including provisioned and serverless
Optimized locking is not currently available in:
- Azure SQL Managed Instance
- Azure SQL Database hyperscale
- SQL Server 2022 (16.x)
Is optimized locking on by default in both new and existing databases?
Where currently supported, yes.
How can I detect if optimized locking is enabled?
See Is optimized locking enabled?
What happens when accelerated database recovery (ADR) is not enabled on my database?
If ADR is disabled, optimized locking is automatically disabled as well.
What if I want to force queries to block despite optimized locking?
For customers using RCSI, to force blocking between two queries when optimized locking is enabled, use the READCOMMITTEDLOCK query hint.
Can I disable optimized locking?
Currently, customers can create a support request to disable optimized locking.
Use the following steps to create a new support request from the Azure portal for Azure SQL Database.
First, verify that optimized locking is enabled for your database.
On the Azure portal menu, select Help + support.
In Help + support, select Create a support request.
For Issue type, select Technical.
For Subscription, Service, and Resource, select the desired SQL Database.
In Summary, type "Disable optimized locking".
For Problem Type, choose Performance and Query Execution.
For Problem Subtype, choose Blocking and deadlocks.
In Additional details, provide as much information as possible for why you would like to disable optimized locking. We are interested to review the reasons and use cases for disabling optimized locking with you.
Submit and view feedback for