Deadlock on Merge replication

I'm having a really confusing problem on the msmerge_contents table on some
of my merge replication databases. It seems I'm receiving a dead lock on the exact
same row on the same index on the table msmerge_contents.

Here is the deadlock xml i'm currently seeing. Based off what i can tell
the index name: 1 means clustered index (looking at the associated objid).
So it is same i/o path and the row hashid is the same (b5f70c321dc6) which is the same row.
One process is the msmakegeneration that i believe the merge agent runs (every minute in this case) and the other process is the trigger that gets call (every time requests table gets updated). Could anyone shine a light on this issue, I'm still learning deadlocks and to me this doesn't make sense. Thanks

```






update dbo.MSmerge_contents with (rowlock) set generation = @target_gen
where generation = @gen and tablenick = @art_nick


Proc [Database Id = 32767 Object Id = -103774808]




update MSmerge_ctsv_A5AC5485C2484F3C9BFC2CE7057DE6B4 with (rowlock)
set lineage = { fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) },
generation = @newgen,
partchangegen = case when (@partchange = 1 or @joinchange = 1) then @newgen else partchangegen end,
colv1 = NULL
FROM inserted as I JOIN MSmerge_ctsv_A5AC5485C2484F3C9BFC2CE7057DE6B4 as V with (rowlock)
ON (I.rowguidcol=V.rowguid)
and V.tablenick = @tablenick
option (force order, loop join)

UPDATE
Requests
SET
StatusMessage = @StatusMessage,
GatewayVersion = @GatewayVersion
WHERE
SubTransactionUUID = @GatewayTransactionGuid


Proc [Database Id = 20 Object Id = 2117582582]

This lock compatibility matrix can shed some light. Note if granted mode is S (shared read), transaction requesting for U (can update) will be granted access. Also note same is true other way, i.e. U is existing lock and request for S is granted. X (Exclusive access )is not compatible with neither S not U.
Also U is not compatible with U

i.e.

Existing Requested Status
S U Yes
U S Yes
U U No
S X No
U X No

In your two different transactions first acquire S or U on same resource. Then some time down the line they want to convert the current lock to more exclusive lock. Here is one possible order which can result in above deadlock.

T1 : Request S
SQL : Granted.
T2 : Request U
SQL : Granted (because U is compatible with S)
T1 : Convert from S to U. (lock escalation)
SQL : Wait. T2 is holding U.
T2 : Convert from U to X (lock escalation)
SQL : Wait. T1 is holding S. (DEADLOCK!!)

To resolve this deadlock don't do conversion of lock. Try to get most exclusive lock first. i.e T1 should straight go for U and T2 should go for X

In your case

Where as

Let me know if you have any questions.

StackExchange Database Administrators Q#25565, answer score: 2