SQL Server更新语句导致死锁

Question

我有一个应用程序在不同的线程中同时运行多个 sql 语句，导致各种死锁，这些死锁似乎都来自一个表。例如下面的两个更新语句：

UPDATE WF SET QUEUETIME='2011-02-18 13:06:53.578', STATE = 'outbound', USER = '', TIME = null WHERE PID = 'MessageProcessing' AND ACTIVITYID = '删除' AND ITEMID = '120' AND TRANID = '已创建' AND STATE = 'ready' AND USER = ''

UPDATE WF SET QUEUETIME='2011-02-18 13:06： 53.625'，状态 = '就绪'，用户 = ''，时间 = null WHERE PID = 'standardOutbound' AND ACTIVITYID = 'Node1' AND ITEMID = '121' AND TRANID = 'toNode1' AND STATE = '' AND USER = ''

产生以下死锁：

<deadlock-list>
 <deadlock victim="process6d8e38">
  <process-list>
   <process id="process6d8e38" taskpriority="0" logused="272" waitresource="RID: 7:1:564:14" waittime="625" ownerId="430343" transactionname="implicit_transaction" lasttranstarted="2011-02-18T13:06:53.640" XDES="0xb44a258" lockMode="U" schedulerid="1" kpid="2632" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-02-18T13:06:53.640" lastbatchcompleted="2011-02-18T13:06:53.640" clientapp="jTDS" hostname="INTERWOV-FP1" hostpid="123" loginname="database1" isolationlevel="read committed (2)" xactid="430343" currentdb="7" TIMEout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="336" sqlhandle="0x0200000077e2b21749c20d3ca2ca8d4d89ea5ea29336e03e">
UPDATE WF  SET QUEUETIME =  @P0 , STATE =  @P1 , USER =  @P2 , TIME =  @P3  WHERE PID =  @P4  AND ACTIVITYID =  @P5  AND ITEMID =  @P6  AND TRANID =  @P7  AND STATE =  @P8  AND USER =  @P9     </frame>
    </executionStack>
    <inputbuf>
(@P0 datetime,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 int,@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000))UPDATE WF  SET QUEUETIME =  @P0 , STATE =  @P1 , USER =  @P2 , TIME =  @P3  WHERE PID =  @P4  AND ACTIVITYID =  @P5  AND ITEMID =  @P6  AND TRANID =  @P7  AND STATE =  @P8  AND USER =  @P9     </inputbuf>
   </process>
   <process id="process8ccb68" taskpriority="0" logused="900" waitresource="RID: 7:1:564:12" waittime="625" ownerId="430341" transactionname="implicit_transaction" lasttranstarted="2011-02-18T13:06:53.623" XDES="0xaeccf48" lockMode="U" schedulerid="2" kpid="312" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-02-18T13:06:53.640" lastbatchcompleted="2011-02-18T13:06:53.623" clientapp="jTDS" hostname="INTERWOV-FP1" hostpid="123" loginname="database1" isolationlevel="read committed (2)" xactid="430341" currentdb="7" TIMEout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="336" sqlhandle="0x0200000077e2b21749c20d3ca2ca8d4d89ea5ea29336e03e">
UPDATE WF  SET QUEUETIME =  @P0 , STATE =  @P1 , USER =  @P2 , TIME =  @P3  WHERE PID =  @P4  AND ACTIVITYID =  @P5  AND ITEMID =  @P6  AND TRANID =  @P7  AND STATE =  @P8  AND USER =  @P9     </frame>
    </executionStack>
    <inputbuf>
(@P0 datetime,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 int,@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000))UPDATE WF  SET QUEUETIME =  @P0 , STATE =  @P1 , USER =  @P2 , TIME =  @P3  WHERE PID =  @P4  AND ACTIVITYID =  @P5  AND ITEMID =  @P6  AND TRANID =  @P7  AND STATE =  @P8  AND USER =  @P9     </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <ridlock fileid="1" pageid="564" dbid="7" objectname="database1.dbo.WF" id="lock3a63dc0" mode="X" associatedObjectId="72057594077577216">
    <owner-list>
     <owner id="process6d8e38" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process8ccb68" mode="U" requestType="wait"/>
    </waiter-list>
   </ridlock>
   <ridlock fileid="1" pageid="564" dbid="7" objectname="database1.dbo.WF" id="lock3a65f40" mode="X" associatedObjectId="72057594077577216">
    <owner-list>
     <owner id="process8ccb68" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6d8e38" mode="U" requestType="wait"/>
    </waiter-list>
   </ridlock>
  </resource-list>
 </deadlock>
</deadlock-list>

我意识到一定数量的死锁是不可避免的，并且应用程序应该处理它们（确实如此），但我不明白为什么它们应该在这种情况下发生。在我简单的想法中，这两个语句应该锁定不同的行，即使它们正在更新同一行，一个也应该等待另一个。

任何人都可以解释为什么它们会导致僵局，或者就如何防止僵局提出任何建议吗？

我们在表上有 3 个非聚集索引（PID、ACTIVITYID、ITEMID、TRANID）、（ITEMID）和（PID、ACTIVITYID）。 (PID、ACTIVITYID、ITEMID、TRANID) 构成主键。我尝试过（有点盲目地）使用索引，但似乎无济于事。

该应用程序在 weblogic 和 sql server 2005 上运行，我在 websphere 和 sql server 2008 上重现了死锁。使用 Oracle 数据库时似乎不会发生这种情况，但不幸的是，这不是我们客户的选择！

非常感谢任何能够对此提供帮助或见解的人。

Answer 1

问题通常不是单独的更新，而是选择和更新的组合。考虑这样的场景：事务选择某行然后更新它。如果两个这样的事务并行运行，就会发生死锁。
最简单的解决方案是在 select 语句中使用 UPDLOCK（以及可选的 ROWLOCK）提示；当然仅用于记录，之后更新 - 否则你可能会以缓慢的应用程序而告终。

Answer 2

有聚集索引吗？有索引视图吗？还有其他指标吗？正在更新的列似乎不在任何索引中。行级锁定应该没问题，但一定有什么原因导致升级。这些集合似乎是不相交的，但也许页面是重叠的（因此我对聚集索引的选择提出了问题）。

http://msdn.microsoft.com/en-us/library/ms184286.aspx