RE: Deadlock on update command

SSCertifiable

Points: 7567

March 25, 2013 at 4:44 am

You've already posted them. No, it looks like a part of the deadlock info is missing. Can you post the actual plans for both of the queries please?
You may well get some mileage by changing the update query as follows:
UPDATE orderha
SET [status] = 2
WHERE sessionid = '7560129'
AND orderha = 1
AND [status] <> 2
The actual plan for this query would be useful too.

Plans for old queries in previous post, plan for new code below:

http://s9.postimg.org/ilcy4oi8v/exeplan3.png

<?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.1" Build="10.0.1600.22" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

<StmtSimple StatementCompId="1" StatementEstRows="1.0509" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0369085" StatementText="UPDATE [orderha] set [status] = @1 WHERE [sessionid]=@2 AND [orderha]=@3 AND [status]<>@4" StatementType="UPDATE" QueryHash="0xD91B15D2CAD0727F" QueryPlanHash="0x341CA7AF0267EE91">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="328">

<RelOp AvgRowSize="9" EstimateCPU="3.15269E-06" EstimateIO="0.03" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0369085">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Update DMLRequestSort="false">

<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[PK_orderha]" IndexKind="Clustered" />

<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[IX_sessionid]" IndexKind="NonClustered" />

<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[IX_shipopt_suspect_status_rsn_dateadded_oos]" IndexKind="NonClustered" />

<SetPredicate>

<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[status] = [Expr1021]">

<ScalarExpressionList>

<ScalarOperator>

<MultipleAssign>

<Assign>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1021" />

</Identifier>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

<RelOp AvgRowSize="27" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690538">

<OutputList>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

<ColumnReference Column="Expr1021" />

<ColumnReference Column="Expr1024" />

<ColumnReference Column="Expr1025" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1025" />

<ScalarOperator ScalarString="[Expr1025]">

<Identifier>

<ColumnReference Column="Expr1025" />

</Identifier>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1024" />

<ScalarOperator ScalarString="[Expr1024]">

<Identifier>

<ColumnReference Column="Expr1024" />

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="27" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690538">

<OutputList>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

<ColumnReference Column="Expr1021" />

<ColumnReference Column="Expr1024" />

<ColumnReference Column="Expr1025" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1024" />

<ScalarOperator ScalarString="CASE WHEN [Expr1006] THEN (0) ELSE (1) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1006" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1025" />

<ScalarOperator ScalarString="CASE WHEN [Expr1006] THEN (0) ELSE (1) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1006" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="20" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690527">

<OutputList>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

<ColumnReference Column="Expr1006" />

<ColumnReference Column="Expr1021" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1021" />

<ScalarOperator ScalarString="(2)">

<Const ConstValue="(2)" />

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="16" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690517">

<OutputList>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

<ColumnReference Column="Expr1006" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1006" />

<ScalarOperator ScalarString="CASE WHEN [DBxxxx].[dbo].[orderha].[status] = (2) THEN (1) ELSE (0) END">

<IF>

<Condition>

<ScalarOperator>

<Compare CompareOp="BINARY IS">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(2)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="19" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Top" NodeId="6" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00690506">

<OutputList>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Top RowCount="true" IsPercent="false" WithTies="false">

<TopExpression>

<ScalarOperator ScalarString="(0)">

<Const ConstValue="(0)" />

</ScalarOperator>

</TopExpression>

<RelOp AvgRowSize="23" EstimateCPU="5.30974E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.27027" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00690435">

<OutputList>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<NestedLoops Optimized="false">

<OuterReferences>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

</OuterReferences>

<RelOp AvgRowSize="19" EstimateCPU="0.000158397" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.27027" LogicalOp="Index Seek" NodeId="8" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032834" TableCardinality="3555030">

<OutputList>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

</DefinedValue>

</DefinedValues>

<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[IX_sessionid]" IndexKind="NonClustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="sessionid" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="N'7560129'">

<Const ConstValue="N'7560129'" />

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

<Predicate>

<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[status]<(2) OR [DBxxxx].[dbo].[orderha].[status]>(2)">

<Logical Operation="OR">

<ScalarOperator>

<Compare CompareOp="LT">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(2)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="GT">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(2)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.270273" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Clustered Index Seek" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00361452" TableCardinality="3555030">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />

</RunTimeInformation>

<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">

<DefinedValues />

<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[PK_orderha]" TableReferenceId="-1" IndexKind="Clustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[rsn]">

<Identifier>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

<Predicate>

<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[orderha]=(1)">

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="orderha" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Top>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</Update>

</RelOp>

<ParameterList>

<ColumnReference Column="@1" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />

<ColumnReference Column="@4" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />

<ColumnReference Column="@3" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />

<ColumnReference Column="@2" ParameterCompiledValue="'7560129'" ParameterRuntimeValue="'7560129'" />

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>