解决仅通过索引视图相关的两个表之间的死锁问题

Question

我遇到了一个死锁的情况，我认为我已经找到了罪魁祸首，但是我不太确定该怎么解决它。 这是在一个运行SQL Server 2008 R2的生产环境上。 为了给你一个稍微简化的情况描述：

---

我有3个如下定义的表格：

TABLE activity (
    id, -- PK
    ...
)

TABLE member_activity (
    member_id, -- PK col 1
    activity_id, -- PK col 2
    ...
)

TABLE follow (
    id, -- PK
    follower_id,
    member_id,
    ...
)

member_activity表具有一个由member_id, activity_id定义的复合主键，因为我只需要以这种方式查找该表上的数据。 我还在follow上创建了一个非聚集索引：

CREATE NONCLUSTERED INDEX [IX_follow_member_id_includes] 
ON follow ( member_id ASC ) INCLUDE ( follower_id )

此外，我还有一个基于模式绑定的视图 network_activity，其定义如下：

CREATE VIEW network_activity
WITH SCHEMABINDING
AS

SELECT
    follow.follower_id as member_id,
    member_activity.activity_id as activity_id,
    COUNT_BIG(*) AS cb
FROM member_activity
INNER JOIN follow ON follow.member_id = member_activity.member_id
INNER JOIN activity ON activity.id = member_activity.activity_id
GROUP BY follow.follower_id, member_activity.activity_id

也具有一个唯一的聚集索引：

CREATE UNIQUE CLUSTERED INDEX [IX_network_activity_unique_member_id_activity_id] 
ON network_activity
(
    member_id ASC,
    activity_id ASC
)

---

现在，我有两个死锁的存储过程。它们经历以下过程：

-- SP1: insert activity
-----------------------
INSERT INTO activity (...)
SELECT ... FROM member_activity WHERE member_id = @a AND activity_id = @b
INSERT INTO member_activity (...)


-- SP2: insert follow
---------------------
SELECT follow WHERE member_id = @x AND follower_id = @y
INSERT INTO follow (...)

这两个过程都在READ COMMITTED隔离级别下运行。我已经成功查询了1222扩展事件输出，并根据死锁的情况进行了解释: SP1正在等待对"IX_follow_member_id_includes"索引上的"RangeS-S"键锁，而SP2持有一个冲突的(X)锁。 SP2正在等待"PK_member_activity"上的"S"模式锁，而SP1持有一个冲突的(X)锁。 死锁似乎发生在每个查询的最后一行（插入操作）。但对我来说不清楚的是，为什么SP1要在"IX_follow-member_id_includes"索引上获取锁。唯一的联系似乎只能从这个索引视图上得到，这也是为什么我将其包含在内的原因。 请问有什么方法可以防止这些死锁的发生吗？非常感谢您的帮助。我在解决死锁问题方面没有太多经验。 如果还需要提供更多信息，请告诉我。 提前谢谢！

---

编辑1：根据要求添加一些更多的信息。 这是来自死锁的1222输出。

