MS SQL Server-频繁的死锁问题

Question

目标数据库：MS SQL Server 2012

需求：在表格中维护用户工作负载的摘要。以下是用于维护此摘要的表格结构。表正在根据分配给用户的工作项的状态的修改进行更新。

• PARTICIPANT_ID varchar NO PK，唯一非聚集索引
• OFFERED_COUNT数字YES
• ALLOCATED_COUNT数字YES
• STARTED_COUNT数字YES
• SUSPENDED_COUNT数字YES

问题：我们在这个表上经常面临死锁。我在这里附上了死锁图供参考。

迄今为止进行的调查：

• 死锁中的目标行(更新)彼此独立，因此不存在一个事务等待另一个事务放弃锁定的问题。甚至对表的插入也会成为死锁的一部分
• 在Oracle中，相同的事务在类似的并发负载下工作
• 由于怀疑该表而不是特定行被锁定，我们甚至尝试使用以下命令禁用表上的锁升级，但死锁仍然存在

ALTER TABLE TABLE_name SET(LOCK_ESCALATION DISABLE)

死锁图

<deadlock>
<victim-list>
<victimProcess id="process133f03d498" />
</victim-list>
<process-list>
<process id="process133f03d498" taskpriority="0" logused="3308" waitresource="RID: 11:1:819250:71" waittime="2377" ownerId="29578701" transactionname="implicit_transaction" lasttranstarted="2017-11-09T09:29:43.397" XDES="0x10b0b716a8" lockMode="U" schedulerid="6" kpid="7332" status="suspended" spid="210" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-09T09:29:45.977" lastbatchcompleted="2017-11-09T09:29:45.397" lastattention="1900-01-01T00:00:00.397" clientapp="jTDS" hostname="PAWSPPAS02" hostpid="123" loginname="STG_SUITE_SSO" isolationlevel="read committed (2)" xactid="29578701" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000ee409c1fd278abb7f476707399656cbedec9d1960000000000000000000000000000000000000000">
update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]-1 where [PARTICIPANT_ID]= @P0    </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000))update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]-1 where [PARTICIPANT_ID]= @P0    </inputbuf>
</process>
<process id="processc70140188" taskpriority="0" logused="85392" waitresource="RID: 11:1:819250:30" waittime="2196" ownerId="29574187" transactionname="implicit_transaction" lasttranstarted="2017-11-09T09:29:16.427" XDES="0x1150f5d6a8" lockMode="U" schedulerid="4" kpid="384" status="suspended" spid="141" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-09T09:29:46.157" lastbatchcompleted="2017-11-09T09:29:45.470" lastattention="1900-01-01T00:00:00.470" clientapp="jTDS" hostname="PAWSPPAS02" hostpid="123" loginname="STG_SUITE_SSO" isolationlevel="read committed (2)" xactid="29574187" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000001c3c261d27cfa98e13a9cb06f6c30e40cfcaa4a50000000000000000000000000000000000000000">
update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]+1 where [PARTICIPANT_ID]= @P0    </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000))update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]+1 where [PARTICIPANT_ID]= @P0    </inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="819250" dbid="11" objectname="STG_SUITE_SSO.dbo.RS_WI_PARTICIPANT_SUMM" id="lock1236714900" mode="X" associatedObjectId="72057599198953472">
<owner-list>
<owner id="processc70140188" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process133f03d498" mode="U" requestType="wait" />
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="819250" dbid="11" objectname="STG_SUITE_SSO.dbo.RS_WI_PARTICIPANT_SUMM" id="lockdacb14f00" mode="X" associatedObjectId="72057599198953472">
<owner-list>
<owner id="process133f03d498" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processc70140188" mode="U" requestType="wait" />
</waiter-list>
</ridlock>
</resource-list>
</deadlock>

Answer 1

一个线程已经更新了行819250:71，现在想要更新行819250:30，另一个线程更新了行81.9250:30，现在想要升级行819250:61。

我只能推测这里发生了什么(因为你省略了很多细节)，但我的猜测是，应用程序有一些逻辑，在某种交换中更新两个"参与者"，并且在数据库事务中，试图增加一个的金额+1，然后减少另一个的数量-1。然而，应用程序逻辑始终以相反的顺序执行(一个线程执行事务[a+=1，B-=1]，而另一个线程则执行事务[B+=1，a-=1])。没有任何模式设计可以防止这种死锁，只有应用程序逻辑更改。查看为什么并发反向更新如此频繁。

这似乎不是在[PARTICIPANT_ID]上使用非唯一非聚集索引的问题，因为在这种情况下，引擎将为两个语句以相同的顺序定位行819250:71和819250:30，并且不会死锁。因此，我的结论是，我们在一个事务中(至少)有两个更新语句，更新两个不同的行(两个@P0值)，并且应用程序逻辑正在从两个事务(可能是两个请求)以相反的顺序更新同一组参与者。

此外：

<process id="498" "RID: 11:1:819250:71" lasttranstarted="2017-11-09T09:29:43.397" lastbatchstarted="2017-11-09T09:29:45.977" lastbatchcompleted="2017-11-09T09:29:45.397"... >
<process id="188" "RID: 11:1:819250:30" lasttranstarted="2017-11-09T09:29:16.427" lastbatchstarted="2017-11-09T09:29:46.157" lastbatchcompleted="2017-11-09T09:29:45.470" ...>

如果你看看这两个交易：

• 流程1在9:29:43.97开始事务，在9:29:45.397完成一个批次，并在9:29:45.977开始当前批次
• 流程2在9:29:16.427开始事务，在9:29:45.470完成一个批次，并在9:29:46.157开始当前批次

这个时间序列证实了我的假设。事务包含一系列批处理，指示应用于不同行(不同ID)的单个更新(例如实体框架应用更改或类似)。ID是重叠的，更新的应用顺序不一致(同样，类似ORM的EF应用更改的典型行为)。

如果我的推测是正确的，那么您应该首先考虑锁定一个更高级别的逻辑实体。如果这些是参与者，那么他们一定参与了一些活动，比如游戏。在更新参与者之前，锁定游戏：例如，在交易开始时在[GAME_ID]上使用sp_getapplock。这样，更新参与者的顺序对于死锁不再是危险的，因为更新是为每个游戏序列化的。

另一种解决方案是在应用更新之前对参与者进行排序，以便每个事务都以相同的顺序更新行。

我们在[PACParticipant_ID]列上只有一个非聚集索引

几乎可以肯定这应该是一个聚集索引。在极少数情况下，使用非聚集索引和堆是有意义的。