Sql-Server

同一頁面上的兩個更新語句死鎖

  • July 6, 2019

我在一個 SQL Server 中收到多個死鎖。當在同一個數據庫上發出具有不同參數值的相同查詢時，會發生死鎖：

例子：

查詢一：

UPDATE Tabel1 
SET column1=8, column2=1 
WHERE ((column3=117902015625) OR (column3 IN(SELECT column4 
                                                       FROM Table2 
                                                       WHERE (column5=117902015625))))

查詢 2：

UPDATE Tabel1 
SET column1=13, column2=1 
WHERE ((column3=300004584884) OR (column3 IN(SELECT column4 
                                                       FROM Table2 
                                                       WHERE (column5=300004584884))))

死鎖圖

我怎樣才能防止這種死鎖？

編輯：

.xdl 內容：

<deadlock-list>
<deadlock victim="process9945c01088">
   <process-list>
       <process id="process9945c01088" taskpriority="0" logused="700" waitresource="PAGE: 171:1:7201734 " waittime="495" ownerId="213003311093" transactionname="user_transaction" lasttranstarted="2019-04-16T15:27:35.287" XDES="0x83b69076c8" lockMode="U" schedulerid="30" kpid="45756" status="suspended" spid="299" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-16T15:27:35.317" lastbatchcompleted="2019-04-16T15:27:35.310" lastattention="1900-01-01T00:00:00.310" clientapp="DESKTOP-X.OP20:ID8" hostname="DESKTOP-X" hostpid="2396" loginname="UserLogin1" isolationlevel="read committed (2)" xactid="213003311093" currentdb="171" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
           <executionStack>
               <frame procname="adhoc" line="1" stmtend="396" sqlhandle="0x02000000f7a3603927bb0cbf024867c873cab3f31e81be510000000000000000000000000000000000000000">unknown</frame>
           </executionStack>
           <inputbuf>UPDATE Table1 SET column1=8, column2=1 WHERE ((column3=117902028987) OR (column3 IN(SELECT column4 FROM Table2 WHERE (column5=117902028987))))</inputbuf>
       </process>
       <process id="processa5cb477848" taskpriority="0" logused="700" waitresource="PAGE: 171:1:7201734 " waittime="554" ownerId="213003310946" transactionname="user_transaction" lasttranstarted="2019-04-16T15:27:35.247" XDES="0xbd6cd82728" lockMode="U" schedulerid="22" kpid="24788" status="suspended" spid="320" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-16T15:27:35.257" lastbatchcompleted="2019-04-16T15:27:35.257" lastattention="1900-01-01T00:00:00.257" clientapp="DESKTOP-X.OP130:ID13" hostname="DESKTOP-X" hostpid="2396" loginname="UserLogin1" isolationlevel="read committed (2)" xactid="213003310946" currentdb="171" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
           <executionStack>
               <frame procname="adhoc" line="1" stmtend="398" sqlhandle="0x02000000bc644c0a72563a7c4f6f8210645f67304fbc8bf60000000000000000000000000000000000000000">unknown</frame>
           </executionStack>
           <inputbuf>UPDATE Table1 SET column1=13, column2=1 WHERE ((column3=300088596120) OR (column3 IN(SELECT column4 FROM Table2 WHERE (column5=300088596120))))</inputbuf>
       </process>
   </process-list>
   <resource-list>
       <pagelock fileid="1" pageid="7201734" dbid="171" subresource="FULL" objectname="Table1" id="lock8595d81c00" mode="IX" associatedObjectId="72057594045792256">
           <owner-list>
               <owner id="processa5cb477848" mode="IX"/>
               <owner id="processa5cb477848" mode="U" requestType="convert"/>
           </owner-list>
           <waiter-list>
               <waiter id="process9945c01088" mode="U" requestType="convert"/>
           </waiter-list>
       </pagelock>
       <pagelock fileid="1" pageid="7201734" dbid="171" subresource="FULL" objectname="Table1" id="lock8595d81c00" mode="IX" associatedObjectId="72057594045792256">
           <owner-list>
               <owner id="process9945c01088" mode="IX"/>
               <owner id="process9945c01088" mode="U" requestType="convert"/>
           </owner-list>
           <waiter-list>
               <waiter id="processa5cb477848" mode="U" requestType="convert"/>
           </waiter-list>
       </pagelock>
   </resource-list>
