Does UPDATE WITH (ROWLOCK) guarantee row-level locking, or can SQL Server still acquire page/table locks and perform lock escalation?

Vinith Ankam 0 Reputation points

Hi Team,

I would like clarification on SQL Server locking behavior for an UPDATE statement when the ROWLOCK table hint is explicitly specified.

Example:

From Microsoft documentation, I understand that:

  • ROWLOCK is a table hint that requests row-level locking.
  • UPDATE operations can use row, page, or table locking strategies.
  • Lock escalation can occur under certain conditions.

However, I would like confirmation on the following points:

  1. When WITH (ROWLOCK) is specified on an UPDATE statement, does SQL Server always acquire only row-level locks?
  2. Can SQL Server still acquire page locks even when ROWLOCK is specified?
  3. Can SQL Server escalate row locks to an OBJECT/TABLE lock during the update operation?
  4. Is there any documented scenario where the Query Optimizer or Storage Engine may ignore the ROWLOCK preference and choose a higher lock granularity?
  5. Are there differences in this behavior between SQL Server versions (2019, 2022, and Azure SQL Database), especially with optimized locking enabled?
  6. If an UPDATE statement affects approximately 25,000 rows and ROWLOCK is specified, how does SQL Server count and manage the locks internally?
  • Should we expect roughly one lock per row (e.g., ~25,000 row/key locks)?
  • Is there a documented threshold at which SQL Server considers lock escalation?
    • Would the presence of multiple indexes increase the total number of locks acquired?

I reviewed the following documentation but could not find an explicit example for UPDATE operations that clearly states whether ROWLOCK guarantees row-level locking throughout the transaction:

  • Transaction Locking and Row Versioning Guide
  • Table Hints (Transact-SQL)
  • Lock Escalation documentation

Could you please confirm the expected behavior and provide any relevant Microsoft references?

0 comments No comments
Sign in to comment

