SQL Server 死锁、单个查询、页锁.包括死锁图和表

Question

我们在多用户环境中遇到死锁。它发生在大约 1% 的时间内。

它属于页面锁定类型，仅在单个查询中发生。我以前从未见过的东西。

由于这只发生在我们无法重现的生产环境中，我想就如何解决这个问题寻求一些帮助/想法。

桌子：

CREATE TABLE [ext].[PickingRows](
    [PickingRowId] [uniqueidentifier] NOT NULL,
    [OrderId] [nvarchar](50) NOT NULL,
    [RecId] [bigint] NOT NULL,
    [RouteId] [nvarchar](50) NOT NULL,
    [ReferenceType] [int] NOT NULL,
    [TransferReference] [uniqueidentifier] NOT NULL,
    [PalletTypeHeaderId] [uniqueidentifier] NULL,
    [ItemNo] [nvarchar](50) NOT NULL,
    [Qty] [int] NOT NULL,
    [Weight] [decimal](18, 3) NOT NULL,
    [SSCC] [nvarchar](18) NULL,
    [BatchNo] [nvarchar](50) NULL,
    [BestBeforeDate] [nvarchar](6) NULL,
    [UserName] [nvarchar](50) NOT NULL,
    [DataAreaId] [nvarchar](4) NOT NULL,
    [Status] [int] NOT NULL,
    [ConsafeId] [uniqueidentifier] NULL,
    [CreateDate] [datetime2](7) NULL,
    [CreatedByUser] [varchar](256) NULL,
    [CreatedByApplication] [varchar](128) NULL,
    [CreatedByHost] [varchar](128) NULL,
    [UpdateDate] [datetime2](7) NULL,
    [UpdatedByUser] [varchar](256) NULL,
    [UpdatedByApplication] [varchar](128) NULL,
    [UpdatedByHost] [varchar](128) NULL,
 CONSTRAINT [PK_PickingRows] PRIMARY KEY CLUSTERED 
(
    [PickingRowId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,         ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

死锁图：

<deadlock>
  <victim-list>
    <victimProcess id="process8054bdc8" />
  </victim-list>
  <process-list>
    <process id="process8054bdc8" taskpriority="0" logused="0" waitresource="PAGE: 13:1:1435527" waittime="2253" ownerId="26857711" transactionname="UPDATE" lasttranstarted="2015-02-03T10:25:34.657" XDES="0x116e4d890" lockMode="U" schedulerid="2" kpid="1980" status="suspended" spid="58" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2015-02-03T10:25:34.550" lastbatchcompleted="2015-02-03T10:25:34.550" clientapp=".Net SqlClient Data Provider" hostname="XXXX" hostpid="1292" isolationlevel="read committed (2)" xactid="26857711" currentdb="13" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="" line="13" stmtstart="266" sqlhandle="0x02000000224c023b6d575eec65b98a1620bc85b551bd7a20" />
        <frame procname="" line="13" stmtstart="476" stmtend="978" sqlhandle="0x02000000776ebd001430def4543cc4d0740b5e0b643fb45e" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process8054abc8" taskpriority="0" logused="256" waitresource="PAGE: 13:1:1435527" waittime="2205" ownerId="26857710" transactionname="UPDATE" lasttranstarted="2015-02-03T10:25:34.637" XDES="0x116e4d620" lockMode="U" schedulerid="2" kpid="4872" status="suspended" spid="71" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2015-02-03T10:25:34.217" lastbatchcompleted="2015-02-03T10:25:34.217" clientapp=".Net SqlClient Data Provider" hostname="XXXX" hostpid="1292" isolationlevel="read committed (2)" xactid="26857710" currentdb="13" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="" line="13" stmtstart="266" sqlhandle="0x02000000224c023b6d575eec65b98a1620bc85b551bd7a20" />
        <frame procname="" line="13" stmtstart="476" stmtend="978" sqlhandle="0x020000006380f42992ac44812fc42ce93a32f85b8b2fd3e8" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process3f39948" taskpriority="0" logused="10000" waittime="2042" schedulerid="2" kpid="740" status="suspended" spid="71" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-02-03T10:25:34.217" lastbatchcompleted="2015-02-03T10:25:34.217" clientapp=".Net SqlClient Data Provider" hostname="XXX" hostpid="1292" loginname="XXX" isolationlevel="read committed (2)" xactid="26857710" currentdb="13" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="" line="13" stmtstart="266" sqlhandle="0x02000000224c023b6d575eec65b98a1620bc85b551bd7a20" />
        <frame procname="" line="13" stmtstart="476" stmtend="978" sqlhandle="0x020000006380f42992ac44812fc42ce93a32f85b8b2fd3e8" />
      </executionStack>
      <inputbuf>
if(exists (
 select * 
 from ext.PickingRows
 where DataAreaId = 'KSE1'
  and RouteId = '334417'
  and ItemNo = '8277'
  and BatchNo = '150130038'
  and SSCC is null
  and RecId = 5638470269 
  and UserName = '8084'
))
begin
 update ext.PickingRows
 set Qty = Qty + 1,
  Weight = Weight + 1 * 1.87
 where DataAreaId = 'KSE1'
  and RouteId = '334417'
  and ItemNo = '8277'
  and BatchNo = '150130038'
  and SSCC is null
  and RecId = 5638470269
  and UserName = '8084'
end
else
begin
insert into ext.PickingRows (DataAreaId,
RouteId, 
RecId, 
OrderId, 
ReferenceType, 
ItemNo, 
Qty, 
[Weight], 
BatchNo, 
UserName, 
[Status], 
BestBeforeDate)
values
('KSE1',
'334417',
5638470269,
'63309',
2,  
'8277', 
1, 
1 * 1.87, 
'150130038', 
'8084', 
'7200', 
'150209')
end
   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="1435527" dbid="13" objectname="" id="lockbd444500" mode="U" associatedObjectId="72057594040025088">
      <owner-list>
        <owner id="process3f39948" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process8054bdc8" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="1435527" dbid="13" objectname="" id="lockbd444500" mode="U" associatedObjectId="72057594040025088">
      <owner-list />
      <waiter-list>
        <waiter id="process8054abc8" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
    <exchangeEvent id="Pipe1736c0500" WaitType="e_waitPipeGetRow" nodeId="3">
      <owner-list>
        <owner id="process8054abc8" />
      </owner-list>
      <waiter-list>
        <waiter id="process3f39948" />
      </waiter-list>
    </exchangeEvent>
  </resource-list>
</deadlock>

表本身大约有 350 行，没有任何触发器、外键或特殊索引。

我想做的是优化查询，如下所示：

UPDATE ext.PickingRows
SET 
  Qty = Qty + 1,
  Weight = Weight + 1 * 1.87
WHERE
  DataAreaId = 'KSE1'
  and RouteId = '334417'
  and ItemNo = '8277'
  and BatchNo = '150130038'
  and SSCC is null
  and RecId = 5638470269
  and UserName = '8084'
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO ext.PickingRows (DataAreaId, RouteId, RecId,  OrderId, ReferenceType, ItemNo, Qty, [Weight], BatchNo, UserName, [Status], BestBeforeDate)
    VALUES ('KSE1', '334417', 5638470269, '63309', 2, '8277', 1, 1 * 1.87, '150130038', '8084', '7200', '150209')
END

这样我就跳过了不需要的 SELECT 语句。

我的问题是，这是否可以解决僵局问题，或者你们中是否有人对此有任何想法。

问题是更新这些查询需要在 24/7 全天候运行的客户处停止生产（大约 1 分钟），这对于多次执行此操作并不理想。

非常感谢！

编辑：也许可以说它在没有任何事务的情况下运行并且隔离级别没有更改，因此它设置为 事务隔离级别 已提交

Answer 1

我遇到的死锁最常见的情况是选择语句，然后是更新。

发生的情况是，两个查询同时采用读锁，然后两个查询都需要将锁更新为独占锁，因此它们必须相互等待（发生死锁）。

为了解决这个问题，我认为您的解决方案可以使用更新而不是 select 语句，另一种方法是在 select 语句上尝试 UPDLOCK 表提示。

if(exists 
(select *  from ext.PickingRows (UPDLOCK)
....
...

有关表格提示的更多信息：https://msdn.microsoft.com/en-us/library/ms187373.aspx