Deadlock on same table with X and U lock

Question

I have a stored procedure with the following two transactions under Begin and Commit tran.

UPDATE  mytable
SET     UserID = @ToUserID
WHERE   UserID = @UserID 

DELETE  FROM mytable
WHERE   UserID = @UserID 

On running that Store Procedure with multiple executions I get dead lock. Here is deadlock Graph:

<deadlock-list>
    <deadlock victim="process16409057468">
        <process-list>
            <process id="process16409057468" taskpriority="0" logused="912" waitresource="RID: 6:1:2392:152" waittime="3022" ownerId="6283339" transactionname="user_transaction" lasttranstarted="2019-02-08T21:08:24.663" XDES="0x16401b98490" lockMode="U" schedulerid="8" kpid="23924" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-02-08T21:08:24.667" lastbatchcompleted="2019-02-08T21:08:24.667" lastattention="1900-01-01T00:00:00.667" clientapp=".Net SqlClient Data Provider" hostname="GYAAN" hostpid="5624" loginname="sa" isolationlevel="read uncommitted (1)" xactid="6283339" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="mytable" line="377" stmtstart="33320" stmtend="33540" sqlhandle="0x030006004f6bf63211085201eaa9000001000000000000000000000000000000000000000000000000000000">
                        UPDATE  mytable
                        SET     UserID = @ToUserID
                        WHERE   UserID = @UserID      
                    </frame>
                </executionStack>
                <inputbuf>
                    Proc [Database Id = 6 Object Id = 855010127]    
                </inputbuf>
            </process>
            <process id="process163feab3088" taskpriority="0" logused="912" waitresource="RID: 6:1:2392:149" waittime="99" ownerId="6282851" transactionname="user_transaction" lasttranstarted="2019-02-08T21:08:22.107" XDES="0x16401b20490" lockMode="U" schedulerid="3" kpid="33220" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-02-08T21:08:22.103" lastbatchcompleted="2019-02-08T21:08:22.107" lastattention="1900-01-01T00:00:00.107" clientapp=".Net SqlClient Data Provider" hostname="GYAAN" hostpid="5624" loginname="sa" isolationlevel="read uncommitted (1)" xactid="6282851" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="mytable" line="382" stmtstart="33650" stmtend="33848" sqlhandle="0x030006004f6bf63211085201eaa9000001000000000000000000000000000000000000000000000000000000">
                        DELETE  FROM mytable
                        WHERE   UserID = @UserID     
                    </frame>
                </executionStack>
                <inputbuf>
                    Proc [Database Id = 6 Object Id = 855010127]    
                </inputbuf>
            </process>
        </process-list>

        <resource-list>
            <ridlock fileid="1" pageid="2392" dbid="6" objectname="mytable" id="lock164096b7800" mode="X" associatedObjectId="72057594051493888">
                <owner-list>
                    <owner id="process163feab3088" mode="X"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process16409057468" mode="U" requestType="wait"/>
                </waiter-list>
            </ridlock>
            <ridlock fileid="1" pageid="2392" dbid="6" objectname="mytable" id="lock163f7fb2c80" mode="X" associatedObjectId="72057594051493888">
                <owner-list>
                    <owner id="process16409057468" mode="X"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process163feab3088" mode="U" requestType="wait"/>
                </waiter-list>
            </ridlock>
        </resource-list>
    </deadlock>
</deadlock-list>

Can some one explain how and what could have cause the dead lock?

I am having some hard time understanding this X and U lock flow at this moment.

Can you explain the flow what could have occurred X and U to stuck between dead lock?

Ac

I don't see the point in replacing every row with a specified UserId with ToUserId. Because, after the update, you try to delete every row with the previous UserId. It doesn´t make any sense to me, the DELETE query is going to delete 0 rows. Can you explain the final DELETE query? – Mannose

Please add details of the indexes that are on the table – Carencarena

Your code really makes no sense. Could you post the real stored procedure body? – Lilongwe

Answer 1

You have not provided sufficient details of your queries, but the deadlock graph you have shared clearly shows that it’s a “writer-writer” deadlock due to parallelism, as all the locks granted or requested are either X or U.

<resource-list>
    <ridlock fileid="1" pageid="2392" dbid="6" objectname="xx" id="lock164096b7800" mode="X" associatedObjectId="72057594051493888">
        <owner-list>
            <owner id="process163feab3088" mode="X"/>
        </owner-list>
        <waiter-list>
            <waiter id="process16409057468" mode="U" requestType="wait"/>
        </waiter-list>
    </ridlock>
    <ridlock fileid="1" pageid="2392" dbid="6" objectname="mytable" id="lock163f7fb2c80" mode="X" associatedObjectId="72057594051493888">
        <owner-list>
            <owner id="process16409057468" mode="X"/>
        </owner-list>
        <waiter-list>
            <waiter id="process163feab3088" mode="U" requestType="wait"/>
        </waiter-list>
    </ridlock>
</resource-list>

One important thing about the writer-writer deadlocks is that SQL Server holds exclusive locks until the transaction commits, unlike shared locks which in the default read committed isolation level.

Without query details it is not going to essay to figure out the exact cause of the error. Normally you need to re-factor your queries to avoid the deadlocks like

1. Move SELECT queries outside the transactions, so that it only returns the committed data, rather than returning data containing modifications that might roll back.
2. Sometime you need to tune the query so the SQL Server won’t need to parallelize it as much or at all.
3. Adding MAXDOP hint to the query to force it run serially will remove any change of an intra-query parallelism deadlock.

Other common reason of deadlock is when you read data with the intention to update or delete it later by just putting a shared lock, the following UPDATE statement can’t acquire the necessary Update Locks, because the resource is already blocked by another process causing the deadlock.

To resolve this you can select the records using WITH (SERIALIZABLE) like following

UPDATE  mytable WITH (SERIALIZABLE)
SET     UserID = @ToUserID
WHERE   UserID = @UserID

This will take the necessary Update lock on the record and will stop other process to acquire any lock (shared/exclusive) on the record and will prevent from any deadlocks.

You also need to look for ordering of your queries, a wrong order can lead to a Cycle Deadlock. In this scenario a query waits for another one in different transactions to complete.

Denti

Answer 2

Except from the fact that you exapmple is updating and then deleting the same record, which looks wrong, you can aquire all the required xlocks before you perform the DDL.

select UserID
FROM mytable   with(xlock, holdlock, rowlock)
WHERE UserID  in (@ToUserID, @UserID)

UPDATE  mytable
SET     UserID = @ToUserID
WHERE   UserID = @UserID 

DELETE  FROM mytable
WHERE   UserID = @UserID 

Stalder