主页 / dba / 问题 / 187776
Accepted
i-one
Asked: 2017-10-06 08:11:48 +0800 CST

MERGE死锁预防

  • 772

在我们的一个数据库中，我们有一个由多个线程集中并发访问的表。线程确实通过MERGE. 有时也会有线程删除行，因此表数据非常不稳定。执行 upsert 的线程有时会遇到死锁。该问题看起来类似于此问题中描述的问题。但是，不同之处在于，在我们的例子中，每个线程都确实更新或插入了一行。

简化的设置如下。该表是堆的，上面有两个唯一的非聚集索引

CREATE TABLE [Cache]
(
    [UID] uniqueidentifier NOT NULL CONSTRAINT DF_Cache_UID DEFAULT (newid()),
    [ItemKey] varchar(200) NOT NULL,
    [FileName] nvarchar(255) NOT NULL,
    [Expires] datetime2(2) NOT NULL,
    CONSTRAINT [PK_Cache] PRIMARY KEY NONCLUSTERED ([UID])
)
GO
CREATE UNIQUE INDEX IX_Cache ON [Cache] ([ItemKey]);
GO

典型的查询是

DECLARE
    @itemKey varchar(200) = 'Item_0F3C43A6A6A14255B2EA977EA730EDF2',
    @fileName nvarchar(255) = 'File_0F3C43A6A6A14255B2EA977EA730EDF2.dat';

MERGE INTO [Cache] WITH (HOLDLOCK) T
USING (
    VALUES (@itemKey, @fileName, dateadd(minute, 10, sysdatetime()))
) S(ItemKey, FileName, Expires)
ON T.ItemKey = S.ItemKey
WHEN MATCHED THEN
    UPDATE
    SET
        T.FileName = S.FileName,
        T.Expires = S.Expires
WHEN NOT MATCHED THEN
    INSERT (ItemKey, FileName, Expires)
    VALUES (S.ItemKey, S.FileName, S.Expires)
OUTPUT deleted.FileName;

即，匹配通过唯一索引键发生。HOLDLOCK由于并发性，提示就在这里（如此处所建议的那样）。

我做了小调查，以下是我发现的。

在大多数情况下，查询执行计划是

索引查找执行计划

具有以下锁定模式

索引查找锁定模式

即IX锁定对象，然后是更细粒度的锁定。

然而，有时查询执行计划是不同的

表扫描执行计划

（这个计划形状可以通过添加INDEX(0)提示来强制），它的锁定模式是

表扫描锁定模式

X已放置在对象上的通知锁IX。

由于两个IX兼容，但两个X不兼容，并发下发生的事情是

僵局

死锁图

僵局！

这里出现了问题的第一部分。符合条件后是否X锁定对象IX？不是bug吗？

文档指出：

意向锁之所以被命名为意向锁，是因为它们是在较低级别的锁之前获取的，因此表明意图将锁放置在较低级别。

还有_

IX 意味着打算只更新部分行而不是所有行

所以，在我看来非常可疑X之后将锁锁定在对象上。IX

首先，我尝试通过添加表锁定提示来防止死锁

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCK) T

和

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCKX) T

随着TABLOCK就地锁定模式变为

合并 holdlock tablock 锁定模式

并且TABLOCKX锁定模式是

合并 holdlock tablockx 锁定模式

由于两个SIX（以及两个X）不兼容，这可以有效地防止死锁，但不幸的是，它也可以防止并发（这是不希望的）。

我的下一个尝试是添加PAGLOCK和ROWLOCK使锁更细化并减少争用。两者都没有影响（X在对象上仍然被立即观察到IX）。

FORCESEEK我的最后一次尝试是通过添加提示来强制具有良好粒度锁定的“良好”执行计划形状

MERGE INTO [Cache] WITH (HOLDLOCK, FORCESEEK(IX_Cache(ItemKey))) T

它奏效了。

这里出现了问题的第二部分。会不会发生这种情况FORCESEEK会被忽略并使用错误的锁定模式？（正如我所提到的，PAGLOCK并且ROWLOCK似乎被忽略了）。

添加UPDLOCK没有效果（X在对象仍然可以观察到之后IX）。

IX_Cache正如预期的那样，使索引聚集在一起是有效的。它导致了使用Clustered Index Seek和粒度锁定的计划。此外，我尝试强制显示粒度锁定的聚集索引扫描。

然而。补充观察。在原来的设置FORCESEEK(IX_Cache(ItemKey)))中，如果一个@itemKey变量声明从varchar(200)更改为nvarchar(200)，执行计划变为

使用 nvarchar 索引查找执行计划

看到使用了 seek ，但在这种情况下，锁定模式再次显示X在IX.

因此，似乎强制搜索不一定能保证粒度锁（因此没有死锁）。我不相信聚集索引能保证粒度锁定。或者是吗？

我的理解（如果我错了，请纠正我）是锁定在很大程度上是情境性的，并且某些执行计划形状并不意味着某些锁定模式。

