Resolving deadlock from 2 tables only related through indexed view

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 122

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:

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

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

StackExchange Database Administrators Q#69699, answer score: 6