· 8 years ago · Mar 15, 2018, 02:16 AM
1TABLE activity (
2 id, -- PK
3 ...
4)
5
6TABLE member_activity (
7 member_id, -- PK col 1
8 activity_id, -- PK col 2
9 ...
10)
11
12TABLE follow (
13 id, -- PK
14 follower_id,
15 member_id,
16 ...
17)
18
19CREATE NONCLUSTERED INDEX [IX_follow_member_id_includes]
20ON follow ( member_id ASC ) INCLUDE ( follower_id )
21
22CREATE VIEW network_activity
23WITH SCHEMABINDING
24AS
25
26SELECT
27 follow.follower_id as member_id,
28 member_activity.activity_id as activity_id,
29 COUNT_BIG(*) AS cb
30FROM member_activity
31INNER JOIN follow ON follow.member_id = member_activity.member_id
32INNER JOIN activity ON activity.id = member_activity.activity_id
33GROUP BY follow.follower_id, member_activity.activity_id
34
35CREATE UNIQUE CLUSTERED INDEX [IX_network_activity_unique_member_id_activity_id]
36ON network_activity
37(
38 member_id ASC,
39 activity_id ASC
40)
41
42-- SP1: insert activity
43-----------------------
44INSERT INTO activity (...)
45SELECT ... FROM member_activity WHERE member_id = @a AND activity_id = @b
46INSERT INTO member_activity (...)
47
48
49-- SP2: insert follow
50---------------------
51SELECT follow WHERE member_id = @x AND follower_id = @y
52INSERT INTO follow (...)
53
54<deadlock>
55 <victim-list>
56 <victimProcess id="process4c6672748" />
57 </victim-list>
58 <process-list>
59 <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">
60 <executionStack>
61 <frame procname="" line="7" stmtstart="1194" stmtend="1434" sqlhandle="0x02000000a26bb72a2b220406876cad09c22242e5265c82e6" />
62 <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
63 </executionStack>
64 <inputbuf> <!-- SP 1 --> </inputbuf>
65 </process>
66 <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">
67 <executionStack>
68 <frame procname="" line="18" stmtstart="942" stmtend="1250" sqlhandle="0x03000800ca458d315ee9130100a300000100000000000000" />
69 </executionStack>
70 <inputbuf> <!-- SP 2 --> </inputbuf>
71 </process>
72 </process-list>
73 <resource-list>
74 <keylock hobtid="72057594104905728" dbid="8" objectname="" indexname="" id="lock33299fc00" mode="X" associatedObjectId="72057594104905728">
75 <owner-list>
76 <owner id="process6cddc5b88" mode="X" />
77 </owner-list>
78 <waiter-list>
79 <waiter id="process4c6672748" mode="RangeS-S" requestType="wait" />
80 </waiter-list>
81 </keylock>
82 <keylock hobtid="72057594098679808" dbid="8" objectname="" indexname="" id="lockb7e2ba80" mode="X" associatedObjectId="72057594098679808">
83 <owner-list>
84 <owner id="process4c6672748" mode="X" />
85 </owner-list>
86 <waiter-list>
87 <waiter id="process6cddc5b88" mode="S" requestType="wait" />
88 </waiter-list>
89 </keylock>
90 </resource-list>
91</deadlock>
92
93-- SP1: insert activity
94-----------------------
95DECLARE @activityId INT
96
97INSERT INTO activity (field1, field2)
98VALUES (@field1, @field2)
99
100SET @activityId = SCOPE_IDENTITY();
101
102IF NOT EXISTS(
103 SELECT TOP 1 member_id
104 FROM member_activity
105 WHERE member_id = @m1 AND activity_id = @activityId
106)
107 INSERT INTO member_activity (member_id, activity_id, field1)
108 VALUES (@m1, @activityId, @field1)
109
110IF NOT EXISTS(
111 SELECT TOP 1 member_id
112 FROM member_activity
113 WHERE member_id = @m2 AND activity_id = @activityId
114)
115 INSERT INTO member_activity (member_id, activity_id, field1)
116 VALUES (@m2, @activityId, @field1)
117
118-- SP2: insert follow
119---------------------
120
121IF NOT EXISTS(
122 SELECT TOP 1 1
123 FROM follow
124 WHERE member_id = @memberId AND follower_id = @followerId
125)
126 INSERT INTO follow (member_id, follower_id)
127 VALUES (@memberId, @followerId)
128
129-- SP1: insert activity
130-----------------------
131DECLARE @activityId INT
132
133INSERT INTO activity (field1, field2)
134VALUES (@field1, @field2)
135
136SET @activityId = SCOPE_IDENTITY();
137
138MERGE member_activity WITH ( HOLDLOCK ) as target
139USING (SELECT @m1 as member_id, @activityId as activity_id, @field1 as field1) as source
140 ON target.member_id = source.member_id
141 AND target.activity_id = source.activity_id
142WHEN NOT MATCHED THEN
143 INSERT (member_id, activity_id, field1)
144 VALUES (source.member_id, source.activity_id, source.field1)
145;
146
147MERGE member_activity WITH ( HOLDLOCK ) as target
148USING (SELECT @m2 as member_id, @activityId as activity_id, @field1 as field1) as source
149 ON target.member_id = source.member_id
150 AND target.activity_id = source.activity_id
151WHEN NOT MATCHED THEN
152 INSERT (member_id, activity_id, field1)
153 VALUES (source.member_id, source.activity_id, source.field1)
154;
155
156-- SP2: insert follow
157---------------------
158
159SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
160BEGIN TRANSACTION
161
162IF NOT EXISTS(
163 SELECT TOP 1 1
164 FROM follow WITH ( UPDLOCK )
165 WHERE member_id = @memberId AND follower_id = @followerId
166)
167 INSERT INTO follow (member_id, follower_id)
168 VALUES (@memberId, @followerId)
169
170COMMIT
171
172BEGIN TRANSACTION;
173EXEC sp_getapplock @Resource = 'network_activity', @LockMode = 'Exclusive';
174...current proc code...
175EXEC sp_releaseapplock @Resource = 'network_activity';
176COMMIT TRANSACTION;