</deadlock>

這是針對數據庫執行的一些臨時查詢，可能是某些事務的一部分。我將聯繫該應用程序的所有者以獲取有關它的更多資訊。

同時，該查詢的估計執行計劃為（100% 的聚集索引更新成本）：

查詢計劃

<?xml version="1.0" encoding="utf-16"?><ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.4422.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"><BatchSequence>
<Batch>
 <Statements>
   <StmtSimple StatementCompId="1" StatementEstRows="724387" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="69.9168" StatementText="UPDATE Table1 SET column1=13, column2=1 WHERE ((column3=300088596120) OR (column3 IN(SELECT column4 FROM Table2 WHERE (column5=300088596120))))" StatementType="UPDATE" QueryHash="0x9EC73E6FECACD77D" QueryPlanHash="0xE66B1A74E94A6BC0" RetrievedFromCache="false" SecurityPolicyApplied="false">
     <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
     <QueryPlan CachedPlanSize="48" CompileTime="55" CompileCPU="55" CompileMemory="1080">
       <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" />
       <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="409600" EstimatedPagesCached="819200" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="82307832" />
       <TraceFlags IsCompileTime="true">
         <TraceFlag Value="1222" Scope="Global" />
         <TraceFlag Value="3226" Scope="Global" />
       </TraceFlags>
       <RelOp AvgRowSize="9" EstimateCPU="0.724387" EstimateIO="69.0174" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="724387" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="69.9168">
         <OutputList />
         <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
           <Object Database="[Database1]" Schema="[dbo]" Table="[Table1]" Index="[PK_Table1]" IndexKind="Clustered" Storage="RowStore" />
           <SetPredicate>
             <ScalarOperator ScalarString="[Database1].[dbo].[Table1].[column1] = [Expr1004],[Database1].[dbo].[Table1].[column2] = [Expr1005]">
               <ScalarExpressionList>
                 <ScalarOperator>
                   <MultipleAssign>
                     <Assign>
                       <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="column1" />
                       <ScalarOperator>
                         <Identifier>
                           <ColumnReference Column="Expr1004" />
                         </Identifier>
                       </ScalarOperator>
                     </Assign>
                     <Assign>
                       <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="column2" />
                       <ScalarOperator>
                         <Identifier>
                           <ColumnReference Column="Expr1005" />
                         </Identifier>
                       </ScalarOperator>
                     </Assign>
                   </MultipleAssign>
                 </ScalarOperator>
               </ScalarExpressionList>
             </ScalarOperator>
           </SetPredicate>
           <RelOp AvgRowSize="27" EstimateCPU="0.0724387" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="724387" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.174983">
             <OutputList>
               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
               <ColumnReference Column="Expr1004" />
               <ColumnReference Column="Expr1005" />
             </OutputList>
             <ComputeScalar>
               <DefinedValues>
                 <DefinedValue>
                   <ColumnReference Column="Expr1004" />
                   <ScalarOperator ScalarString="(13)">
                     <Const ConstValue="(13)" />
                   </ScalarOperator>
                 </DefinedValue>
                 <DefinedValue>
                   <ColumnReference Column="Expr1005" />
                   <ScalarOperator ScalarString="(1)">
                     <Const ConstValue="(1)" />
                   </ScalarOperator>
                 </DefinedValue>
               </DefinedValues>
               <RelOp AvgRowSize="15" EstimateCPU="0.0724387" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="724387" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.102545">
                 <OutputList>
                   <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                 </OutputList>
                 <Top RowCount="true" IsPercent="false" WithTies="false">
                   <TopExpression>
                     <ScalarOperator ScalarString="(0)">
                       <Const ConstValue="(0)" />
                     </ScalarOperator>
                   </TopExpression>
                   <RelOp AvgRowSize="15" EstimateCPU="1.75E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1.5" LogicalOp="Aggregate" NodeId="4" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0301061">
                     <OutputList>
                       <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                     </OutputList>
                     <StreamAggregate>
                       <DefinedValues />
                       <GroupBy>
                         <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                       </GroupBy>
                       <RelOp AvgRowSize="15" EstimateCPU="0.0056023" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Concatenation" NodeId="5" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.0301043">
                         <OutputList>
                           <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                         </OutputList>
                         <Merge>
                           <DefinedValues>
                             <DefinedValue>
                               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                             </DefinedValue>
                           </DefinedValues>
                           <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="804874">
                             <OutputList>
                               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                             </OutputList>
                             <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                               <DefinedValues>
                                 <DefinedValue>
                                   <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                                 </DefinedValue>
                               </DefinedValues>
                               <Object Database="[Database1]" Schema="[dbo]" Table="[Table1]" Index="[SK_Table1_column3]" IndexKind="NonClustered" Storage="RowStore" />
                               <SeekPredicates>
                                 <SeekPredicateNew>
                                   <SeekKeys>
                                     <Prefix ScanType="EQ">
                                       <RangeColumns>
                                         <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="column3" />
                                       </RangeColumns>
                                       <RangeExpressions>
                                         <ScalarOperator ScalarString="(300088596120.)">
                                           <Const ConstValue="(300088596120.)" />
                                         </ScalarOperator>
                                       </RangeExpressions>
                                     </Prefix>
                                   </SeekKeys>
                                 </SeekPredicateNew>
                               </SeekPredicates>
                             </IndexScan>
                           </RelOp>
                           <RelOp AvgRowSize="15" EstimateCPU="0.000100015" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="8" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0212189">
                             <OutputList>
                               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                             </OutputList>
                             <MemoryFractions Input="1" Output="1" />
                             <Sort Distinct="true">
                               <OrderBy>
                                 <OrderByColumn Ascending="true">
                                   <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                                 </OrderByColumn>
                               </OrderBy>
                               <RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985766">
                                 <OutputList>
                                   <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                                 </OutputList>
                                 <NestedLoops Optimized="false">
                                   <OuterReferences>
                                     <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="column4" />
                                   </OuterReferences>
                                   <RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                                     <OutputList>
                                       <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="column4" />
                                     </OutputList>
                                     <NestedLoops Optimized="false">
                                       <OuterReferences>
                                         <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="Table2_Id(PK)" />
                                       </OuterReferences>
                                       <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="11" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1033460">
                                         <OutputList>
                                           <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="Table2_Id(PK)" />
                                         </OutputList>
                                         <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                           <DefinedValues>
                                             <DefinedValue>
                                               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="Table2_Id(PK)" />
                                             </DefinedValue>
                                           </DefinedValues>
                                           <Object Database="[Database1]" Schema="[dbo]" Table="[Table2]" Index="[IX_Table2_column5]" IndexKind="NonClustered" Storage="RowStore" />
                                           <SeekPredicates>
                                             <SeekPredicateNew>
                                               <SeekKeys>
                                                 <Prefix ScanType="EQ">
                                                   <RangeColumns>
                                                     <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="column5" />
                                                   </RangeColumns>
                                                   <RangeExpressions>
                                                     <ScalarOperator ScalarString="(300088596120.)">
                                                       <Const ConstValue="(300088596120.)" />
                                                     </ScalarOperator>
                                                   </RangeExpressions>
                                                 </Prefix>
                                               </SeekKeys>
                                             </SeekPredicateNew>
                                           </SeekPredicates>
                                         </IndexScan>
                                       </RelOp>
                                       <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="13" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1033460">
                                         <OutputList>
                                           <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="column4" />
                                         </OutputList>
                                         <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                           <DefinedValues>
                                             <DefinedValue>
                                               <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="column4" />
                                             </DefinedValue>
                                           </DefinedValues>
                                           <Object Database="[Database1]" Schema="[dbo]" Table="[Table2]" Index="[PK_Table2]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
                                           <SeekPredicates>
                                             <SeekPredicateNew>
                                               <SeekKeys>
                                                 <Prefix ScanType="EQ">
                                                   <RangeColumns>
                                                     <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="Table2_Id(PK)" />
                                                   </RangeColumns>
                                                   <RangeExpressions>
                                                     <ScalarOperator ScalarString="[Database1].[dbo].[Table2].[Table2_Id(PK)]">
                                                       <Identifier>
                                                         <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="Table2_Id(PK)" />
                                                       </Identifier>
                                                     </ScalarOperator>
                                                   </RangeExpressions>
                                                 </Prefix>
                                               </SeekKeys>
                                             </SeekPredicateNew>
                                           </SeekPredicates>
                                         </IndexScan>
                                       </RelOp>
                                     </NestedLoops>
                                   </RelOp>
                                   <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="14" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="804874">
                                     <OutputList>
                                       <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                                     </OutputList>
                                     <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                       <DefinedValues>
                                         <DefinedValue>
                                           <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="Table1_Id(PK)" />
                                         </DefinedValue>
                                       </DefinedValues>
                                       <Object Database="[Database1]" Schema="[dbo]" Table="[Table1]" Index="[SK_Table1_column3]" IndexKind="NonClustered" Storage="RowStore" />
                                       <SeekPredicates>
                                         <SeekPredicateNew>
                                           <SeekKeys>
                                             <Prefix ScanType="EQ">
                                               <RangeColumns>
                                                 <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table1]" Column="column3" />
                                               </RangeColumns>
                                               <RangeExpressions>
                                                 <ScalarOperator ScalarString="[Database1].[dbo].[Table2].[column4">
                                                   <Identifier>
                                                     <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Table2]" Column="column4" />
                                                   </Identifier>
                                                 </ScalarOperator>
                                               </RangeExpressions>
                                             </Prefix>
                                           </SeekKeys>
                                         </SeekPredicateNew>
                                       </SeekPredicates>
                                     </IndexScan>
                                   </RelOp>
                                 </NestedLoops>
                               </RelOp>
                             </Sort>
                           </RelOp>
                         </Merge>
                       </RelOp>
                     </StreamAggregate>
                   </RelOp>
                 </Top>
               </RelOp>
             </ComputeScalar>
           </RelOp>
         </Update>
       </RelOp>
     </QueryPlan>
   </StmtSimple>
 </Statements>
