When Page Lock happen under Read Committed Isolation level in SQL Server

Question

I have a clustered index table in Azure SQL Database. After checking the dm_db_index_operational_stats, the column page_lock_wait_count greater than 0 means there are some Page Lock happended for this before, but no row_lock_wait_count recorded like below pic.

I try to simulate this scenario in SQL Server 2019. Set the transaction level to Read Committed Snapshot in my test db environment, same as Azure SQL. But I can only get below lock situations.

1. Begin transaction A, to update few rows in table T. Begin transaction B to update similar rows in table. The waiting lock resource type of transaction B is KEY in dm_tran_locks.
2. Begin transaction A, to update a plenty of rows in table T. Begin transaction B to also update large amount of rows in table. The wait resource type is OBJECT for transaction B, since the transaction A is hold the X mode of OBJECT, I guess this is because the lock has been escalated. I tried above scenarios many times in my testing environment. Since transaction A will cause Page IX lock, so the row_lock_count/row_lock_wait_count/page_lock_count will increase, but no page_lock_wait_count occurred.

Question:

1. Why page_lock_wait_count can be greater than 0 when row_lock_wait_count=0?
2. What conditions or scenarios will cause page lock wait?

Answer 1

1. Why page_lock_wait_count can be greater than 0 when row_lock_wait_count=0?

They're measuring two different, and often independent, things.

A process requesting a row lock and finding it has to wait will increment row_lock_wait_count.
A process requesting a page lock and finding it has to wait will increment page_lock_wait_count.

Each data access begins with the storage engine making a decision about what locking granularity to use. It might decide to start with row, page, or even object level locking.

You can influence this decision with hints like ROWLOCK, PAGLOCK, and TABLOCK. Otherwise, the engine makes its own decision based on various metadata at the time of execution. A plan recompilation is not necessary for the engine to change its mind about locking granularity between executions of the same plan.

A process accessing data under page-level locking might increment page_lock_wait_count but it will never increment row_lock_wait_count because it never tries to acquire locks at that granularity.

In addition to user activity, there are also system processes that only ever operate at page-level locking granularity, like index reorganization.

2. What conditions or scenarios will cause page lock wait?

Any time one process requests a page lock that is incompatible with another page lock already held by another process. There must be a million possibilities.

One common one (under RCSI) would be where two concurrent processes are taking update (U) locks on the same table at page granularity. Neither process can complete lock escalation successfully because the other already holds an incompatible lock on the table.

Note that lock escalation only ever escalates to the partition or object level. Locks never escalate from row granularity to page granularity regardless of isolation level, for example.