主页 / dba / 问题 / 23467
Accepted
Sako73
Asked: 2012-08-31 11:49:54 +0800 CST

合并语句本身死锁

  • 772

我有以下过程（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, MyKey, MyValue) values
        (@companyId, @userId, newData.MyKey, newData.MyValue);

end;

CompanyId、UserId、MyKey 构成目标表的复合键。CompanyId 是父表的外键。此外，还有一个非聚集索引CompanyId asc, UserId asc。

它是从许多不同的线程调用的，并且我一直在调用同一语句的不同进程之间遇到死锁。我的理解是“with (holdlock)”对于防止插入/更新竞争条件错误是必要的。

我假设两个不同的线程在验证约束时以不同的顺序锁定行（或页面），因此是死锁。

这是一个正确的假设吗？

解决这种情况的最佳方法是什么（即没有死锁，对多线程性能的影响最小）？

查询计划图像 （如果您在新选项卡中查看图像，它是可读的。对不起，小尺寸。）

  • @datatable 中最多有 28 行。
  • 我已经追溯了代码，我看不到我们在这里开始交易的任何地方。
  • 外键设置为仅在删除时级联，并且没有从父表中删除。
sql-server sql-server-2008-r2

4 个回答

  1. 如果表变量只保存一个值，就不会有问题。对于多行，出现死锁的新可能性。假设两个并发进程 (A & B) 使用包含同一公司的 (1, 2) 和 (2, 1) 的表变量运行。

    进程 A 读取目标，没有找到任何行，并插入值“1”。它持有值“1”的排他行锁。进程 B 读取目标，没有找到任何行，并插入值“2”。它持有值“2”的排他行锁。

    现在进程 A 需要处理第 2 行，进程 B 需要处理第 1 行。这两个进程都无法取得进展，因为它需要一个与另一个进程持有的独占锁不兼容的锁。

    为了避免多行死锁，每次都需要以相同的顺序处理行（和访问的表） 。问题中显示的执行计划中的表变量是一个堆，因此行没有内在顺序（它们很可能按插入顺序读取，尽管这不能保证）：

    现有计划

    缺乏一致的行处理顺序直接导致死锁机会。第二个考虑因素是缺少密钥唯一性保证意味着必须使用 Table Spool 才能提供正确的万圣节保护。假脱机是一个急切的假脱机，这意味着所有行都被写入tempdb工作表，然后再被读回并为 Insert 运算符重放。

    重新定义TYPEtable 变量以包含 clustered PRIMARY KEY：

    DROP TYPE dbo.CoUserData;

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL PRIMARY KEY CLUSTERED,
    MyValue integer NOT NULL
);

    执行计划现在显示了对聚集索引的扫描，并且唯一性保证意味着优化器能够安全地删除 Table Spool：

    带主键

    MERGE在128 个线程上对语句进行 5000 次迭代的测试中，聚簇表变量没有发生死锁。我要强调的是，这只是基于观察；聚集表变量也可以（从技术上讲）以各种顺序生成其行，但是顺序一致的机会大大增加。当然，每个新的累积更新、Service Pack 或新版本的 SQL Server 都需要重新测试观察到的行为。

    如果无法更改表变量定义，还有另一种选择：

    MERGE dbo.CompanyUser AS R
USING 
    (SELECT DISTINCT MyKey, MyValue FROM @DataTable) AS NewData ON
    R.CompanyId = @CompanyID
    AND R.UserID = @UserID
    AND R.MyKey = NewData.MyKey
WHEN NOT MATCHED THEN 
    INSERT 
        (CompanyID, UserID, MyKey, MyValue) 
    VALUES
        (@CompanyID, @UserID, NewData.MyKey, NewData.MyValue)