关于仍然打开X后对对象加锁的资格问题。IX如果它符合条件，是否可以采取一些措施来防止对象锁定？

sql-server execution-plan

1 个回答

  1. Best Answer

    放置在对象上是否IX符合X条件？是不是bug？

    它看起来有点奇怪，但它是有效的。在获取锁时IX，其意图很可能是X在较低级别获取锁。没有什么可说的，实际上必须使用这种锁。毕竟，下层可能没有什么可以锁定的；引擎无法提前知道这一点。此外，可能会有一些优化，例如可以跳过较低级别的锁（这里可以看到IS和锁的示例）。S

    更具体地说，对于当前场景，可序列化的键范围锁确实不适用于堆，因此唯一的选择是X对象级别的锁。从这个意义上说，如果访问方法是堆扫描，引擎可能能够及早检测到X不可避免地需要锁，从而避免获取IX锁。

    另一方面，锁定是复杂的，有时可能会出于内部原因而使用意图锁，这与使用较低级别锁的意图不一定相关。采取IX可能是为某些晦涩的边缘情况提供所需保护的侵入性最小的方式。对于类似的考虑，请参阅在 IsolationLevel.ReadUncommitted 上发布的共享锁。

    因此，目前的情况对于您的死锁场景来说是不幸的，原则上它可能是可以避免的，但这并不一定与“错误”相同。如果您需要明确的答案，您可以通过您的正常支持渠道或 Microsoft Connect 报告该问题。

    会不会发生这种情况FORCESEEK会被忽略并使用错误的锁定模式？

    No.FORCESEEK不是一个提示，而是一个指令。如果优化器找不到符合“提示”的计划，它将产生错误。

    强制索引是确保可以采用键范围锁定的一种方式。再加上在处理要更改的行的访问方法时自然采用的更新锁，这提供了足够的保证来避免您的方案中的并发问题。

    如果表的架构没有改变（例如添加新索引），提示也足以避免该查询与自身发生死锁。与其他可能在非聚集索引之前访问堆的查询（例如对非聚集索引的键的更新）仍然存在循环死锁的可能性。

    ...变量声明 from varchar(200)to nvarchar(200)...

    这打破了单行将受到影响的保证，因此引入了 Eager Table Spool 用于万圣节保护。作为对此的进一步解决方法，使用 . 明确保证MERGE TOP (1) INTO [Cache]...。

    我的理解 [...] 是锁定在很大程度上是情境性的，并且某些执行计划形状并不意味着某些锁定模式。

    在执行计划中肯定还有更多可见的内容。您可以使用例如计划指南强制某个计划形状，但引擎仍可能决定在运行时采用不同的锁定。TOP (1)如果您合并上述元素，机会相当低。

    一般说明

    以这种方式使用堆表有点不寻常。您应该考虑将其转换为聚集表的优点，也许使用评论中建议的索引 Dan Guzman：

    CREATE UNIQUE CLUSTERED INDEX IX_Cache ON [Cache] ([ItemKey]);

    这可能具有重要的空间重用优势，并为当前的死锁问题提供了一个很好的解决方法。

    MERGE在高并发环境中看到也有点不寻常。INSERT有点违反直觉，执行单独的and语句通常更有效UPDATE，例如：

    DECLARE
    @itemKey varchar(200) = 'Item_0F3C43A6A6A14255B2EA977EA730EDF2',
    @fileName nvarchar(255) = 'File_0F3C43A6A6A14255B2EA977EA730EDF2.dat';

BEGIN TRANSACTION;

    DECLARE @expires datetime2(2) = DATEADD(MINUTE, 10, SYSDATETIME());

    UPDATE TOP (1) dbo.Cache WITH (SERIALIZABLE, UPDLOCK)
    SET [FileName] = @fileName,
        Expires = @expires
    OUTPUT Deleted.[FileName]
    WHERE
        ItemKey = @itemKey;

    IF @@ROWCOUNT = 0
        INSERT dbo.Cache
            (ItemKey, [FileName], Expires)
        VALUES
            (@itemKey, @fileName, @expires);

COMMIT TRANSACTION;

    请注意不再需要 RID 查找：

    执行计划

    如果您可以保证存在唯一索引（如问题中所示） ，则可以删除中ItemKey的冗余，从而提供更简单的计划：TOP (1)UPDATE

    简化更新

    INSERT在任何一种情况下，和UPDATE计划都有资格获得微不足道的计划。MERGE总是需要完全基于成本的优化。

    请参阅相关的问答SQL Server 2014 并发输入问题以了解要使用的正确模式，以及有关MERGE.

    死锁不能总是被阻止。通过仔细的编码和设计，它们可以减少到最低限度，但应用程序应该始终准备好优雅地处理奇怪的死锁（例如重新检查条件然后重试）。

    如果您可以完全控制访问相关对象的进程，您还可以考虑使用应用程序锁来序列化对单个元素的访问，如SQL Server 并发插入和删除中所述。

    • 14

相关问题