Deadlock, waiting for a 'next key'
I'm having a deadlock that comes along every once in a while, not a big issue but there is something I try to figure out and I am not quite sure what to make of it.
The deadlock is a result between an UPDATE WHERE (Process 1) and a SELECT (Process 2).
The following has been retrieve from the log :
Deadlock Id 1: Process (Familyid 0, Spid 4093) was
waiting for a 'next key' lock on row 0 page 12282632 of the 'tom_req_assign'
table in database 'tom_data' but process (Familyid 0, Spid 4651) already held a
'shared row' 'range' lock on it.
It is the part where you can read : "was waiting for a 'next key' lock" that is bugging me.
Am I correct if I understand this as : "I am waiting to get a lock on the next value I need to update" ? Or is there something else to understand?
Also, trying to minimize the occurrence of this deadlock, would it be a good idea, since I need to update many records, to acquire a table lock before updating my records. From my point of view, I feel it is a good idea, what your thoughts?