select t.*, row_number() over (
    partition by t.Entity_Identifier 
    order by t.Message_Sequence
    ) as seqnum 
from Process_Activity t (ROWLOCK) 
where t.Process_Id = 

Is it possible for this select to retrieve a different number of rows based on whether it can lock a row or not?

We have a situation where the select will bring back 18 rows when there is another process performing an insert/update on the same table, but the select will bring back 33 rows (the correct amount) when the other process is not running.

I feel the ROWLOCK is getting in the way. Why use a rowlock on a select?

  • 1
    What is the database isolation level ? Is it set to snapshot ? – Kin Shah Jul 28 '15 at 17:20
  • 1
    If rows are being skipped,that might suggest that the READPAST hint is also being used. – RLF Jul 28 '15 at 17:25

Your Answer

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

Browse other questions tagged or ask your own question.