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 theIX_follow_member_id_includes
index while SP2 holds a conflicting (X) lockSP2 is waiting for an
S
mode lock onPK_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 amember
tablemember_activity.activity_id
is a foreign key to theactivity
tablefollow.member_id
is a foreign key to amember
tablefollow.follower_id
is a foreign key to amember
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.
SERIALIZABLE
(there's a bit more to it than that, but this is a comment not an answer :)