Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

<deadlock-list>
    <deadlock victim="process8f31f9498">
        <process-list>
            <process id="process8f31f9498" taskpriority="5" logused="2616" waitresource="KEY: 20:72057594653704192 (b5f70c321dc6)" waittime="1925" ownerId="3680081779" transactionname="user_transaction" lasttranstarted="2012-10-05T10:02:01.223" XDES="0x8f3b283a8" lockMode="U" schedulerid="7" kpid="11784" status="suspended" spid="252" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2012-10-05T10:02:01.177" lastbatchcompleted="2012-10-05T10:02:01.177" lastattention="1900-01-01T00:00:00.177" clientapp="GN-SQL-GW_National-GW_National Publicati-vz-sql-59" hostname="GN-SQL" hostpid="19364" loginname="DCSINFOSYS\SQLServer" isolationlevel="read committed (2)" xactid="3680081779" currentdb="20" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128024">
                <executionStack>
                    <frame procname="mssqlsystemresource.sys.sp_MSmakegeneration" line="337" stmtstart="29568" stmtend="29880" sqlhandle="0x0300ff7fa885d0f97c6e5901f39f000001000000000000000000000000000000000000000000000000000000">
update dbo.MSmerge_contents with (rowlock) set generation = @target_gen 
                where generation = @gen and tablenick = @art_nick     </frame>
                </executionStack>
                <inputbuf>
Proc [Database Id = 32767 Object Id = -103774808]    </inputbuf>
            </process>
            <process id="process87f1e4188" taskpriority="0" logused="676" waitresource="KEY: 20:72057594653704192 (b5f70c321dc6)" waittime="1994" ownerId="3680083804" transactionname="UPDATE" lasttranstarted="2012-10-05T10:02:01.363" XDES="0x38b50b6a8" lockMode="X" schedulerid="3" kpid="3920" status="suspended" spid="93" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-05T10:02:01.210" lastbatchcompleted="2012-10-05T10:02:01.210" lastattention="1900-01-01T00:00:00.210" clientapp="National KBM R1 Gateway" hostname="HQ-GATEWAY" hostpid="9592" loginname="DCSINFOSYS\GatewayBot" isolationlevel="read committed (2)" xactid="3680083804" currentdb="20" lockTimeout="4294967295" clientoption1="673319008" clientoption2="128056">
                <executionStack>
                    <frame procname="GW_National.dbo.MSmerge_upd_A5AC5485C2484F3C9BFC2CE7057DE6B4" line="97" stmtstart="8470" stmtend="9582" sqlhandle="0x03001400d6e21f3c4265d200d5a0000000000000000000000000000000000000000000000000000000000000">
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)     </frame>
                    <frame procname="GW_National.http.StoreTransaction" line="66" stmtstart="3658" stmtend="3968" sqlhandle="0x03001400f6be377e130a2c019c9e000001000000000000000000000000000000000000000000000000000000">
UPDATE
        Requests
    SET
        StatusMessage = @StatusMessage,
        GatewayVersion = @GatewayVersion
    WHERE
        SubTransactionUUID = @GatewayTransactionGuid     </frame>
                </executionStack>
                <inputbuf>
Proc [Database Id = 20 Object Id = 2117582582]    </inputbuf>
            </process>
        </process-list>
        <resource-list>
            <keylock hobtid="72057594653704192" dbid="20" objectname="GW_National.dbo.MSmerge_contents" indexname="1" id="lock888cc6e00" mode="U" associatedObjectId="72057594653704192">
                <owner-list>
                    <owner id="process87f1e4188" mode="U"/>
                    <owner id="process87f1e4188" mode="X" requestType="convert"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process8f31f9498" mode="U" requestType="convert"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594653704192" dbid="20" objectname="GW_National.dbo.MSmerge_contents" indexname="1" id="lock888cc6e00" mode="U" associatedObjectId="72057594653704192">
                <owner-list>
                    <owner id="process8f31f9498" mode="S"/>
                    <owner id="process8f31f9498" mode="U" requestType="convert"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process87f1e4188" mode="X" requestType="convert"/>
                </waiter-list>
            </keylock>
        </resource-list>
    </deadlock>
</deadlock-list>
share|improve this question

migrated from stackoverflow.com Oct 7 '12 at 11:10

This question came from our site for professional and enthusiast programmers.

3  
You might want to remove your table names and user accounts from the code you provided. –  Vinnie Oct 5 '12 at 20:12
add comment

1 Answer 1

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

<owner id="process8f31f9498" mode="S"/>
<owner id="process8f31f9498" mode="U" requestType="convert"/>

Where as

<owner id="process87f1e4188" mode="U"/>
<owner id="process87f1e4188" mode="X" requestType="convert"/>

Let me know if you have any questions.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.