2 answers

  1. Deepesh Dhake 165 Reputation points
    1. Does WITH (ROWLOCK) always acquire only row-level locks?

    No. Per Microsoft, if you use a lock hint such as ROWLOCK, this only alters the initial lock plan; lock hints don't prevent lock escalation. It influences the starting granularity, not the transaction's entire lifetime.

    1. Can SQL Server still acquire page locks despite ROWLOCK?

    Yes. The hint requests row granularity as the starting point but does not prevent the engine from taking intent locks on pages/object, nor from escalating. ROWLOCK only suppresses page locks as the initial choice.

    1. Can row locks escalate to OBJECT/TABLE during the update?

    Yes. ROWLOCK does not disable escalation. A large UPDATE accumulating enough locks on a single object will trigger an escalation attempt to TABLE regardless of the hint.

    1. Documented scenarios where the requested granularity is overridden?

    Yes. escalation triggers via two independent paths. The lock-count threshold, and memory pressure: a memory threshold of 40 percent of lock memory, which equates to roughly 24 percent of the buffer pool, can trigger escalation. Also ALTER TABLE ... SET (LOCK_ESCALATION = ...), lock-memory exhaustion (1204 errors), and non-SARGable predicates forcing large scans.

    1. Version differences - 2019, 2022, Azure SQL, and optimized locking:

    Per Microsoft Learn, it applies to SQL Server 2025 (17.x), Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric.

    Optimized locking avoids lock escalations and holds very few locks even for large transactions. So under optimized locking the ROWLOCK hint becomes largely moot for escalation concerns - the row/page locks are released almost immediately rather than accumulating toward a threshold. Two enablement caveats: ADR is absolutely necessary to get optimized locking working, and RCSI enables the lock-after-qualification component. Also note: if RCSI is enabled, use the READCOMMITTEDLOCK table hint to force blocking between two queries when optimized locking is enabled.

    Under optimized locking (Azure SQL, or 2025 with it enabled): The 25,000-row UPDATE might still acquire 25,000 X row locks, but each is released as soon as its row is updated, and only one X TID lock is held until the end of the transaction. Escalation is largely avoided. The classic "batch under 5,000 rows" workaround becomes unnecessary in this mode.

    To actually guarantee row-level locking / prevent escalation under traditional locking, the hint is insufficient. Use ALTER TABLE dbo.YourTable SET (LOCK_ESCALATION = DISABLE), or batch the UPDATE into chunks below the threshold.

    1. Erland Sommarskog 134.7K Reputation points • MVP • Volunteer Moderator

      A clarification here: Deepesh is correct in that the ROWLOCK hint does not prevent lock escalation. However, you are unlikely to see locks on page level (save for intent locks which are always on table and page) when you use the ROWLOCK hint. This is because lock escalation is never to page level, but always to table level. Well, if the table has LOCK_ESCALATION set to AUTO, escalation can also be to partition level.

    2. Akhil Gajavelly 1,830 Reputation points • Microsoft External Staff • Moderator

      Hi @Vinith Ankam ,

      Just following up to see whether the explanations provided by Q&A Assist and the community members helped address your question regarding ROWLOCK, lock escalation, and locking granularity.

      If you have any remaining questions or would like clarification on a specific scenario, please let us know and we'll be happy to assist further.

      Thanks,
      Akhil.

    3. Akhil Gajavelly 1,830 Reputation points • Microsoft External Staff • Moderator

      Hi @Vinith Ankam ,

      Just following up once more on this question.

      Based on the information provided by Q&A Assist and the community responses, ROWLOCK is a preference rather than a guarantee. SQL Server may still acquire page or table locks when appropriate, and lock escalation can still occur if the engine determines it is beneficial.

      If you have any specific test results, execution details, or remaining concerns about a particular workload, feel free to share them. Otherwise, if your question has been answered, please let us know so we can close the discussion.

      Thanks,
      Akhil.

    Sign in to comment

  2. AI answer

    ROWLOCK is a request for row-level locks, not a guarantee. The behavior is constrained but not absolute.

    1. Does WITH (ROWLOCK) force only row locks?

    No. ROWLOCK specifies that row-level locks are to be taken where possible, but it does not guarantee that only row locks will be used for the entire operation. Locking is still subject to the normal locking hierarchy and engine decisions.

    Relevant points from the documentation:

    • Data modification statements (UPDATE, INSERT, DELETE) typically request both shared and exclusive locks and can use row, page, or table locks depending on the access pattern and optimizer decisions.
    • Locking always follows the lock hierarchy (row → page → HoBT → table), and intent locks are used at higher levels to protect lower-level locks.
    1. Can SQL Server still acquire page locks with ROWLOCK?

    Yes. ROWLOCK is a hint that row-level locks are to be taken, but it does not explicitly forbid page locks. Page locks can still be taken in cases such as:

    • Internal operations where page-level metadata or structures must be protected.
    • Situations where the engine decides a different granularity is required to maintain consistency or protect internal structures.

    To disallow page locks on an index, the documented mechanism is to set index/table options (not ROWLOCK), for example via CREATE INDEX / ALTER INDEX or CREATE TABLE / ALTER TABLE options that disallow page-level locks. The documentation explicitly states that locking granularity can be configured so that:

    • If page-level locks are disallowed, the index is accessed by row-level and table-level locks.
    1. Can SQL Server escalate to table (OBJECT/TABLE) locks?

    Yes. Lock escalation is still possible. The locking guide explicitly notes that:

    • Lock granularity can be affected by LOCK_ESCALATION on the table.
    • When page and row locks are both disallowed, table-level locks are used.

    Even when row-level locking is requested, the engine can escalate to table-level locks when the number of row/page locks becomes large or when required by LOCK_ESCALATION settings. ROWLOCK does not disable escalation.

    1. Can the engine ignore ROWLOCK and choose higher granularity?

    Yes. Table and query hints are honored “as possible,” but the engine can still choose different locking behavior when necessary. The documentation on customizing locking and row versioning makes clear that:

    • Locking granularity is ultimately controlled by index/table options and the lock escalation mechanism.
    • Page-level, row-level, or both can be disallowed per index; otherwise, the engine can choose among them.

    The table-hints documentation states that hints like TABLOCKX, UPDLOCK, XLOCK control how locks are taken and held, but they do not override all internal decisions. For example, UPDLOCK is explicitly described as taking update locks at row or page level, and if combined with TABLOCK or if a table-level lock is taken for some other reason, an exclusive table lock is taken instead. This shows that hints can be superseded by higher-level locking when the engine deems it necessary.

    1. Differences in behavior with optimized locking (SQL Server 2019/2022/Azure SQL Database)

    With optimized locking and RCSI enabled, the guidance is:

    • Optimized locking focuses on reducing blocking by qualifying rows using row versions and acquiring locks only on qualifying rows (“lock after qualification”).
    • Table and query hints such as UPDLOCK, XLOCK, etc. are still honored but reduce the benefit of optimized locking because they force the engine to take and hold locks longer.
    • The documentation explicitly recommends avoiding locking hints where possible under optimized locking.

    Key implications:

    • Optimized locking changes when and how long locks are held, not the fundamental fact that lock escalation and higher-granularity locks can still occur.
    • There is no statement that ROWLOCK becomes a strict guarantee under any version or under optimized locking.

    Summary per question:

    1. WITH (ROWLOCK) does not guarantee only row-level locks; it requests row locks where possible.
    2. Page locks can still be acquired in some scenarios even with ROWLOCK.
    3. Row locks can still be escalated to table (OBJECT/TABLE) locks; ROWLOCK does not disable escalation.
    4. The engine may effectively override the preference when required by lock escalation, index/table options, or internal needs; hints are honored but not absolute.
    5. Across SQL Server 2019, 2022, and Azure SQL Database (with optimized locking), the core behavior remains: hints are honored but can reduce optimized locking benefits and do not prevent escalation or higher-granularity locks.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer