sql-server – 合并语句死锁本身

create procedure usp_SaveCompanyUserData
    @companyId bigint,@userId bigint,@dataTable tt_CoUserdata readonly
as
begin

    set nocount,xact_abort on;

    merge CompanyUser with (holdlock) as r
    using (
        select 
            @companyId as CompanyId,@userId as UserId,MyKey,MyValue
        from @dataTable) as newData
    on r.CompanyId = newData.CompanyId
        and r.UserId = newData.UserId
        and r.MyKey = newData.MyKey
    when not matched then
        insert (CompanyId,UserId,MyValue) values
        (@companyId,@userId,newData.MyKey,newData.MyValue);

end;

CompanyId,MyKey构成目标表的复合键. CompanyId是父表的外键.此外,CompanyId asc,UserId asc上有一个非聚集索引.

它是从许多不同的线程调用的,我一直在调用同一语句的不同进程之间遇到死锁.我的理解是“with(holdlock)”是防止插入/更新竞争条件错误所必需的.

我假设两个不同的线程在验证约束时以不同的顺序锁定行(或页面),因此是死锁.

这是正确的假设吗？

解决这种情况的最佳方法是什么(即没有死锁,对多线程性能的影响最小)？

(如果您在新标签中查看该图片,则该图片可读.抱歉,尺寸较小.)

> @datatable中最多有28行.
>我已经追溯了代码,我无法在任何地方看到我们在这里开始交易.
>外键设置为仅在删除时级联,并且父表中没有删除.

解决方法

> MERGE的MATCH部分检查匹配的索引,读取锁定那些行/页面.
>如果它没有匹配的行,它将首先尝试插入新的索引行,以便它将请求行/页写锁…

但是,如果另一个用户也在同一行/页面上进行了第1步,则第一个用户将被阻止更新,并且……

如果第二个用户也需要在同一页面上插入,那么它们就处于死锁状态.

AFAIK,只有一种(简单)方法可以100％确定你不能通过这个程序获得死锁,那就是向MERGE添加一个TABLOCKX提示,但这可能会对性能产生非常糟糕的影响.

相反,添加TABLOCK提示可能足以解决问题,而不会对性能产生很大影响.

最后,您还可以尝试添加PAGLOCK,XLOCK或PAGLOCK和XLOCK.这可能会起作用,性能可能不会太糟糕.你必须尝试看看.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!