</Batch>

Trail 1：在更新語句上使用 with (rowlock)

Trail 2：在更新語句上使用 (rowlock,updatelock)

Column3 是表的聚集索引列嗎？如果不是，請更改查詢以根據謂詞獲取聚集索引列，然後僅更新這些記錄。

始終確保基於聚集索引列進行更新以避免死鎖。

Select (ClusteredKey) into #tblSometable From Table1 
Where Column3 =(Your Predicate)

Update A
SET Column2 = ABC, ...ColumnN = MNO
FROM Table1 A
Join #tblSometable B
ON A.ClusteredKey = B.ClusteredKey;

如果這不能解決您的問題，請告訴我。

引用自：https://dba.stackexchange.com/questions/234947

相關問答

Sql-Server

SQL 死鎖 - 同時呼叫的相同儲存過程

  • March 16, 2022
檢視問答
Sql-Server

如果出現死鎖異常（MSSQL），我可以重試每個 SQL 查詢嗎

  • December 3, 2021
檢視問答
Sql-Server

MS SQL Server 2019 - 主鍵更新行後出現死鎖，可能由觸發器引起

  • November 27, 2020
檢視問答
Sql-Server

儲存過程本身死鎖，我該如何解決？

  • March 9, 2018
檢視問答
Sql-Server

INSERT/UPDATE 儲存過程本身死鎖

  • January 8, 2018
檢視問答
Sql-Server

死鎖——如何讓它變得更好？

  • August 1, 2017
檢視問答