当前位置: 首页>>技术教程>>正文


合并语句本身就是死锁

webfans 技术教程 , , , , 去评论

问题描述

我有以下过程(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上有non-clustered索引。

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

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

这是正确的假设吗?

解决这种情况的最佳方法是什么(即没有死锁,对multi-threaded性能的影响最小)?

(如果您在新标签中查看图像,它是可读的。对不起,小尺寸。)

sql-server,sql-server-2008-r2,deadlock,merge,database

  • @datatable中最多有28行。

  • 我已经追溯了代码,我无法在任何地方看到我们在这里开始交易。

  • 外键设置为仅在删除时级联,并且父表中没有删除。

最佳解决方法

好的,看了几次之后,我认为你的基本假设是正确的。这里可能发生的是:

  1. MERGE的MATCH部分检查匹配的索引,read-locking检查那些行/页面。

  2. 如果它没有匹配的行,它将首先尝试插入新的索引行,以便它将请求行/页面write-lock …

但是如果另一个用户也在同一行/页面上进行了第1步,则第一个用户将被阻止更新,并且……

如果第二个用户也需要在同一页面上插入,那么它们就处于死锁状态。

AFAIK,只有一种(简单)方法可以100%确定你不能通过这个程序获得死锁,那就是向MERGE添加一个TABLOCKX提示,但这可能会对性能造成很大影响。

相反,添加TABLOCK提示可能足以解决问题,而不会对性能产生很大影响。

最后,您还可以尝试添加PAGLOCK,XLOCK或PAGLOCK和XLOCK。这可能会起作用,性能可能不会太糟糕。你必须尝试看看。

次佳解决方法

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

进程A读取目标,找不到行,并插入值’1’。它在值’1’上保持独占行锁定。进程B读取目标,找不到行,并插入值’2’。它在值’2’上拥有独占行锁定。

现在进程A需要处理第2行,进程B需要处理第1行。这两个进程都不能进行,因为它需要一个与其他进程持有的独占锁不兼容的锁。

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

sql-server,sql-server-2008-r2,deadlock,merge,database

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

重新定义表变量的TYPE以包含群集PRIMARY KEY

DROP TYPE dbo.CoUserData;

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

执行计划现在显示聚簇索引的扫描,唯一性保证意味着优化器能够安全地删除表盘符:

sql-server,sql-server-2008-r2,deadlock,merge,database

在128个线程上对MERGE语句进行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);

这也消除了线轴(和row-order的一致性),代价是引入了明确的排序:

sql-server,sql-server-2008-r2,deadlock,merge,database

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

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;

第三种解决方法

我认为SQL_Kiwi提供了非常好的分析。如果您需要解决数据库中的问题,您应该遵循他的建议。当然,每次升级,应用Service Pack或添加/更改索引或索引视图时,您都需要重新测试它是否仍然适用于您。

还有其他三种选择:

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

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

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

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

通常情况下,我们的系统中根本没有死锁,尽管我们确实有很多潜力可以使用它们。在2011年,我们在一次部署中犯了一个错误,并且在几个小时内发生了六次死锁,所有这些都遵循相同的情况。我很快就修好了,这就是今年的所有死锁。

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

参考资料

本文由朵颐IT整理自网络, 文章地址: https://duoyit.com/article/3051.html,转载请务必附带本地址声明。