我有以下过程(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.这可能会起作用,性能可能不会太糟糕.你必须尝试看看.
