Deadlock on single table

[CODA PBC]

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.

[Joe Weinstein]

CODA PBC wrote:

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.

Hi. The trouble is that there is more than one lockable object
usually involved in an update. The datarow/page, and likely one
or more index page. It is unfortunate that with the clustered index,
your two users are obtaining those locks in different orders (a function
of the different query plans), causing a deadlock. I suppose that if both
updaters used the same plan (sending the exact same SQL), you would get
the behavior you want. It is sadly ugly that the generated code is
updating every column to change only one. Particularly if the change
includes the clustered key column(s), because it tells the DBMS that the
row has to be deleted from the clustered index (the last nodes of which
are the data pages), and re-inserted where the new key values dictate.
(It might be a fond hope that the DBMS could examine the key values
could be examined and the DBMS could interpret whether the row
actually has to move, but that is in reality not possible. The plan
needs to be made before the actual table data are accessed.).

I hope this helps,
Joe Weinstein at BEA