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))))
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):
<?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>
Best Answer
Trail 1: Go with with (rowlock) on the update statement
Trail 2: Go with with (rowlock,updatelock) on the update statement