Illustration of lock acquisition sequence under Optimized Locking
Optimized locking is a Database Engine feature introduced in 2023 that drastically reduces lock memory, and the number of locks required for concurrent writes. This is enabled by default for Azure SQL Database.
Although the document (Optimized Locking - SQL Server | Microsoft Learn) introduces and explains the idea of optimized locking clearly, it might still be a bit vague and hard to visualize.
To better understand how optimized locking works, I conducted a few labs and collected Extended Events to observe the lock acquisition sequence.
Here, I have included two demonstrations of the lock acquisition sequence to give you a glimpse of how Optimized Locking works. However, I strongly recommend reading the public document (Optimized Locking - SQL Server | Microsoft Learn) to gain a basic understanding of Optimized Locking before going through the demonstrations.
Demonstration #1
Create table and populate data:
CREATE TABLE t2
(
a INT NOT NULL,
b INT NULL
);
INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);
GO
Create the first session that run the update query
--session 1
BEGIN TRANSACTION foo;
UPDATE t2
SET b = b + 10
WHERE a = 1;
From the xevent logs, you will see the UPDATE query acquires 4 locks
- IX lock on OBJECT (table)
- X lock on PAGE
- X lock on RID of the row to be modified
- X lock on TID (1088080)
Through the DMV, you will see that it only holds the XACT lock (the other three locks are released once the row has been updated, even though it has not been committed).
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_type IN ('PAGE','RID','KEY','XACT');
If now the second session would like to update the same row where a = 1
--session 2:
BEGIN TRANSACTION bar;
UPDATE t2 -- WITH (ROWLOCK)
SET b = b + 20
WHERE a = 1;
You will see that it's waiting for 'XACT_WITH_RESOURCE_TO_MODIFY' because it's trying to place an S lock on session 1's TID but has to wait because session 1 owns the X lock on its TID.
Once the transaction in session 1 has committed, you can see:
- The transaction in session 2 can place the S lock on the session 1's TID (1088080)
- The transaction in session 2 stamps the to-be-modified row with its TID (1088086) and further places the X lock on it.
Then the transaction in session 2 has committed the transaction (release X lock on TID), and session 1 starts a new transaction to update the same row. (a=1)
--session 1
BEGIN TRANSACTION foo;
UPDATE t2
SET b = b + 10
WHERE a = 1;
From the xevent log, you can tell that it does not need to place an S lock on session 2's previous transaction's TID (1088086); instead, it places an X lock on its new TID (1088087)."
The above lock acquisition sequence is illustrated below:
=====================================================================================================
Demonstration #2
Use the same table as demo #1.
Create the first session that run the update query
--session 1
BEGIN TRANSACTION foo;
UPDATE t2
SET b = b + 10
WHERE a = 1;
From the xevent logs, you will see the UPDATE query in session 1 acquires 4 locks
- IX lock on OBJECT (table)
- X lock on PAGE
- X lock on RID of the row to be modified. (51960, 1)
- X lock on TID (1088163)
Create a second session that runs the UPDATE query on another row; you will see that it won't be blocked with Optimized Locking.
--session 2:
BEGIN TRANSACTION bar;
UPDATE t2
SET b = b + 10
WHERE a = 2;
From the xevent logs, you will see the UPDATE query in session 2 acquires 4 locks
- IX lock on OBJECT (table)
- X lock on PAGE
- X lock on RID of the row to be modified. (it's a different row than a=1, you can find the resource id different: 51960, 65537)
- X lock on TID (1088164)
If now session 2 would like to update the same row where a = 1 as session 1.
--session 2:
UPDATE t2
SET b = b + 20
WHERE a = 1;
You will see that it's waiting for 'XACT_WITH_RESOURCE_TO_MODIFY' because it's trying to place an S lock on session 1's TID (1088163) but has to wait since session 1 owns the X lock on it.
Once the transaction in session 1 commits, the transaction in session 2 can place S lock on TID (1088163)
The transaction in session 2 stamps the to-be-modified row with its TID (1088084) but no need to further places the X lock on it because it already has X lock on its current TID (1088164) and hasn’t released yet (transaction uncommitted).
The above lock acquisition sequence is illustrated below:
(End)
Updated Dec 18, 2024
Version 1.0Mattcc
Microsoft
Joined October 05, 2022
Azure Database Support Blog
Follow this blog board to get notified when there's new activity