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
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base