15

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.

  • read committed doesn't take key-range locks, only serializable does. If the deadlock does in fact show read committed (2) then my gues is you're accessing to changing a foreign key which will be turned into serializable under the covers (though still say read committed). We'd honestly need the entire ddl and sp's to help further. – Sean Gallardy Jul 4 '14 at 18:11
  • @SeanGallardy, thanks. I've edited to include the 1222 output in case i was interpreting wrong, and I've added more details on what the SPs are doing. Does this help? – Leland Richardson Jul 4 '14 at 18:56
  • 2
    @SeanGallardy The part of the query plan that maintains the indexed view runs internally at SERIALIZABLE (there's a bit more to it than that, but this is a comment not an answer :) – Paul White Jul 4 '14 at 19:23
  • @PaulWhite Thank you for the insight, I did not know that! Doing a quick test, I can definitely get the serializable locking modes with the indexed view during the insert in your stored procedures(RangeI-N, RangeS-S, RangeS-U). It seems to be like the deadlock is happening from the incompatible lock modes hitting at the right time against each other during the inserts in your stored procedures when they fall inside of the lock boundaries (for example in the area held by the range lock). Both a timing and an input data collision I would think. – Sean Gallardy Jul 5 '14 at 15:11
  • Question: If I added a HOLDLOCK hint on the SELECT statements, would that prevent the lock from happening on insert? – Leland Richardson Jul 7 '14 at 17:45
4
+50

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.

  • Thanks for the help. I'm going to read up a bit more on option #2 and see if that works for us. The view is read from quite a bit, and the clustered index is a pretty big help... so I'd rather not remove it yet. I'll come back an update once I give this a shot. – Leland Richardson Jul 9 '14 at 17:21
  • I think using sp_getapplock will work. I haven't been able to try it in our production environment yet, but I wanted to make sure you got awarded the bounty before it expired. I'll update here when I can confirm it works! – Leland Richardson Jul 12 '14 at 17:20
  • Thanks. One nice thing about Application Locks is that you can change the level of granularity of concatenating in something like the member_id into the @Resource value. That doesn't seem to apply to this particular situation but I have seen it used like that and it is quite handy, especially in a multi-tenant system where you want to confine the process to a single thread on a per customer basis, but still have it be multithreaded across customers. – Solomon Rutzky Jul 12 '14 at 21:19
  • I wanted to give an update and say that this did end up working in our production environment. :) – Leland Richardson Jul 17 '14 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.