I can't seem to find an explanation as to why this is happening, but while trying to debug a deadlock issue, I found out that SQLServer is locking far more records than it needs to when I include an "order by" clause in my query with the updlock and readpast hints.

For context, I am doing a "select top 1" query on a table that is used for queuing jobs in a multi-node application. I want to grab the oldest job that has not been processed yet, that matches certain criteria that requires joining on a few tables (all of these tables have the nolock hint) If I sort the list by the creation date, all of the records matching the filter are locked, but only one is returned. But if I remove the sorting, only the one record that I want is locked.

Any hints as to why this is happening?


As it turns out, adding an index on the column that I'm sorting by fixes the problem.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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