Page level locking in SQL Server explained
Solved Email & Outlook
RK
Rachel Kim
October 22, 2019
2 replies
7,530 views
Reviewed by moderators

Deadlock graphs from our busiest table keep showing PAGE locks where I expected row locks, and a colleague suggests disabling page locking on the index as the fix.

How does SQL Server decide between row and page locks, and is disabling page locks a real fix or a trap?

Accepted Answer
Verified by David Taylor, Database Expert ยท Reviewed October 2019

Both, is the annoying honest answer, real fix in a narrow case and trap everywhere else. The deciding machinery first:

SQL Server picks lock granularity per operation by cost: row locks are precise but each costs memory and management, so scans touching many rows on a page get page locks instead and past roughly 5000 locks in one statement, escalation trades them all for a table lock. Your deadlock graphs showing PAGE means the colliding operations were scanning ranges rather than seeking single rows, which is itself the diagnostic gift: the first question is why those queries scan, since a missing or unsuitable index turning seeks into scans creates the page lock collisions as a symptom.

The narrow case where disabling earns its keep: a genuine insert hotspot, sessions colliding on the same pages of an ever ascending index during heavy concurrent inserts, where ALTER INDEX yours ON table SET (ALLOW_PAGE_LOCKS = OFF) forces row granularity and the collisions dissolve. The costs bought with it: more lock memory, faster arrival at the 5000 lock escalation cliff and the operational surprise that REORGANIZE requires page locks, so that index falls out of your reorganize maintenance and rebuilds only.

Order of operations for your table: read the deadlock graph for the actual statements, fix the scan with the index it wanted, check whether escalations feature via the lock escalation Extended Event and only then, if the collisions are the true insert hotspot shape, reach for ALLOW_PAGE_LOCKS OFF with the maintenance caveat documented. Reaching for the switch first treats the symptom while the scan that caused it keeps costing everywhere else.

The deadlock statements were scanning on a column with no index, added it and the PAGE deadlocks stopped within a day, switch never touched. The colleague has read this thread and withdrawn the suggestion with grace.