<deadlock>
    <victim-list>
        <victimProcess id="process4c6672748" />
    </victim-list>
    <process-list>
        <process id="process4c6672748" taskpriority="0" logused="332" waitresource="KEY: 8:72057594104905728 (25014f77eaba)" waittime="581" ownerId="474698706" transactionname="INSERT" lasttranstarted="2014-07-03T17:03:12.287" XDES="0x298487970" lockMode="RangeS-S" schedulerid="1" kpid="972" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-03T17:03:12.283" lastbatchcompleted="2014-07-03T17:03:12.283" lastattention="2014-07-03T10:25:00.283" clientapp=".Net SqlClient Data Provider" hostname="WIN08CLYDESDALE" hostpid="4596" loginname="TechPro" isolationlevel="read committed (2)" xactid="474698706" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="" line="7" stmtstart="1194" stmtend="1434" sqlhandle="0x02000000a26bb72a2b220406876cad09c22242e5265c82e6" />
                <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf> <!-- SP 1 --> </inputbuf>
        </process>
        <process id="process6cddc5b88" taskpriority="0" logused="456" waitresource="KEY: 8:72057594098679808 (89013169fc76)" waittime="567" ownerId="474698698" transactionname="INSERT" lasttranstarted="2014-07-03T17:03:12.283" XDES="0x30c459970" lockMode="S" schedulerid="4" kpid="4204" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-03T17:03:12.283" lastbatchcompleted="2014-07-03T17:03:12.283" lastattention="2014-07-03T15:04:55.870" clientapp=".Net SqlClient Data Provider" hostname="WIN08CLYDESDALE" hostpid="4596" loginname="TechPro" isolationlevel="read committed (2)" xactid="474698698" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="" line="18" stmtstart="942" stmtend="1250" sqlhandle="0x03000800ca458d315ee9130100a300000100000000000000" />
            </executionStack>
            <inputbuf> <!-- SP 2 --> </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594104905728" dbid="8" objectname="" indexname="" id="lock33299fc00" mode="X" associatedObjectId="72057594104905728">
            <owner-list>
                <owner id="process6cddc5b88" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process4c6672748" mode="RangeS-S" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594098679808" dbid="8" objectname="" indexname="" id="lockb7e2ba80" mode="X" associatedObjectId="72057594098679808">
            <owner-list>
                <owner id="process4c6672748" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process6cddc5b88" mode="S" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

在这种情况下， associatedObjectId 72057594098679808 对应于 member_activity, PK_member_activity associatedObjectId 72057594104905728 对应于 follow, IX_follow_member_id_includes 此外，这里有一个更具体的描述，说明了SP1和SP2所做的事情。

-- SP1: insert activity
-----------------------
DECLARE @activityId INT

INSERT INTO activity (field1, field2)
VALUES (@field1, @field2)

SET @activityId = SCOPE_IDENTITY();

IF NOT EXISTS(
    SELECT TOP 1 member_id 
    FROM member_activity 
    WHERE member_id = @m1 AND activity_id = @activityId
)
    INSERT INTO member_activity (member_id, activity_id, field1)
    VALUES (@m1, @activityId, @field1)

IF NOT EXISTS(
    SELECT TOP 1 member_id 
    FROM member_activity 
    WHERE member_id = @m2 AND activity_id = @activityId
)
    INSERT INTO member_activity (member_id, activity_id, field1)
    VALUES (@m2, @activityId, @field1)

也包括SP2：

-- SP2: insert follow
---------------------

IF NOT EXISTS(
    SELECT TOP 1 1 
    FROM follow
    WHERE member_id = @memberId AND follower_id = @followerId
)
    INSERT INTO follow (member_id, follower_id)
    VALUES (@memberId, @followerId)

---

编辑2：在重新阅读评论后，我想补充一些关于哪些列是外键的信息...

• member_activity.member_id 是指向一个 member 表的外键
• member_activity.activity_id 是指向一个 activity 表的外键
• follow.member_id 是指向一个 member 表的外键
• follow.follower_id 是指向一个 member 表的外键

---

更新1： 我做了一些改动，希望能够避免死锁的发生，但是没有成功。 我所做的改动如下：

-- SP1: insert activity
-----------------------
DECLARE @activityId INT

INSERT INTO activity (field1, field2)
VALUES (@field1, @field2)

SET @activityId = SCOPE_IDENTITY();

MERGE member_activity WITH ( HOLDLOCK ) as target
USING (SELECT @m1 as member_id, @activityId as activity_id, @field1 as field1) as source
    ON target.member_id = source.member_id
    AND target.activity_id = source.activity_id
WHEN NOT MATCHED THEN
    INSERT (member_id, activity_id, field1)
    VALUES (source.member_id, source.activity_id, source.field1)
;

MERGE member_activity WITH ( HOLDLOCK ) as target
USING (SELECT @m2 as member_id, @activityId as activity_id, @field1 as field1) as source
    ON target.member_id = source.member_id
    AND target.activity_id = source.activity_id
WHEN NOT MATCHED THEN
    INSERT (member_id, activity_id, field1)
    VALUES (source.member_id, source.activity_id, source.field1)
;

而且还有SP2版本：

-- SP2: insert follow
---------------------

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

