帮助解决由SQL Azure自动创建的索引导致的死锁 [英] Help Solving a Deadlock caused by an index created automatically by SQL Azure
问题描述
我有一个SQL Azure数据库，我在SQL Azure中启用了自动调整，因此Azure会根据需要创建和删除索引。我喜欢这个，因为我对idices的知识水平不是很好。
I have a SQL Azure database where i have enabled automatic tuning in SQL Azure, so Azure creates and deletes indices as it feels necessary. I like this as my level of knowledge on idices is not great.
现在，我有一张桌子已经开始导致死锁，我不知道为什么。它是一个具有状态列的简单表，我们将其用作队列，我们将行添加到挂起状态，然后我们选择待处理任务并将其更新为正在进行中，并且一旦
完成，我们将更新要完成的状态。桌面上不断有插入和更新。
Now, i have a table that has started to cause deadlocks and i dont know why. It is a simple table that has a status column, and we use it as like a queue, we add rows in a pending state, then we select a pending task and update it to in progress, and once complete we update the status to complete. There are constantly inserts and updates happening on the table.
以下是死锁的详细信息。任何人都可以用合理的简单术语向我解释是什么导致了我的僵局。它们是两个更新的quieres所以我认为一个应该阻止另一个，并等到另一个完成然后继续。
所以我认为它是导致死锁的索引，但除此之外我什么都不知道。非常感谢任何帮助。
Below are the details of the deadlock. Can anyone explain to me in reasonable simple terms what is causing my deadlock. They are two update quieres so i think one should just block the other, and wait until the other is finished and then continue. So i assume it is the index that is causing the deadlock, but i dont know anything beyond that. Really appreciate any help.
<deadlock>
<victim-list>
<victimProcess id="process13ab3b22ca8" />
</victim-list>
<process-list>
<process id="process13ab3b22ca8" taskpriority="0" logused="0" waitresource="KEY: 5:72057594049658880 (e10addd384fe)" waittime="449" ownerId="493079967" transactionname="UPDATE" lasttranstarted="2019-05-23T00:09:38.330" XDES="0x13abade4428" lockMode="U" schedulerid="1" kpid="61712" status="suspended" spid="132" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-05-23T00:09:38.330" lastbatchcompleted="2019-05-23T00:09:38.337" lastattention="1900-01-01T00:00:00.337" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF21FC37" hostpid="11172" loginname="loginname" isolationlevel="read committed (2)" xactid="493079967" currentdb="5" currentdbname="databasename" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="e1a95efe-ec78-4847-b0cb-f14d3a257103.tb.TaskSchedulerItem_Update_PendingItemsToCancelled_ByTaskSchedulerIdByReferenceId" queryhash="0xad84b250c4c13ec3" queryplanhash="0x879b9bca9cf428df" line="10" stmtstart="376" stmtend="814" sqlhandle="0x03000500ddcd7a0d01462f01baa9000001000000000000000000000000000000000000000000000000000000">
Update [TaskSchedulerItem]
Set ItemStatus='CANCELLED',
ItemStatusDescription='CANCELLED DUE TO NEW ITEM REPLACING'
Where TaskSchedulerId=@TaskSchedulerId
and ItemReferenceId=@ItemReferenceId
and ItemStatus='PENDING </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 226151901] </inputbuf>
</process>
<process id="process13ab712f468" taskpriority="0" logused="376" waitresource="KEY: 5:72057594053591040 (c501f23cb8fa)" waittime="449" ownerId="493079974" transactionname="UPDATE" lasttranstarted="2019-05-23T00:09:38.437" XDES="0x13abf698428" lockMode="X" schedulerid="2" kpid="114596" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-05-23T00:09:38.367" lastbatchcompleted="2019-05-23T00:09:38.370" lastattention="1900-01-01T00:00:00.370" clientapp=".Net SqlClient Data Provider" hostname="RD00155D31D49D" hostpid="16940" loginname="loginname" isolationlevel="read committed (2)" xactid="493079974" currentdb="5" currentdbname="databasename" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="e1a95efe-ec78-4847-b0cb-f14d3a257103.tb.TaskSchedulerItem_Select_NextToProcess" queryhash="0xec04ed9404b13746" queryplanhash="0x54e2b3a709c1a4eb" line="51" stmtstart="2460" stmtend="3644" sqlhandle="0x03000500f0fe481b961a020153aa000001000000000000000000000000000000000000000000000000000000">
UPDATE [TaskSchedulerItem]
SET
ItemStatus = 'IN PROGRESS',
ItemStatusDescription = 'IN PROGRESS',
DateLastUpdated = @CurrentDateTime
output inserted.TaskSchedulerId,inserted.TaskSchedulerItemId, inserted.ItemReferenceId,inserted.ItemStatus,inserted.ItemStatusDescription, inserted.DateCreated, inserted.DateLastUpdated, inserted.FailureCount
Where TaskSchedulerItemId = @TaskSchedulerItemId
and
(
ItemStatus in ('PENDING','FAILED')
or
(ItemStatus = 'IN PROGRESS' AND DateLastUpdated<DATEADD(MINUTE,-1,@CurrentDateTime))
)
and TaskSchedulerId=@TaskSchedulerI </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 457768688] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594049658880" dbid="5" objectname="e1a95efe-ec78-4847-b0cb-f14d3a257103.tb.TaskSchedulerItem" indexname="PK_TaskSchedulerItem" id="lock13995619000" mode="X" associatedObjectId="72057594049658880">
<owner-list>
<owner id="process13ab712f468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process13ab3b22ca8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594053591040" dbid="5" objectname="e1a95efe-ec78-4847-b0cb-f14d3a257103.tb.TaskSchedulerItem" indexname="nci_wi_TaskSchedulerItem_10D8AC902621242A89D6" id="lock139c6032880" mode="U" associatedObjectId="72057594053591040">
<owner-list>
<owner id="process13ab3b22ca8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process13ab712f468" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
David
推荐答案
看起来你桌子上有一个复合PK，我猜它是获取键锁而不是行锁的原因，并且键锁更容易发生死锁。
It looks like you have a compound PK on the table, which I guess is the cause of it taking key locks rather than row locks, and the key locks are more prone to deadlocks.
逻辑是否需要一次更新多行，或者只需要单行？ &NBSP;如果是这样，你可以将PK移动到一个标识字段，有更简单的谓词，并可能消除那种死锁。
Does the logic require updating many rows at a time, or just single rows? If so you could move the PK to an identity field, have simpler predicates, and probably eliminate the deadlocks that way.
否则它可能需要尝试提示或要解决的东西。
Otherwise it will probably require trying hints or something to resolve.
对于使用复合键执行任何操作，SQL Server通常都很麻烦。
SQL Server is often twitchy about doing anything with compound keys.
Josh
这篇关于帮助解决由SQL Azure自动创建的索引导致的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