OPTION (ORDER GROUP);

    这也以引入显式排序为代价实现了假脱机（和行顺序一致性）的消除：

    排序计划

    该计划使用相同的测试也没有产生死锁。复制脚本如下：

    CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL /* PRIMARY KEY */,
    MyValue integer NOT NULL
);
GO
CREATE TABLE dbo.Company
(
    CompanyID   integer NOT NULL

    CONSTRAINT PK_Company
        PRIMARY KEY (CompanyID)
);
GO
CREATE TABLE dbo.CompanyUser
(
    CompanyID   integer NOT NULL,
    UserID      integer NOT NULL,
    MyKey       integer NOT NULL,
    MyValue     integer NOT NULL

    CONSTRAINT PK_CompanyUser
        PRIMARY KEY CLUSTERED
            (CompanyID, UserID, MyKey),

    FOREIGN KEY (CompanyID)
        REFERENCES dbo.Company (CompanyID),
);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.CompanyUser (CompanyID, UserID);
GO
INSERT dbo.Company (CompanyID) VALUES (1);
GO
DECLARE 
    @DataTable AS dbo.CoUserData,
    @CompanyID integer = 1,
    @UserID integer = 1;

INSERT @DataTable
SELECT TOP (10)
    V.MyKey,
    V.MyValue
FROM
(
    VALUES
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, 5),
        (6, 6),
        (7, 7),
        (8, 8),
        (9, 9)
) AS V (MyKey, MyValue)
ORDER BY NEWID();

BEGIN TRANSACTION;

    -- Test MERGE statement here

ROLLBACK TRANSACTION;
    • 35
  2. Best Answer

    好的，在查看了几次之后，我认为您的基本假设是正确的。这里可能发生的是：

    1. MERGE 的 MATCH 部分检查索引是否匹配，并在运行时对这些行/页进行读锁定。

    2. 当它有一行没有匹配时，它会首先尝试插入新的索引行，所以它会请求一个行/页写锁......

    但是，如果另一个用户也在同一行/页面上执行了第 1 步，那么第一个用户将被阻止更新，并且...

    如果第二个用户也需要在同一页面上插入，那么他们就陷入了僵局。

    AFAIK，只有一种（简单）方法可以 100% 确保您不会在此过程中陷入死锁，那就是向 MERGE 添加 TABLOCKX 提示，但这可能会对性能产生非常糟糕的影响。

    添加TABLOCK提示可能足以解决问题，而不会对您的性能产​​生很大影响。

    最后，您还可以尝试添加 PAGLOCK、XLOCK 或同时添加 PAGLOCK 和 XLOCK。同样，这可能会起作用，性能可能不会太糟糕。你得试一试才能看到。

    • 12

  3. 我认为 SQL_Kiwi 提供了很好的分析。如果你需要解决数据库中的问题，你应该听从他的建议。当然，每次升级、应用服务包或添加/更改索引或索引视图时，您都需要重新测试它是否仍然适用。

    还有其他三种选择：

    1. 您可以序列化您的插入，以使它们不会发生冲突：您可以在事务开始时调用 sp_getapplock 并在执行 MERGE 之前获取排他锁。当然，您仍然需要对其进行压力测试。

    2. 您可以让一个线程处理所有插入，以便您的应用服务器处理并发。

    3. 您可以在死锁后自动重试 - 如果并发性很高，这可能是最慢的方法。

    无论哪种方式，只有您可以确定您的解决方案对性能的影响。

    通常情况下，我们的系统中根本没有死锁，尽管我们确实有很大的可能出现死锁。2011 年，我们在一次部署中犯了一个错误，在几个小时内发生了六次死锁，所有这些都遵循相同的场景。我很快就解决了这个问题，这就是这一年的所有僵局。

    我们在系统中主要使用方法 1。它对我们非常有效。

    • 8

  4. 另一种可能的方法 - 我发现 Merge 有时会出现锁定和性能问题 - 可能值得使用 Option (MaxDop x) 查询选项

    在昏暗而遥远的过去，SQL Server 有一个插入行级别锁定选项 - 但这似乎已经死了，但是具有标识的集群 PK 应该使插入运行干净。

    • -1

相关问题