logo标签列表

在同一张表上使用X锁和U锁引发的死锁问题

sqlsql-serverdatabase-administrationdatabase-deadlocks
11

我有一个存储过程，在Begin和Commit tran下有以下两个事务。

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

DELETE  FROM mytable
WHERE   UserID = @UserID

当我运行该存储过程进行多次执行时，会出现死锁。以下是死锁图：

<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>

有人能解释一下是如何以及什么原因导致了死锁吗？

我现在对于X和U锁的流程有些难以理解。

你能否解释一下发生了什么，导致X和U之间陷入死锁？

1
我不明白为什么要用指定的ToUserId替换每一行中的UserId。因为在更新之后，你会尝试删除所有以前的UserId行。这对我来说毫无意义，DELETE查询将不会删除任何行。你能解释一下最终的DELETE查询吗？ - Estanislao Pérez Nartallo
1
请添加表上的索引细节。 - Steve Ford
2
你的代码真的毫无意义。你能发一下真正的存储过程体吗？ - gotqn
2个回答
9

您没有提供足够详细的查询信息，但您分享的死锁图清楚地显示这是由于并行性引起的“写入者-写入者”死锁，因为所有授予或请求的锁都是X或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>
重要的一点是，在read committed隔离级别下，SQL Server会持有排他锁直到事务提交，而不是共享锁。没有查询详细信息的情况下，无法确定错误的确切原因。通常需要重新设计查询以避免死锁，例如将SELECT查询移到事务之外，这样它只返回已提交的数据，而不是包含可能回滚修改的数据。有时需要调整查询，使SQL Server尽可能少或完全不进行并行化。将MAXDOP提示添加到查询中，以强制其串行运行，将消除任何内部查询并发死锁的可能性。读取数据时，如果只打共享锁，而在稍后更新或删除数据时，UPDATE语句无法获取所需的更新锁，因为资源已被其他进程阻塞，从而导致死锁。解决此问题的方法是使用WITH (SERIALIZABLE)选择记录。 
UPDATE  mytable WITH (SERIALIZABLE)
SET     UserID = @ToUserID
WHERE   UserID = @UserID

这将在记录上获取必要的更新锁，并阻止其他进程获取任何锁（共享/排他）以及防止死锁。

您还需要查找查询的顺序，错误的顺序可能会导致循环死锁。在这种情况下，查询等待另一个在不同事务中完成的查询。

2
除了您的示例在更新然后删除相同记录方面看起来有问题之外，您可以在执行DDL之前获取所有所需的xlocks。"Except from the fact that"可翻译为"除了...之外"，"acquire"可翻译为"获取"，"perform"可翻译为"执行"，"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
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，