c# - 插入语句/存储过程死锁

Question

我有一个使用 linq 死锁的插入语句。所以我把它放在一个存储过程中，以防周围的语句影响它。

现在存储过程被死锁了。根据 Server Profiler，insert 语句的某些方面正在锁定自身。它声称其中两个插入语句正在等待释放 PK 索引:

当我将代码放入存储过程时，它现在声明此存储过程已与此存储过程的另一个实例发生死锁。

这是代码。 select 语句类似于 linq 在执行自己的查询时使用的语句。我只是想看看该项目是否存在，如果不存在则将其插入。我可以通过 PK 或一些查找值找到系统。

       SET NOCOUNT ON;
       BEGIN TRY
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

        BEGIN TRANSACTION SPFindContractMachine
        DECLARE @id int;
        set @id = (select [m].pkID from Machines as [m]
                        WHERE ([m].[fkContract] = @fkContract) AND ((
                        (CASE 
                            WHEN @bByID = 1 THEN 
                                (CASE 
                                    WHEN [m].[pkID] = @nMachineID THEN 1
                                    WHEN NOT ([m].[pkID] = @nMachineID) THEN 0
                                    ELSE NULL
                                 END)
                            ELSE 
                                (CASE 
                                    WHEN ([m].[iA_Metric] = @lA) AND ([m].[iB_Metric] = @lB) AND ([m].[iC_Metric] = @lC) THEN 1
                                    WHEN NOT (([m].[iA_Metric] = @lA) AND ([m].[iB_Metric] = @lB) AND ([m].[iC_Metric] = @lC)) THEN 0
                                    ELSE NULL
                                 END)
                         END)) = 1));
        if (@id IS NULL)
        begin
            Insert into Machines(fkContract, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded) 
                values (@fkContract, @lA, @lB, @lC, GETDATE());

            set @id = SCOPE_IDENTITY();
        end

        COMMIT TRANSACTION SPFindContractMachine

        return @id;

    END TRY
    BEGIN CATCH
        if @@TRANCOUNT > 0
            ROLLBACK TRANSACTION SPFindContractMachine
    END CATCH

Answer 1

遵循该模式的任何过程:

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 查询不应永远尝试在一个查询中解决两个不同的解决方案(结果永远不可优化)。由于手头的两个任务(通过机器 ID 获取和通过指标获取)是完全独立的，因此应该是单独的过程并且调用者应该调用适当的过程，而不是传递标志。此示例展示了如何使用 MERGE 实现(可能)期望的结果，但当然，正确 和最佳 解决方案取决于实际架构(表定义、索引和约束到位)和实际要求(不清楚如果标准已经匹配，而不是输出和@id，该过程应该做什么？)。

通过消除 SERIALIZABLE 隔离，这不再保证死锁，但它仍然可能死锁。当然，解决死锁完全取决于未指定的模式，因此在这种情况下实际上无法提供解决死锁的方法。有一个锁定所有候选行的大锤(强制 UPDLOCK 甚至 TABLOCX)，但这样的解决方案会在大量使用时降低吞吐量，所以我不能在不知道用例的情况下推荐它。