Sample Header Ad - 728x90

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. from dm_db_index_operational_stats 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