We have one user who enters a transaction and then does a single row
update (updates all columns but only one is changing - this is due to
the way our sql is generated in the application), at this point
another user enter a transaction and tries to update the same row (he
understandably has to sit and wait while he is blocked by the original
user). The original user then updates the same row again – at this
point the second user is chosen as a deadlock victim and killed. If I
try and recreate this with any other tables(or pubs) I get my expected
behaviour of the original user just doing 2 successful updates and the
second user then completing his update once the original user has
either committed his changes or rolled back. The query plan indicates
that a drop and insert of the row is happening (this is not the case
with any other tables where we get our expected behaviour). This only
happens when the index is clustered - if we use a non-clustered index
it does not occur.
Is this expected behaviour? it seems dangerous to me as the first
user has not commited or rolled back his updates. It was only
highlighted by a fault in our application that caused the second
update to be executed.
I have some thoughts about it being something to do with a row lock
being relased due to a delete / insest of the row in the second update
(we see this in the execution plan).....
Any help much appreciated as I am struggling to get my head round how
the second user was ever able to get hold of the resource.