将部分触发器提取到存储过程后发生死锁
今天，我们有很多令我感到神秘的事情。 简而言之，我们做了一些重构，清理重复代码中的触发器，将其提取到单个可重用的存储过程中。 我们认为这种重构不会有副作用，但我们错了。 发布后，我们遇到了很多死锁和性能下降，没有明显的原因。 在检查了系统表以查看哪些数据库被占用之后，我们发现上面的重构涉及到了，最后我们回滚了更新。
我们没有在测试环境中重现这个问题以探讨治疗方法，所以有些棘手的情况发生，以便发现问题。
以下是变更内容的细节。 我们更新了很多触发器，但它们都非常相似，我会告诉你一个。 它应该是足够的，因为我发现死锁图表显示存在一个死锁，其中两个进程正在执行单个触发器（显示如下）并且死锁。
让我从以前工作的解决方案开始（我们回滚到了，看起来与下面显示的死锁解决方案几乎完全相同）。
CREATE TRIGGER [dbo].[TR__xyz__update_sync_publishers]
ON [dbo].[xyz]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
if(TRIGGER_NESTLEVEL() = 1)
BEGIN
create table #AffectedIDs (advisor_id int primary key)
insert into #AffectedIDs
select distinct t.id
from
(select id
from inserted
inner join xyz a ON a.id = id
where [text] <> ''
union
select id
from inserted
inner join xyz a ON a.id = id
where [text] <> '') t
declare @date datetime = getutcdate()
declare @RegisteredObjectTypeID int = 2
declare @SyncPublisherSourceID int = 1
update pub
set pub.master_update_date = @date
from #AffectedIDs affected
inner join sync_publishers pub on
pub.sync_registered_object_type_id = @RegisteredObjectTypeID
and pub.sync_publisher_source_id = @SyncPublisherSourceID
and pub.sync_object_id = affected.advisor_id
insert into sync_publishers (sync_object_id, sync_registered_object_type_id, sync_publisher_source_id , master_update_date)
select
affected.advisor_id,
@RegisteredObjectTypeID,
@SyncPublisherSourceID,
@date
from #AffectedIDs affected
left join sync_publishers pub on
pub.sync_registered_object_type_id = @RegisteredObjectTypeID
and pub.sync_publisher_source_id = @SyncPublisherSourceID
and pub.sync_object_id = affected.advisor_id
where
pub.sync_object_id is null
drop table #AffectedIDs
END
END
这是死锁的新触发器。
CREATE TRIGGER [dbo].[TR__xyz__update_sync_publishers]
ON [dbo].[xyz]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @ids dtInt
insert into @ids
select distinct t.id
from
(
select id from inserted
INNER JOIN xyz a ON a.id = id
WHERE [text] <> ''
union
select id from inserted
INNER JOIN xyz a ON a.id = id
WHERE [text] <> ''
) t
exec SyncTracker_PublishEvent 2, @ids
END
这里是提取的SP的定义：
CREATE PROCEDURE [dbo].[SyncTracker_PublishEvent]
@objectTypeId int,
@ids dtInt readonly
AS
BEGIN
SET NOCOUNT ON;
if(TRIGGER_NESTLEVEL() > 1) RETURN;
declare @pubSourceId int = 1
declare @date datetime = getutcdate()
update pub
set pub.master_update_date = @date
from @ids affected
inner join sync_publishers pub
on pub.sync_registered_object_type_id = @objectTypeId
and pub.sync_publisher_source_id = @pubSourceId
and pub.sync_object_id = affected.value
insert into sync_publishers (sync_object_id, sync_registered_object_type_id, sync_publisher_source_id , master_update_date)
select affected.value, @objectTypeId, @pubSourceId, @date
from @ids affected
left join sync_publishers pub
on pub.sync_registered_object_type_id = @objectTypeId
and pub.sync_publisher_source_id = @pubSourceId
and pub.sync_object_id = affected.value
where
pub.sync_object_id is null
END
GO
dtInt的定义。
CREATE TYPE [dbo].[dtInt] AS TABLE
(
[value] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[value] ASC
)
)
最后是死锁图。
<deadlock>
<victim-list>
<victimProcess id="processe1892fe8c8" />
</victim-list>
<process-list>
<process id="processe1892fe8c8" taskpriority="0" logused="3824" waitresource="KEY: 5:72057602924150784 (4776e78e2961)" waittime="5686" ownerId="2583257965" transactionname="user_transaction" lasttranstarted="2016-10-03T08:30:42.500" XDES="0xe192b24408" lockMode="U" schedulerid="6" kpid="41296" status="suspended" spid="141" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-10-03T08:30:42.503" lastbatchcompleted="2016-10-03T08:30:42.493" lastattention="2016-10-03T08:29:01.693" clientapp="..." hostname="..." hostpid="22572" loginname="kbuser" isolationlevel="read committed (2)" xactid="2583257965" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="63c1b4d8-1c55-4429-b057-81fb6da8f780.dbo.SyncTracker_PublishEvent" line="21" stmtstart="1178" stmtend="1680" sqlhandle="0x030005007bf23c4b5012b40092a6000001000000000000000000000000000000000000000000000000000000">
update pub
set pub.master_update_date = @date
from @ids affected
inner join sync_publishers pub
on pub.sync_registered_object_type_id = @objectTypeId
and pub.sync_publisher_source_id = @pubSourceId
and pub.sync_object_id = affected.valu </frame>
<frame procname="63c1b4d8-1c55-4429-b057-81fb6da8f780.dbo.TR__xyz__update_sync_publishers" line="28" stmtstart="1300" stmtend="1372" sqlhandle="0x03000500f711233ddee4c60090a6000000000000000000000000000000000000000000000000000000000000">
exec SyncTracker_PublishEvent 2, @id </frame>
<frame procname="unknown" line="1" stmtstart="1054" stmtend="3032" sqlhandle="0x02000000912653235c5ef3529289f19ae4445e62ee1ccbc00000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
</process>
<process id="processdfa401b848" taskpriority="0" logused="9384" waitresource="KEY: 5:72057602924150784 (1501093f83b4)" waittime="5814" ownerId="2582414029" transactionname="user_transaction" lasttranstarted="2016-10-03T08:30:09.933" XDES="0x104486ac408" lockMode="U" schedulerid="1" kpid="19548" status="suspended" spid="213" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-10-03T08:30:53.047" lastbatchcompleted="2016-10-03T08:30:53.047" lastattention="1900-01-01T00:00:00.047" clientapp="..." hostname="..." hostpid="6196" loginname="kbuser" isolationlevel="read committed (2)" xactid="2582414029" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="63c1b4d8-1c55-4429-b057-81fb6da8f780.dbo.SyncTracker_PublishEvent" line="21" stmtstart="1178" stmtend="1680" sqlhandle="0x030005007bf23c4b5012b40092a6000001000000000000000000000000000000000000000000000000000000">
update pub
set pub.master_update_date = @date
from @ids affected
inner join sync_publishers pub
on pub.sync_registered_object_type_id = @objectTypeId
and pub.sync_publisher_source_id = @pubSourceId
and pub.sync_object_id = affected.valu </frame>
<frame procname="63c1b4d8-1c55-4429-b057-81fb6da8f780.dbo.TR__xyz__update_sync_publishers" line="28" stmtstart="1300" stmtend="1372" sqlhandle="0x03000500f711233ddee4c60090a6000000000000000000000000000000000000000000000000000000000000">
exec SyncTracker_PublishEvent 2, @id </frame>
<frame procname="unknown" line="1" stmtstart="1120" stmtend="3132" sqlhandle="0x020000007414d821ed68a2ab4462b4eca6b2fdb4ba28cc350000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057602924150784" dbid="5" objectname="63c1b4d8-1c55-4429-b057-81fb6da8f780.dbo.sync_publishers" indexname="IX__sync_publishers__registered_object_type_id__sync_object_id" id="lock10887a96b00" mode="X" associatedObjectId="72057602924150784">
<owner-list>
<owner id="processdfa401b848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processe1892fe8c8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057602924150784" dbid="5" objectname="63c1b4d8-1c55-4429-b057-81fb6da8f780.dbo.sync_publishers" indexname="IX__sync_publishers__registered_object_type_id__sync_object_id" id="lockdb7d7b8200" mode="X" associatedObjectId="72057602924150784">
<owner-list>
<owner id="processe1892fe8c8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processdfa401b848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
sync_publishers的定义可以在这里找到：http://pastebin.com/LviwwCDi。
如果您对可行的原因有任何想法 - 欢迎分享 - 我们将非常感谢！
UPDATE 1. UPDATE / INSERT到sync_publishers的实际执行计划
实际执行计划看起来非常相似。
新的执行计划（偶尔会出现死锁）。 
老执行计划（那不）。 
更新2.尝试了一些建议
我今天尝试了一些建议：
由于在非聚集索引中通过删除列丢失了sync_publisher_source_id ，因此摆脱了查询计划中的“键查找” - 这在我们的实现中并不是强制性的。
将UPDATE + INSERT重写为单个MERGE语句。
MERGE sync_publishers2 t
USING @ids s
ON s.[value] = t.sync_object_id
and t.sync_registered_object_type_id = @objectTypeId
WHEN MATCHED
THEN UPDATE
SET master_update_date = @date
WHEN NOT MATCHED
THEN INSERT
(sync_object_id, sync_registered_object_type_id, master_update_date)
VALUES
(s.[value], @objectTypeId, @date);
MERGE语句开始出现死锁。 新的死锁图可以在这里查看：http://pastebin.com/QNJk7tea。
更新3.尝试MERGE提示
我试图用xlock和holdlock提示来做MERGE - 但是没有运气 - 再次遇到了MERGE的僵局。
MERGE sync_publishers2 with(xlock, holdlock) t
这个版本在并行3个工作负载会话的1小时后似乎没有死锁。 我无法真正发现死锁的确切原因，但我能做的是强调与死锁试验的不同之处，它也包含MERGE语句：版本波纹管（似乎工作正常）使用CTE来允许MERGE的ON表达式重写为仅提及PK列（ sync_publisher_id ）。
CREATE PROCEDURE [dbo].[SyncTracker_PublishEvent2]
@objectTypeId int,
@ids dtInt readonly
AS
BEGIN
SET NOCOUNT ON;
-- stop recoursive propogations
if(TRIGGER_NESTLEVEL() > 1) RETURN;
declare @date datetime = getutcdate()
;WITH sync_publishers2CTE AS
(
SELECT [sync_publisher_id],
[sync_object_id],
[sync_registered_object_type_id],
[master_update_date]
FROM [dbo].[sync_publishers2]
WHERE sync_registered_object_type_id = @objectTypeId
)
MERGE sync_publishers2CTE WITH (XLOCK) trg
USING
(
SELECT sp.sync_publisher_id,
s.Value AS sync_object_id,
@objectTypeId AS sync_registered_object_type_id,
@date AS master_update_date
FROM @ids s
LEFT JOIN sync_publishers2 sp ON sp.sync_object_id = s.Value
AND sp.sync_registered_object_type_id = @objectTypeId
) src
ON (trg.sync_publisher_id = src.sync_publisher_id)
WHEN MATCHED
THEN UPDATE
SET trg.master_update_date = src.master_update_date
WHEN NOT MATCHED
THEN INSERT
(sync_object_id, sync_registered_object_type_id, master_update_date)
VALUES
(sync_object_id, sync_registered_object_type_id, master_update_date);
END
样本执行计划：
上一篇: Deadlocks after extracting part of the trigger into a stored procedure