HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

What makes SQL Server prefer page locking over row locking?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
preferwhatsqlmakeslockingpageserverrowover

Problem

I'm probably dumb for asking this, but I'm wondering WHY would SQL Server prefer page locking over row locking when running DML queries.

In my specific scenario, I'm seeing in various deadlock graphs that queries that should S lock 400-500 rows are choosing to page lock instead - entering the deadlock in the first place. Note that I have not disabled row locks in our indexes. Moreover, I don't really think I'm missing indexes as the only suggestion coming from SQL are of the "create a nonclustered index on every column in this table" sort.

To be clear: I'm not asking for help on solving our deadlocks (not yet at least), I just want to understand if I'm missing something to try and foresee how SQL Server will behave.

Solution

There are many reasons why page locks might be used over row locks, but the most basic answer is memory management. 400-500 row locks might be far less attractive than locking a smaller number of pages to accomplish the same task.

It's also possible that the way the query is written, or the way the index is designed is responsible for the type of locks taken.

See my posts here for greater detail:

-
Bad Estimates And Locking

-
Index Key Column Order And Locking

Context

StackExchange Database Administrators Q#242354, answer score: 5

Revisions (0)

No revisions yet.