SQL Server deadlock graph: please explain

Question

I am incapable of understanding a deadlock_xml from Azure SQL Server V12. Here is the graph (which is consistent with the underlying XML):

• So the rhs process has issued an Update lock and the lhs process, which also wants an Update lock on the same resource, has to wait.

• Then the rhs process requests an exclusive lock on the same resource which apparently is blocked due to an Update lock of the lhs process (why? because it has requested one??!).

My question:

Why the rhs process cannot restrict the U lock to an X lock?

I am trying to understand this at a high level but, nevertheless, here are the specifics:

• Both processes were running the same sp

• The sp performs an upsert op: Insert where not exists (Select...); if @@ROWCOUNT= 0 Update...

• the transactions were serializable.

Answer 1

Both actions are affecting the same table. You see a Key Lock on the primary key index "PK_Product".

I try to put it in an easy example:

A man comes into a room and says: "I'm going to tear down this wall!" and walks out to get his tools. Another one comes in and says: "I'm going to paint this wall!" and walks out to get the color. Now both come back and want to start the work. The one tearing down the wall starts a little earlier. Now for the second man there is no sense in waiting. These processes cannot be serialized. He cannot wait, until the first one is finished. The first man's work changed the base of his work and made it impossible.

For you this means: Both processes say: "We are going to update this table, but we check for a certain condition first". Since an INSERT affects the primary key the second process cannot wait and continue a little later. This process can only be killed and re-started.

You might have a look at MERGE-command, which allows you to peform the upsert within one single go.