within a transaction with read committed isolation level if you do an update on a set of rows and then do a select on the same set of rows will sql server not reuse the x locks it acquires on the index when doing the update to do the next select or will it try to get a new set of locks on the index. I'm having a deadlock scenario where the two processes are fighting for a index key lock-one of the two resources that is locked. This happens when one of the processes has already done an update and then does a select on the same rows. My question is how come that process waits to acquire a key lock on the index when it has already gotten the x lock on the same row in the previous step.
Asked
Active
Viewed 80 times
0
-
Is the SELECT being attempted by the same connection that performed the update? Your question mentions two processes, which could mean that a different connection is involved, so I just want to make sure. – Fred Sobotka Sep 19 '12 at 04:46
-
yes, the select is within the same transaction that did the update – nams Sep 19 '12 at 04:49