When Page Lock happen under Read Committed Isolation level in SQL Server
2
votes
1
answer
193
views
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?

Asked by Jerry.Chen
(23 rep)
Jul 22, 2024, 09:05 AM
Last activity: Aug 4, 2024, 10:14 AM
Last activity: Aug 4, 2024, 10:14 AM