插入语句/存储过程死锁

Question

我有一个用linq编写的插入语句，会导致死锁。因此，我将它放在了一个存储过程中，以避免周围的语句对其产生干扰。

现在这个存储过程也出现了死锁。服务分析器显示，这个插入语句在锁定自身，其中两个语句正在等待主键索引被释放：

当我将代码放到存储过程中后，它现在声称该存储过程与该存储过程的另一个实例发生了死锁。

以下是代码。选择语句类似于linq执行查询时使用的语句。我只想查看该项是否存在，如果不存在，则插入它。我可以通过主键或一些查找值来找到该系统。

       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

- BabelFish

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，过程预期执行什么操作？）。

通过消除串行化隔离，这不再是死锁保证，但它仍然可能死锁。当然，解决死锁完全取决于未指定的模式，因此在此情况下无法提供死锁的解决方案。锁定所有候选行（强制UPDLOCK甚至TABLOCX）是一种解决方法，但这种解决方案会在重压使用时降低吞吐量，因此我不能推荐它而不知道用例。

- Remus Rusanu

---

我使用了可序列化（serializable）是因为我认为这会提供更好的锁定机制，以确保只有一个进程可以访问。但我的假设是错误的。我想我误读了一些网站。 - BabelFish

请澄清一下，您是说“永远不要使用返回值”还是“永远不要使用‘return’值，比如‘return @id;’”。 - BabelFish

可序列化隔离级别保证了当一个读操作（如SELECT）在事务中稍后再次运行时，将返回完全相同的行。因此，如果两个事务T1和T2同时运行您的存储过程中的原始SELECT语句，它们都可以得到保证：该SELECT结果不会发生改变。假设两者都没有找到任何行，则它们都会继续插入。假设T1先插入，如果T1成功插入并提交，T2再次运行SELECT时，将看到T1的插入。所以T1必须无法插入，否则它将破坏T2执行的可序列化读操作。 - Remus Rusanu

现在你转而将相同的逻辑应用于T2。它的INSERT无法成功并提交，因为如果是这样的话，那么如果T1重复SELECT，它将看到T2的插入。因此，T1被T2阻塞，T2被T1阻塞，因此死锁发生了。可串行化隔离的实际实现是使用范围锁完成的，这就是为什么您在死锁中看到Range-S和Range-N锁的原因，但这只是实现方式而已（2/2）。 - Remus Rusanu

关于返回值：客户端API（ODBC、OleDB、ADO.Net、ADO、JDBC、PHP驱动程序等）在支持过程返回值方面差异巨大。数据访问和ORM层（nHibernate、Linq2SQL、EF等）也对返回值处理方式不同，有些框架甚至无法访问返回值。另一个问题是数据库中的数据类型会发生变化，例如int ID可能在明年变成bigint或decimal，但是过程返回类型是固定的（int），无法跟随变化。最后，输出参数不能被忽略。总之，返回值很糟糕。 - Remus Rusanu

Answer 2

这个SQL语句怎么样？它将检查现有数据和插入新数据合并为一条语句。这样，当两个线程同时运行时，它们不会因为互相等待而死锁。最好的情况是，第二个线程等待第一个线程，但只要第一个线程完成，第二个线程就可以运行。

BEGIN TRY

  BEGIN TRAN SPFindContractMachine

  INSERT INTO Machines (fkContract, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded)
    SELECT @fkContract, @lA, @lB, @lC, GETDATE()
      WHERE NOT EXISTS (
        SELECT * FROM Machines
        WHERE fkContract = @fkContract
        AND ((@bByID = 1 AND pkID = @nMachineID)
             OR
             (@bByID <> 1 AND iA_Metric = @lA AND iB_Metric = @lB AND iC_Metric = @lC))

  DECLARE @id INT

  SET @id = (
    SELECT pkID FROM Machines
    WHERE fkContract = @fkContract
    AND ((@bByID = 1 AND pkID = @nMachineID)
         OR
         (@bByID <> 1 AND iA_Metric = @lA AND iB_Metric = @lB AND iC_Metric = @lC)))

  COMMIT TRAN SPFindContractMachine

  RETURN @id

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN SPFindContractMachine
END CATCH

我将那些CASE语句改为OR子句，只是因为它们对我来说更易读。如果我记得我的SQL理论，使用OR可能会使这个查询稍微慢一点。

- Thorin

---

有趣...它应该说VALUES而不是SELECT，还是我理解错了？我明天会尝试一下看看是否有帮助。 - BabelFish

通过将这两个语句合并成一个语句，是为了避免锁定吗？那这不会转化为在后端执行两次查询吗？ - BabelFish

我在发布之前创建了一个测试数据库，其中包含上述字段的测试表来测试此代码。它确实有效。这是一个INSERT INTO .. SELECT语句。要查看其工作原理，请尝试仅运行SELECT .. WHERE NOT EXISTS语句，如果该行不存在，则会获取变量值，否则不会获取任何值。这两个语句已经合并为一个语句。使用两个语句时，死锁发生是因为来自不同线程的语句插入到两个语句之间。使用一个语句时，没有其他语句可以插入。 - Thorin

哦，Remus Rusanu的答案在SQL Server 2008中非常有效。我认为MERGE语句是SQL Server 2008中的新功能。如果你还在使用2005版本，我的INSERT INTO .. SELECT .. WHERE NOT EXISTS()将实现你想要的功能。Remus建议将此代码拆分为两个过程，一个用于ByMachineId，另一个用于ByMetrics，这一点是正确的。 - Thorin

Answer 3

摆脱这个交易。它并没有真正帮助你，反而会伤害你。这应该可以解决你的问题。

- Russell McClure

Answer 4

我在想是否在之前的SELECT中添加UPDLOCK提示可以修复这个问题。这应该可以避免某些死锁场景，因为它会防止另一个进程在你即将改变数据时获取读取锁。

- Marc Gravell