Share via

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
2026-06-11T15:39:08.5766667+00:00

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?

SQL Server | SQL Server Transact-SQL
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deepesh Dhake 85 Reputation points
    2026-06-11T19:51:45.5833333+00:00
    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.

    Was this answer helpful?

  2. AI answer

    2026-06-11T15:39:34.7433333+00:00

    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.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.