IF NOT EXISTS(
    SELECT TOP 1 1 
    FROM follow WITH ( UPDLOCK )
    WHERE member_id = @memberId AND follower_id = @followerId
)
    INSERT INTO follow (member_id, follower_id)
    VALUES (@memberId, @followerId)

COMMIT

经过这两个更改，我似乎仍然遇到了死锁问题。 如果还有其他需要提供的信息，请告诉我。谢谢。

- Leland Richardson

---

读提交不会使用键范围锁，只有可串行化会这样做。如果死锁确实显示为读提交（2），那么我猜测你正在访问或更改一个外键，该操作将在底层转换为可串行化（尽管仍然显示为读提交）。我们需要完整的DDL和存储过程才能提供进一步帮助。 - Sean Gallardy

@SeanGallardy，谢谢。我已经编辑了内容，以防我解释错误，并且我还添加了更多关于存储过程正在做什么的细节。这样有帮助吗？ - Leland Richardson

@SeanGallardy 查询计划中维护索引视图的部分在内部以SERIALIZABLE运行（除此之外还有更多内容，但这只是一条评论，不是答案 :) - Paul White

@PaulWhite 谢谢你的见解，我之前不知道这个！经过快速测试，我确实可以在你的存储过程中的插入操作中使用可序列化的锁定模式来获取索引视图（RangeI-N，RangeS-S，RangeS-U）。看起来死锁是由于在插入操作中的不兼容的锁定模式在正确的时间相互冲突，当它们落在锁定边界内时发生的（例如在范围锁定所持有的区域内）。我认为这既是一个时间上的冲突，也是一个输入数据的冲突。 - Sean Gallardy

问题：如果我在SELECT语句中添加了HOLDLOCK提示，那么是否可以防止插入时发生锁定？ - Leland Richardson

Answer 1

冲突的关键在于network_activity是一个需要在DML语句中进行维护（内部）的索引视图。这很可能是为什么SP1想要对IX_follow-member_id_includes索引进行锁定，因为它很可能被该视图使用（它看起来是该视图的覆盖索引）。 有两个可能的选择： 考虑取消视图上的聚集索引，使其不再成为索引视图。它的好处是否超过了维护成本？您是否经常从中进行选择，或者索引带来的性能提升是否值得？如果您经常运行这些存储过程，那么可能成本高于收益？ 如果视图的索引化带来的好处确实超过了成本，请考虑隔离对该视图的基表进行的DML操作。可以通过使用应用程序锁（参见sp_getapplock和sp_releaseapplock）来实现。应用程序锁允许您在任意概念周围创建锁定。也就是说，您可以在两个存储过程中将@Resource定义为"network_activity"，这将强制它们等待执行。每个存储过程都应遵循相同的结构： BEGIN TRANSACTION; EXEC sp_getapplock @Resource = 'network_activity', @LockMode = 'Exclusive'; ...当前存储过程代码... EXEC sp_releaseapplock @Resource = 'network_activity'; COMMIT TRANSACTION; 您需要自己管理错误/回滚（如链接的MSDN文档中所述），因此请放入通常的TRY...CATCH。但是，这确实允许您管理情况。 请注意：应该谨慎使用sp_getapplock / sp_releaseapplock；应用程序锁定确实非常方便（例如在这种情况下），但只有在绝对必要时才应使用。

- Solomon Rutzky

---

谢谢帮忙。我会再多了解一下第二个选项，看看它是否适合我们。这个视图被频繁读取，聚集索引对此非常有帮助...所以我还不想移除它。我试过之后会回来更新的。 - Leland Richardson

我认为使用sp_getapplock会起作用。我还没有在我们的生产环境中尝试过，但是我想确保你在奖励到期之前得到了奖金。我会在确认它起作用后在这里更新！ - Leland Richardson

谢谢。应用程序锁的一个好处是，您可以更改将member_id等内容连接到@Resource值中的粒度级别。这似乎不适用于这种特定情况，但我曾见过它被用在那样的方式上，而且非常方便，尤其是在多租户系统中，您希望将进程限制在每个客户的单个线程上，但仍然要跨客户进行多线程处理。 - Solomon Rutzky

我想给大家一个最新的消息，这个问题在我们的生产环境中确实解决了。 :) - Leland Richardson