Training
Learning path
Optimize query performance in Azure SQL - Training
Optimize query performance in Azure SQL
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: Azure SQL Database SQL database in Microsoft Fabric
This article introduces the optimized locking feature, a new Database Engine capability that offers an improved transaction locking mechanism that reduces lock memory consumption and blocking for concurrent transactions.
Optimized locking helps to reduce lock memory as very few locks are held even 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).
For example:
X
) row locks held until the end of the transaction.X
row locks but each lock is released as soon as each row is updated, and only one TID lock is held until the end of the transaction. Because locks are released quickly, lock memory usage is reduced and lock escalation is much less likely to occur, improving workload concurrency.Note
Enabling optimized locking reduces or eliminates row and page locks acquired by the Data Modification Language (DML) statements such as INSERT
, UPDATE
, DELETE
, MERGE
. It has no effect on other kinds of database and object locks, such as schema locks.
Optimized locking is available in Azure SQL Database and Fabric SQL database only, in all service tiers and compute sizes.
Optimized locking is not currently available in Azure SQL Managed Instance or in SQL Server.
Optimized locking is enabled per user database. Connect to your database, then use the following query to check if optimized locking is enabled:
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');
Result | Description |
---|---|
0 |
Optimized locking is disabled. |
1 |
Optimized locking is enabled. |
NULL |
Optimized locking is not available. |
Optimized locking builds on other database features:
Both ADR and RCSI are enabled by default in Azure SQL Database. To verify that these options are enabled for your current database, connect to the database and run 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 guarantee the ACID properties of transactions.
When a transaction needs to modify data, it requests 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 attempt 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 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.
When row versioning based isolation levels are in use or when ADR is enabled, every row in the database internally contains a transaction ID (TID). This TID is persisted on disk. Every transaction modifying a row stamps 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 holds an X
lock on its TID. Other transactions acquire an S
lock on the TID to wait until the first transaction completes. With TID locking, page and row locks continue to be taken for modifications, but each page and row lock is released as soon as each row is modified. The only lock held until the end of transaction is the single X
lock on the TID resource, replacing multiple page and row (key) locks.
Consider the following example that shows locks for the current session while a write transaction is active:
/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');
CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);
INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO
BEGIN TRANSACTION;
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 TRANSACTION;
GO
DROP TABLE IF EXISTS t0;
If optimized locking is enabled, the request holds only a single X
lock on the XACT
(transaction) resource.
If optimized locking is not enabled, the same request holds four locks - three X
key locks on each row and one IX
(intent exclusive) lock on the page containing the rows:
The sys.dm_tran_locks dynamic management view (DMV) is useful in examining or troubleshooting locking issues, such as observing optimized locking in action.
Building on the TID infrastructure, optimized locking changes how DML statements such as INSERT
, UPDATE
, DELETE
, and MERGE
acquire locks.
Without optimized locking, query predicates are checked row by row in a scan by first taking an update (U
) row lock. If the predicate is satisfied, an exclusive (X
) row lock is taken before updating the row and held until the end of transaction.
With optimized locking, and when the READ COMMITTED
snapshot isolation level (RCSI) is enabled, predicates are checked on latest committed version of the row without taking any 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 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 do not block each other.
For example:
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 |
---|---|
BEGIN TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 1; |
|
BEGIN TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 2; |
|
COMMIT TRANSACTION; |
|
COMMIT TRANSACTION; |
Without optimized locking, session 2 is blocked because session 1 holds a U
lock on the row session 2 needs to update. However, with optimized locking, session 2 is not blocked because U
locks aren't taken, and because in the latest committed version of row 1, column a
equals to 1, which does not satisfy the predicate of session 2.
Because with LAQ U
locks aren't taken, a concurrent transaction might modify the row after the predicate has been evaluated. If the predicate is satisfied and there is no other active transaction on the row (no X
TID lock), the row is modified. If there is an active transaction, the Database Engine waits for it to complete, and re-evaluates the predicate again at the time of modification because the other transaction might have modified the row. If the predicate is still satisfied, the row is modified.
Consider the following example where predicate evaluation is automatically retried because another transaction has changed the row:
CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO
Session 1 | Session 2 |
---|---|
BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1; |
|
BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1; |
|
COMMIT TRANSACTION; |
|
COMMIT TRANSACTION; |
Concurrent workloads under read committed snapshot isolation (RCSI) that rely on strict execution order of transactions might experience differences in query behavior when optimized locking is enabled.
Consider the following example where transaction T2 is updating table t4
based on column b
that was updated during transaction T1.
CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);
INSERT INTO t4
VALUES (1,1);
GO
Session 1 | Session 2 |
---|---|
BEGIN TRANSACTION T1; UPDATE t4 SET b = 2 WHERE a = 1; |
|
BEGIN TRANSACTION T2; UPDATE t4 SET b = 3 WHERE b = 2; |
|
COMMIT TRANSACTION; |
|
COMMIT TRANSACTION; |
Let's evaluate the outcome of the above scenario with and without lock after qualification (LAQ).
Without LAQ
Without LAQ, the UPDATE
statement in transaction T2 is blocked, waiting for transaction T1 to complete. Once T1 completes, T2 updates the row setting column b
to 3
because its predicate is satisfied.
After both transactions commit, table t4
contains the following rows:
a | b
1 | 3
With LAQ
With LAQ, transaction T2 uses the latest committed version of the row where column b
equals to 1
to evaluate its predicate (b = 2
). The row does not qualify; hence it is skipped and the statement completes without having been blocked by transaction T1. In this example, LAQ removes blocking but leads to different results.
After both transactions commit, table t4
contains the following rows:
a | b
1 | 2
Important
Even without LAQ, applications should not assume that the Database Engine guarantees strict ordering without using locking hints when row versioning based isolation levels are used. Our general recommendation for customers running concurrent workloads under RCSI that rely on strict execution order of transactions (as shown in the previous example) is to use stricter isolation levels such as REPEATABLE READ
and SERIALIZABLE
.
The following improvements help you monitor and troubleshoot blocking and deadlocks when optimized locking is enabled:
XACT
wait types for the S
lock on the TID, and resource descriptions in sys.dm_os_wait_stats (Transact-SQL):
LCK_M_S_XACT_READ
- Occurs when a task is waiting for a shared lock on an XACT
wait_resource
type, with an intent to read.LCK_M_S_XACT_MODIFY
- Occurs when a task is waiting for a shared lock on an XACT
wait_resource
type, with an intent to modify.LCK_M_S_XACT
- Occurs when a task is waiting for a shared lock on an XACT
wait_resource
type, where the intent cannot be inferred. This is not common.XACT
locking resources. For more information, see resource_description
in sys.dm_tran_locks (Transact-SQL).XACT
wait resources. For more information, see wait_resource
in sys.dm_exec_requests (Transact-SQL).<resource-list>
, each <xactlock>
element reports the underlying resources and specific information for locks of each member of a deadlock. For more information and an example, see Optimized locking and deadlocks.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 already enabled, enable RCSI by connecting to the master
database and executing the following statement:
ALTER DATABASE [your-database-name] 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 read a version of the row from the snapshot taken at the start of the statement. With LAQ, writers qualify rows per the predicate based on the latest committed version of the row and without acquiring U
locks. With LAQ, a query waits 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 required lock memory is reduced. This is because readers don't take any locks, and writers take only short duration locks, instead of locks that are held until the end of the transaction. When using stricter isolation levels such as REPEATABLE READ
or SERIALIZABLE
, the Database Engine holds row and page locks until the end of the transaction even with optimized locking enabled, for both readers and writers, resulting in increased blocking and lock memory usage.
While table and query hints such as UPDLOCK
, READCOMMITTEDLOCK
, XLOCK
, HOLDLOCK
, etc. are honored when optimized locking is enabled, they reduce the benefit of optimized locking. Lock hints force the Database Engine to take row or 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 the UPDLOCK
hint 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 benefit from optimized locking most.
A table hint on one table in a query does not disable optimized locking for other tables in the same query. Further, optimized locking only affects the locking behavior of tables being updated by a DML statement such as INSERT
, UPDATE
, DELETE
, or MERGE
. For example:
CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);
CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO
INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO
UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;
In the previous query example, only table t6
is affected by the locking hint, while t5
can still benefit from optimized locking.
UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;
In the previous query example, only table t5
uses the REPEATABLE READ
isolation level and hold locks until the end of the transaction. Other updates to t5
can still benefit from optimized locking. The same applies to the HOLDLOCK
hint.
In Azure SQL Database, yes.
See Is optimized locking enabled?.
If ADR is disabled, optimized locking is automatically disabled as well.
For customers using RCSI, to force blocking between two queries when optimized locking is enabled, use the READCOMMITTEDLOCK
query hint.
No, because DML statements cannot run on read-only replicas, and the corresponding row and page locks are not taken.
Not at this time.
Training
Learning path
Optimize query performance in Azure SQL - Training
Optimize query performance in Azure SQL