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

前端之家收集整理的这篇文章主要介绍了sql-server – 合并语句死锁本身前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有以下过程(sql Server 2008 R2):
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.这可能会起作用,性能可能不会太糟糕.你必须尝试看看.

原文链接:https://www.f2er.com/mssql/79339.html

猜你在找的MsSQL相关文章