Deadlock in MERGE due to Primary Key Update

Problem/Motivation

In high-load or concurrent environments, the MERGE query generated by the sqlsrv module can lead to deadlocks. This issue is related to isPartOfPrimaryKey() function, which fails to recognize the cid column as part of the primary key for cache tables. This occurs because cid is not included in the "identities" metadata.

As a result, the generated SQL query looks like the following:

MERGE {cache_entity} AS tgt
USING (VALUES (
  :db_upsert_placeholder_0, :db_upsert_placeholder_1, :db_upsert_placeholder_2,
  :db_upsert_placeholder_3, :db_upsert_placeholder_4, :db_upsert_placeholder_5,
  :db_upsert_placeholder_6
)) AS src ([cid], [expire], [created], [tags], [checksum], [data], [serialized])
ON tgt.[cid] = src.[cid]
WHEN MATCHED THEN
  UPDATE SET [cid] = src.[cid], [expire] = src.[expire], [created] = src.[created],
             [tags] = src.[tags], [checksum] = src.[checksum], [data] = src.[data],
             [serialized] = src.[serialized]
WHEN NOT MATCHED THEN
  INSERT ([cid], [expire], [created], [tags], [checksum], [data], [serialized])
  VALUES (src.[cid], src.[expire], src.[created], src.[tags],
          src.[checksum], src.[data], src.[serialized]);

It seems the deadlock arises because the cid column being part of the primary key is unnecessarily included in the UPDATE clause. Updating a primary key column like cid in a concurrent environment can cause deadlocks due to unnecessary index updates, lock escalations, and conflicting access patterns. Even if the value doesn't change, including it in the UPDATE clause triggers extra locking, increasing contention and the likelihood of deadlocks during MERGE operations.

Steps to reproduce

Proposed resolution

Remaining tasks

User interface changes

API changes

Data model changes