Resolving deadlock from 2 tables only related through indexed view

Question

I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it.

This is on a production environment running SQL Server 2008 R2.

To give you a slightly simplified view of the situation:

---

I have 3 tables as defined below:

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,
    ...
)

The member_activity table has a compound Primary Key defined as member_id, activity_id, because I only ever need to look up data on that table that way.

I also have a nonclustered index on follow:

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

Additionally, I have a Schema-bound view network_activity which is defined as follows:

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

Which also has a unique clustered index:

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

---

Now, I have two deadlocked stored procedures. They go through the following process:

-- 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 (...)

These 2 procedures both run in READ COMMITTED isolation. I've managed to query the 1222 extended events output, and have interpreted the following with regard to the deadlocks:

SP1 is waiting for a RangeS-S key lock on the IX_follow_member_id_includes index while SP2 holds a conflicting (X) lock

SP2 is waiting for an S mode lock on PK_member_activity while SP1 holds a conflicting (X) lock

The deadlock appears to be happening on the last line of each query (the inserts). What is unclear to me is why SP1 is wanting a lock on the IX_follow-member_id_includes index. The only link, to me, seems to be from this indexed view which is why I've included it.

What would be the best way for me to prevent these deadlocks from happening? Any help would be much appreciated. I don't have much experience in resolving deadlock issues.

Please let me know if there is any more information I can provide that might help!

Thanks in advance.

---

Edit 1: Adding some more information per request.

Here is the 1222 output from this deadlock:

<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>

In this case,

associatedObjectId 72057594098679808 corresponds to member_activity, PK_member_activity

associatedObjectId 72057594104905728 corresponds to follow, IX_follow_member_id_includes

Also, here is a more precise picture into what SP1 and SP2 are doing

-- 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)

also 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)

---

Edit 2: After rereading the comments, I thought I'd add some info on what columns are foreign keys as well...

• member_activity.member_id is a foreign key to a member table
• member_activity.activity_id is a foreign key to the activity table
• follow.member_id is a foreign key to a member table
• follow.follower_id is a foreign key to a member table

---

Update 1:

I made a couple of changes that I thought might help prevent the deadlock, with no luck.

The changes I made were as follows:

-- 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)
;

and with 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

With these two changes, I still seem to be getting deadlocks.

If there is anything else I can provide, please let me know. Thanks.

Answer 1

The conflict boils down to network_activity being an Indexed View which needs to be maintained (internally) across the DML statements. That is most likely why SP1 is wanting a lock on the IX_follow-member_id_includes index as it is probably used by the View (it looks to be a covering index for the View).

Two possible options:

1. Consider dropping the Clustered Index on the View so that it is no longer an Indexed View. Does the benefit of having it outweigh the maintenance cost? Do you select from it frequently enough or is the performance gain of having it indexed worth it? If you run these procs rather frequently, then maybe the cost is higher than the benefit?

2. If the benefit of having the View be indexed does outweigh the cost, then consider isolating DML operations against the base tables of that View. This can be done through the use of Application Locks (see sp_getapplock and sp_releaseapplock). Application Locks let you create locks around arbitrary concepts. Meaning, you can define the @Resource as "network_activity" in both of your Stored Procs which will force them to wait their turn. Each proc would follow the same structure:

   BEGIN TRANSACTION;
   EXEC sp_getapplock @Resource = 'network_activity', @LockMode = 'Exclusive';
   ...current proc code...
   EXEC sp_releaseapplock @Resource = 'network_activity';
   COMMIT TRANSACTION;
   

   You need to manage errors / ROLLBACK yourself (as stated in the linked MSDN documentation) so put in the usual TRY...CATCH. But, this does allow you to manage the situation.
   Please note: sp_getapplock / sp_releaseapplock should be used sparingly; Application Locks can definitely be very handy (such as in cases like this one) but they should only be used when absolutely necessary.