4

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.

Improve this question
0

1 Answer 1

Reset to default
4

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:

Improve this answer
1
  • You know, I had a feeling the answer was right under my nose. Thank you kind sir for showing me the error of my ways.
    – GMassDBA
    Jul 9, 2019 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.