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
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?