2

I'm receiving multiple deadlocks in one SQL Server. The deadlocks occur when the same query with different parameter values is being issued on the same database:

Example:

Query 1:

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

Query 2:

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

Deadlock graph

How can I prevent these kind of deadlocks?

Edit:

The .xdl content:

<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>

It is some adhoc query being ran against the database, probably part of some transaction. I'll contact the owner of the application to get more information about it.

Meanwhile, the estimated execution plan for this query is (100% cost in the clutered index update):

Query plan

<?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>

  • 1
    These update statements must be part of a larger transaction, otherwise one would just win and the other one would run second. – Aaron Bertrand Apr 16 at 16:37
  • 4
    What Aaron is trying to say is that you should post the full deadlock graph and if possible the query plans for the associated statements. – Erik Darling Apr 16 at 16:43
  • 2
    ...and a screenshot of the diagram is not enough; please post the .xdl file somewhere (that doesn't require readers to log in). – Aaron Bertrand Apr 16 at 18:15
  • How many rows are return when you write column3=117902015625 or column5=117902015625 ? How many rows in each table ? provide table detail including index. – KumarHarsh Apr 17 at 3:45
  • Thanks for the replies. Edited the post with the .xdl content and the update statement estimated query plan. The code is being run from the application code, so I also asked for more info to it's owner, will post it as soon as I get it (wether it's part of a larger transaction and which is it). – nahgeb Apr 17 at 14:06
1

Trail 1: Go with with (rowlock) on the update statement

Trail 2: Go with with (rowlock,updatelock) on the update statement

1

Is Column3 the clustered index column of the table? If not, change the query to get the clustered index column based on the predicate, and then, update those records only.

Always ensure the updates are happening based on the clustered indexed column to avoid deadlocks.

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;

Let me know if this doesn't solve your problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.