遵循模式的任何过程:

BEGIN TRAN

check if row exists with SELECT

if row doesn't exist INSERT

COMMIT

在生产中会遇到麻烦,因为没有什么可以阻止两个踏板同时进行检查,并且都得出了应该插入的结论。特别是,在序列化隔离级别(如您的情况)下,此模式是

放心

陷入僵局。

一

许多的

更好的模式是使用数据库唯一约束并始终插入、捕获重复的键冲突错误。这也显著提高了性能。

另一种选择是使用

MERGE

声明:

create procedure usp_getOrCreateByMachineID

@nMachineId int output,

@fkContract int,

@lA int,

@lB int,

@lC int,

@id int output

as

begin

declare @idTable table (id int not null);

merge Machines as target

using (values (@nMachineID, @fkContract, @lA, @lB, @lC, GETDATE()))

as source (MachineID, ContractID, lA, lB, lC, dteFirstAdded)

on (source.MachineID = target.MachineID)

when matched then

update set @id = target.MachineID

when not matched then

insert (ContractID, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded)

values (source.contractID, source.lA, source.lB, source.lC, source.dteFirstAdded)

output inserted.MachineID into @idTable;

select @id = id from @idTable;

end

go

create procedure usp_getOrCreateByMetrics

@nMachineId int output,

@fkContract int,

@lA int,

@lB int,

@lC int,

@id int output

as

begin

declare @idTable table (id int not null);

merge Machines as target

using (values (@nMachineID, @fkContract, @lA, @lB, @lC, GETDATE()))

as source (MachineID, ContractID, lA, lB, lC, dteFirstAdded)

on (target.iA_Metric = source.lA

and target.iB_Metric = source.lB

and target.iC_Metric = source.lC)

when matched then

update set @id = target.MachineID

when not matched then

insert (ContractID, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded)

values (source.contractID, source.lA, source.lB, source.lC, source.dteFirstAdded)

output inserted.MachineID into @idTable;

select @id = id from @idTable;

end

go

此示例将这两种情况分开,因为T-SQL查询应该

从未

尝试在一个查询中解析两个不同的解决方案(结果永远不可优化)。由于手头的两个任务(按mahsine id获取和按度量获取)完全独立,因此应该是单独的过程,调用方应该调用相应的过程,而不是传递标志。这个例子要求如何使用合并来实现(可能)期望的结果,但是当然,一个

对的

和

最优的

解决方案取决于实际的模式(表定义、索引和集中位置)和实际的需求(如果条件已经匹配,而不是输出和@id,则不清楚过程应该做什么?).

通过消除可序列化的隔离,这将不再是

放心

死锁,但它可能仍然死锁。当然,解决死锁完全依赖于未指定的模式,因此在此上下文中无法提供死锁的解决方案。对于锁定所有候选行(force updlock或甚至tablocx),有一个巨大的锤子,但是这样的解决方案会在大量使用时破坏吞吐量,因此我不能在不了解用例的情况下推荐它。