Enter your search text in the box above

Select a result to preview

Exploring Optimized Locking in SQL Server 2025

#fabcon2026 #session #wednesday #sql-server #performance #locking #learning #microsoft #fabcon2026-session

Description

Updating data in SQL Server can lead to lock escalation, blocking, deadlocks, or slow performance if it isn't done correctly. The newly released optimized locking helps reduce these issues while improving concurrency. This session explores how it works—covering its architecture, implementation, and best practices to maximize performance to help reduce those dreaded locking issues!

Key Takeaways

My Notes

Action Items

Slides

📥 Download Slides

Sound off.
The mic is all yours.
Influence the product roadmap.
Join the Fabric User Panel
Join the SQL User Panel
Share your feedback directly with our
Fabric product group and researchers.
Influence our SQL roadmap and ensure
it meets your real-life needs
https://aka.ms/JoinFabricUserPanel
https://aka.ms/JoinSQLUserPanel
Exploring Optimized
Locking in SQL Server
John Morehouse
Principal Consultant
Denny Cherry & Associates Consulting
United States
Community Speaker
Blogger/Tweeter
John Morehouse
Principal Consultant
Denny Cherry & Associates
john@dcac.com
/in/johnmorehouse
@SQLRUS
Sqlrus.com
He/Him
Conference Organizer
MVP – Data Platform
Friend of Redgate
VMWare vExpert
Denny Cherry & Associates
Certified IT professionals to help achieve IT goals
Clients ranging from small business to Fortune 10
corporations
Help save on costs while improving IT reliability and solving
challenges
Slides & Demos
https://bit.ly/mypresentationfiles
Let’s Talk Objectives
Objectives
Current
problems
Lock
Refresher
Optimized
Locking
Components
How does it
work
Limitations
Q&A
Current Problems

  1. Drag & drop your photo onto
    the slide.
  2. Resize & crop so the key part
    of the photo will show up
    here. You will want the
    picture to go to ALL THE WAY
    to the top, bottom, and right
    side. On the right, position it
    so it only just barely covers
    the red line.
    IMPORTANT: Only resize photos
    using the CORNER SQUARE to
    maintain proportions (never have
    distorted photos in your slides).
    Use that to get the HEIGHT you
    need (so it touches the top and
    bottom of the slide. Use CROP
    (double click the photo and it will
    show up under “picture format”
    tab) to get the photo to the
    WIDTH you need.
    Pessimistic vs Optimistic Locking
    Pessimistic locking assumes conflicts
    are probable and explicitly locks data.
    Optimistic locking assumes conflicts
    are rare and checks for conflicts only
    when committing changes.
    What can be locked?
    Resource Lock
    Key
    Description
    Lock on a row in an index
    Object
    Lock on table, procedure, view, etc
    Page
    Lock on an 8-KB page
    RID
    Xact
    Lock on a single row in a heap
    Lock on a transaction
    Locking Refresher
    Lock
    Purpose
    IX/IU – Intent Lock Establishes a lock hierarchy,
    U – Update Lock
    Used on resources that can be updated.
    X – Exclusive Lock
    Used for data-modification operations,
    such as INSERT, UPDATE, or DELETE.
    S – Shared Lock
    Used for read operations that do not
    change or update data
    Lock Compatibility Matrix
    Existing/Request
    Lock
    IS
    S
    U
    IX
    X
    Intent shared (IS)
    Yes
    Yes
    Yes
    Yes
    No
    Shared (S)
    Yes
    Yes
    Yes
    No
    No
    Update (U)
    Yes
    Yes
    No
    No
    No
    Intent exclusive (IX)
    Yes
    No
    No
    Yes
    No
    Exclusive (X)
    No
    No
    No
    No
    No
    Traditional Locking
    ALTER DATABASE [Locking] SET READ_COMMITTED_SNAPSHOT OFF;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    CREATE TABLE t1 (a int, b int);
    INSERT INTO t1 VALUES (1,10), (2,20), (3,30);
    -- TID2: Increase b by 10
    BEGIN TRAN UPDATE t1 SET b=b+10;
    p1: Data Page for t1
    Lock Manager
    Lock Mode
    Lock Type
    Lock Resource
    r1: 1 | 10
    20 | TID1
    TID2
    IX
    OBJECT
    t1
    r2: 2 | 20
    30 | TID1
    TID2
    IU
    IX
    PAGE
    p1
    r3: 3 | 30
    40 | TID1
    TID2
    U
    X
    RID
    r1
    U
    X
    RID
    r2
    U
    X
    RID
    r3
    Slide Credit: Perry Skountrianos/Prashanth Purnananda & the Microsoft team who brought you optimized locking
    Old
    School
    Updating 1 million rows
    might require 1 million
    exclusive (X) row locks
    held until the end of the
    transaction.
    https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking?view=azuresqldb-current
    Optimized Locking Components
    Optimized Locking Components
    Accelerated Database Recovery
    Transaction ID (TID)
    Lock After Qualification (LAQ)
    ADR Overview
    Persisted Version Store (PVS) lives in user databases
    In-row versions versus off-row versions (PVS)
    Facilitates much faster rollback operations
    Eliminates long-running transaction rollbacks
    Traditional Blocking
    ALTER DATABASE [db1] SET READ_COMMITTED_SNAPSHOT ON;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    CREATE TABLE t1 (a int, b int);
    INSERT INTO t1 VALUES (1,10), (2,20), (3,30);
    -- TID2 [SESSION 1]: Increase b by 10 where a=1
    BEGIN TRAN UPDATE t1 SET b=b+10 where a=1;
    p1: Data Page for t1
    Row version store
    Row qualifies
    does not qualify
    20 | TID1
    TID2
    r1: 1 | 10
    r1: 1 | 10 | TID1
    r2: 2 | 20 | TID1
    r3: 3 | 30 | TID1
    Slide Credit: Perry Skountrianos/Prashanth Purnananda & the Microsoft team who
    brought you optimized locking
    -- TID3 [SESSION 2]: Increase b by 10 where a=2
    BEGIN TRAN UPDATE t1 SET b=b+10 where a=2;
    Lock Manager
    Lock Mode
    Lock Type
    Resource
    Owner
    Status
    IX
    OBJECT
    t1
    TID2 TID3
    TID2,
    GRANT
    IU
    IX
    PAGE
    p1
    TID2
    GRANT
    U
    X
    RID
    r1
    TID2
    GRANT
    U
    IU
    RID
    PAGE
    r3
    r2
    p1
    TID2
    TID3
    GRANT
    U
    RID
    r1
    TID3
    WAIT
    Session 2 is blocked waiting for Session 1 to commitMVP Global Summit 2023
    Optimized Locking
    ALTER DATABASE [db1] SET READ_COMMITTED_SNAPSHOT ON;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    CREATE TABLE t1 (a int, b int);
    INSERT INTO t1 VALUES (1,10), (2,20), (3,30);
    -- TID2 [SESSION 1]: Increase b by 10 where a=1
    BEGIN TRAN UPDATE t1 SET b=b+10 where a=1;
    -- TID3 [SESSION 2]: Increase b by 10 where a=2
    BEGIN TRAN UPDATE t1 SET b=b+10 where a=2;
    Row version store
    Lock Manager
    Row does not qualify
    Lock Mode
    Lock Type
    Resource
    Owner
    Status
    r1: 1 | 20
    10 | TID2
    TID1
    r1:
    r1: 11 || 10
    10 || TID1
    TID1
    X
    XACT
    TID2
    TID2
    GRANT
    r2: 2 | 30
    20 | TID3
    TID1
    r2: 2 | 20 | TID1
    IX
    OBJECT
    T1
    t1
    TID2 TID3
    TID2,
    GRANT
    X
    IX
    XACT
    PAGE
    TID3
    p1
    TID3
    TID2
    GRANT
    IX
    X
    PAGE
    RID
    p1
    r1
    TID3
    TID2
    GRANT
    X
    RID
    r2
    TID3
    GRANT
    p1: Data Page for t1
    Row qualifies
    does not qualify
    r3: 3 | 30 | TID1
    Slide Credit: Perry Skountrianos/Prashanth Purnananda & the Microsoft team who
    brought you optimized locking
    Session 2 is not blocked by Session 1
    Updating 1 million rows might
    require 1 million X row locks but
    each lock is released as soon as
    each row is updated,
    New
    School
    Only one TID lock will be held
    until the end of the transaction.
    https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking?view=azuresqldb-current
    Warning
    Even without LAQ, applications should
    not assume that SQL Server (under
    versioning isolation levels) will
    guarantee strict ordering, without using
    locking hints.
    https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking?view=azuresqldb-current
    Best Practices &
    Troubleshooting
    Locking hints will be honored but reduce the effectiveness of the optimized locking
    Avoid locking hints
    Make sure RCSI is enabled
    New entries for Deadlock Graphs
    New Waits introduced:
    ONCE
    AGAIN FOR
    THOSE IN
    THE BACK,
    NO LOCK IS
    NOT THE
    ANSWER!!!
    Limitations of Optimized Locking
    Required
    Accelerated Database Recovery
    SQL Managed Instance 2025 Policy ONLY
    Repeatable Read & Serializable Isolation
    forces the lock to be held on the row or
    page until the end of the transaction
    Resources
    Optimized Locking - https://learn.microsoft.com/enus/sql/relational-databases/sql-server-transaction-lockingand-row-versioning-guide?view=sql-serverver16&source=recommendations
    Article - https://www.red-gate.com/simpletalk/databases/sql-server/database-administration-sqlserver/optimized-locking-in-azure-sql-database/ (Simple Talk