Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

MERGE statement provokes deadlocking due to incorrect locking behavior

During MERGE statement execution one can observe that X lock placed on the object after IX lock has been already placed on the object by the same session.

Such locking behavior provokes (and actually leads to) deadlocking under concurrency conditions, since two IX locks compatible, but two X are not.

According to Lock Modes (https://technet.microsoft.com/en-us/library/ms175519.aspx)
"Intent locks ... signal intent to place locks at a LOWER level."

so, I consider such behavior as not valid.

See steps to reproduce for details.

More details can be found at DBA.SE in related question:
https://dba.stackexchange.com/q/187776

In case if this resolves to "By Design" provide explanation please.

1 vote
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base