Deadlock on update command

Question

Deadlock on update command

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

bugg SSCertifiable Points: 7567 More actions · Answer 1

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>

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

This article linked in my sig explains in detail how to post execution plans. They're active - .png graphics are not.

Right-click on the execution plan tab, select "Save Execution Plan As" and post as an attachment.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bugg SSCertifiable Points: 7567 More actions · Answer 3

Here is the deadlock info from the SQL log, unfirttunaltey it includes both deadlock flags. Due to my monitoring software

Date,Source,Severity,Message

03/25/2013 10:35:35,spid34s,Unknown,waiter id=processd4d708

03/25/2013 10:35:35,spid34s,Unknown,waiter-list

03/25/2013 10:35:35,spid34s,Unknown,owner id=process4fa0508

03/25/2013 10:35:35,spid34s,Unknown,owner-list

03/25/2013 10:35:35,spid34s,Unknown,exchangeEvent id=Pipe155556480 WaitType=e_waitPipeGetRow nodeId=3

03/25/2013 10:35:35,spid34s,Unknown,waiter id=process689b88 mode=IX requestType=wait

03/25/2013 10:35:35,spid34s,Unknown,waiter-list

03/25/2013 10:35:35,spid34s,Unknown,owner id=processd4d708 mode=S

03/25/2013 10:35:35,spid34s,Unknown,owner-list

03/25/2013 10:35:35,spid34s,Unknown,pagelock fileid=1 pageid=4013239 dbid=9 objectname=DBXXXX.dbo.orderha id=lock22ca4f380 mode=S associatedObjectId=72057609113042944

03/25/2013 10:35:35,spid34s,Unknown,waiter id=process4fa0508 mode=S requestType=wait

03/25/2013 10:35:35,spid34s,Unknown,waiter-list

03/25/2013 10:35:35,spid34s,Unknown,owner id=process689b88 mode=X

03/25/2013 10:35:35,spid34s,Unknown,owner-list

03/25/2013 10:35:35,spid34s,Unknown,keylock hobtid=72057602492792832 dbid=9 objectname=DBXXXX.dbo.orderha indexname =PK_orderhaid=lock2e17cd700 mode=X associatedObjectId=72057602492792832

03/25/2013 10:35:35,spid34s,Unknown,resource-list

03/25/2013 10:35:35,spid34s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)

03/25/2013 10:35:35,spid34s,Unknown,inputbuf

03/25/2013 10:35:35,spid34s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)

03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000b124a93432a05dac02c98dd52c4bc4a8623e4c71

03/25/2013 10:35:35,spid34s,Unknown,executionStack

03/25/2013 10:35:35,spid34s,Unknown,process id=processd4d708 taskpriority=0 logused=10000 waittime=2656 schedulerid=3 kpid=2888 status=suspended spid=52 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-03-25T10:35:33.040 lastbatchcompleted=2013-03-25T10:35:33.040 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=37813851141 currentdb=9 lockTimeout=4294967295

03/25/2013 10:35:35,spid34s,Unknown,update orderha set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7592810' and orderha=1

03/25/2013 10:35:35,spid34s,Unknown,inputbuf

03/25/2013 10:35:35,spid34s,Unknown,update orderha set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7592810' and orderha=1

03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000003349709cef9f8e441641eabcec39ed43894f7ae

03/25/2013 10:35:35,spid34s,Unknown,(@1 int<c/>@2 int<c/>@3 varchar(8000)<c/>@4 tinyint)UPDATE [orderha] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [orderha]%%=%%@4

03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a

03/25/2013 10:35:35,spid34s,Unknown,executionStack

03/25/2013 10:35:35,spid34s,Unknown,process id=process689b88 taskpriority=0 logused=292 waitresource=PAGE: 9:1:4013239 waittime=2809 ownerId=37813851204 transactionname=UPDATE lasttranstarted=2013-03-25T10:35:33.113 XDES=0x148857970 lockMode=IX schedulerid=1 kpid=6156 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-03-25T10:35:33.107 lastbatchcompleted=2013-03-25T10:35:33.107 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=37813851204 currentdb=9 lockTimeout=4294967295

03/25/2013 10:35:35,spid34s,Unknown,inputbuf

03/25/2013 10:35:35,spid34s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)

03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000b124a93432a05dac02c98dd52c4bc4a8623e4c71

03/25/2013 10:35:35,spid34s,Unknown,executionStack

03/25/2013 10:35:35,spid34s,Unknown,process id=process4fa0508 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (f9008448af4c) waittime=2709 ownerId=37813851141 transactionname=SELECT lasttranstarted=2013-03-25T10:35:33.080 XDES=0x1b7502b60 lockMode=S schedulerid=14 kpid=236 status=suspended spid=52 sbid=0 ecid=13 priority=0 trancount=0 lastbatchstarted=2013-03-25T10:35:33.040 lastbatchcompleted=2013-03-25T10:35:33.040 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 isolationlevel=read committed (2) xactid=37813851141 currentdb=9 lockTimeout=4294967295

03/25/2013 10:35:35,spid34s,Unknown,process-list

03/25/2013 10:35:35,spid34s,Unknown,deadlock victim=process4fa0508

03/25/2013 10:35:35,spid34s,Unknown,deadlock-list

03/25/2013 10:35:35,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001B7502B60 Mode: S SPID:52 BatchID:0 ECID:13 TaskProxy:(0x00000001243D7BD0) Value:0xb6996cc0 Cost:(0/0)

03/25/2013 10:35:35,spid5s,Unknown,Victim Resource Owner:

03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/25/2013 10:35:35,spid5s,Unknown,ResType:ExchangeId Stype:'AND' SPID:52 BatchID:0 ECID:0 TaskProxy:(0x000000023373C510) Value:0xd4d708 Cost:(0/10000)

03/25/2013 10:35:35,spid5s,Unknown,Port: 0x0000000155556480 Xid Slot: 0<c/> Wait Slot: -1<c/> Task: 0x0000000000D4D708<c/> (Coordinator)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

03/25/2013 10:35:35,spid5s,Unknown,Node:3

03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/25/2013 10:35:35,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000148857970 Mode: IX SPID:59 BatchID:0 ECID:0 TaskProxy:(0x000000014A4F0538) Value:0xcf1395c0 Cost:(0/292)

03/25/2013 10:35:35,spid5s,Unknown,Requested by:

03/25/2013 10:35:35,spid5s,Unknown,Input Buf: Language Event: Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)

03/25/2013 10:35:35,spid5s,Unknown,SPID: 52 ECID: 0 Statement Type: SELECT Line #: 1

03/25/2013 10:35:35,spid5s,Unknown,Owner:0x0000000224960B80 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:52 ECID:0 XactLockInfo: 0x00000000800A5940

03/25/2013 10:35:35,spid5s,Unknown,Grant List 0:

03/25/2013 10:35:35,spid5s,Unknown,PAGE: 9:1:4013239 CleanCnt:2 Mode:S Flags: 0x3

03/25/2013 10:35:35,spid5s,Unknown,Node:2

03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/25/2013 10:35:35,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001B7502B60 Mode: S SPID:52 BatchID:0 ECID:13 TaskProxy:(0x00000001243D7BD0) Value:0xb6996cc0 Cost:(0/0)

03/25/2013 10:35:35,spid5s,Unknown,Requested by:

03/25/2013 10:35:35,spid5s,Unknown,Input Buf: Language Event: update orderha set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7592810' and orderha=1

03/25/2013 10:35:35,spid5s,Unknown,SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1

03/25/2013 10:35:35,spid5s,Unknown,Owner:0x00000001D6758340 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000001488579B0

03/25/2013 10:35:35,spid5s,Unknown,Grant List 0:

03/25/2013 10:35:35,spid5s,Unknown,KEY: 9:72057602492792832 (f9008448af4c) CleanCnt:2 Mode:X Flags: 0x1

03/25/2013 10:35:35,spid5s,Unknown,Node:1

03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/25/2013 10:35:35,spid5s,Unknown,Wait-for graph

03/25/2013 10:35:35,spid5s,Unknown,Deadlock encountered .... Printing deadlock information

bugg SSCertifiable Points: 7567 More actions · Answer 4

ChrisM@Work (3/25/2013)
This article linked in my sig explains in detail how to post execution plans. They're active - .png graphics are not.
Right-click on the execution plan tab, select "Save Execution Plan As" and post as an attachment.

Now attached plan. let me know if you need anything else appreciate your help on this.

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

OK got it. You need to change two indexes.

1. Include orderhash and status in index IX_SessionID, like this:

CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[orderha]

([sessionid] ASC, OrderHash ASC) INCLUDE (Status)

2. Include printref in index IX_suspect_status_rsn_shipopt, like this:

CREATE NONCLUSTERED INDEX [IX_suspect_status_rsn_shipopt] ON [dbo].[orderha]

([suspectorder] ASC,

[status] ASC,

[rsn] ASC,

[shipopt] ASC) INCLUDE (PrintRef)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

bugg (3/25/2013)
ChrisM@Work (3/25/2013)
This article linked in my sig explains in detail how to post execution plans. They're active - .png graphics are not.
Right-click on the execution plan tab, select "Save Execution Plan As" and post as an attachment.
Now attached plan. let me know if you need anything else appreciate your help on this.
thanks

You're welcome.

Do you have some check constraints on this table, by any chance? Something affecting the value of [status]? It would be interesting to see the scripts for those.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bugg SSCertifiable Points: 7567 More actions · Answer 7

ChrisM@Work (3/25/2013)
OK got it. You need to change two indexes.
1. Include orderhash and status in index IX_SessionID, like this:
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[orderha]
([sessionid] ASC, OrderHash ASC) INCLUDE (Status)
2. Include printref in index IX_suspect_status_rsn_shipopt, like this:
CREATE NONCLUSTERED INDEX [IX_suspect_status_rsn_shipopt] ON [dbo].[orderha]
([suspectorder] ASC,
[status] ASC,
[rsn] ASC,
[shipopt] ASC) INCLUDE (PrintRef)

Thanks Chris, when I get a chance I'll try out these new indexes and report back to you 🙂

bugg SSCertifiable Points: 7567 More actions · Answer 8

bugg (3/25/2013)
ChrisM@Work (3/25/2013)
OK got it. You need to change two indexes.
1. Include orderhash and status in index IX_SessionID, like this:
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[orderha]
([sessionid] ASC, OrderHash ASC) INCLUDE (Status)
2. Include printref in index IX_suspect_status_rsn_shipopt, like this:
CREATE NONCLUSTERED INDEX [IX_suspect_status_rsn_shipopt] ON [dbo].[orderha]
([suspectorder] ASC,
[status] ASC,
[rsn] ASC,
[shipopt] ASC) INCLUDE (PrintRef)
Thanks Chris, when I get a chance I'll try out these new indexes and report back to you 🙂

There are a load of unnecessary check constraints that probably need dropping.

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_sessionid] DEFAULT ('') FOR [sessionid]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_orderha] DEFAULT ((0)) FOR [orderha]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_perc] DEFAULT ((0)) FOR [perc]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_userid] DEFAULT ((0)) FOR [userid]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_hashtotal] DEFAULT ((0)) FOR [hashtotal]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_dateadded] DEFAULT (getdate()) FOR [dateadded]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_shipped] DEFAULT ((0)) FOR [shipped]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_shipping] DEFAULT ((0)) FOR [status]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_suspectorder] DEFAULT ((0)) FOR [suspectorder]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_shippingweight] DEFAULT ((0)) FOR [shippingweight]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_discount] DEFAULT ((0)) FOR [discount]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_shiptotal] DEFAULT ((0)) FOR [shiptotal]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_shipopt] DEFAULT ((0)) FOR [shipopt]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_rd] DEFAULT ((0)) FOR [rd]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_giftwrap] DEFAULT ((0)) FOR [giftwrap]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_bay] DEFAULT ((0)) FOR [bay]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_instock] DEFAULT ((0)) FOR [instock]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_beenupdated] DEFAULT ((0)) FOR [beenupdated]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_vatamount] DEFAULT ((0)) FOR [vatamount]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_prodname] DEFAULT ('') FOR [prodname]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_source] DEFAULT ('IC') FOR [source]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_externalsessionid] DEFAULT ('') FOR [externalsessionid]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_postalrsn] DEFAULT ((0)) FOR [postalrsn]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_printref] DEFAULT ('') FOR [printref]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_sdref] DEFAULT ('') FOR [sdref]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_oos] DEFAULT ((0)) FOR [oos]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_sdreflabel] DEFAULT ('') FOR [sdreflabel]

GO

ALTER TABLE [dbo].[orderha] ADD CONSTRAINT [DF_orderha_bin] DEFAULT ('') FOR [bin]

GO

bugg SSCertifiable Points: 7567 More actions · Answer 9

ChrisM@Work (3/25/2013)
OK got it. You need to change two indexes.
1. Include orderhash and status in index IX_SessionID, like this:
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[orderha]
([sessionid] ASC, OrderHash ASC) INCLUDE (Status)
2. Include printref in index IX_suspect_status_rsn_shipopt, like this:
CREATE NONCLUSTERED INDEX [IX_suspect_status_rsn_shipopt] ON [dbo].[orderha]
([suspectorder] ASC,
[status] ASC,
[rsn] ASC,
[shipopt] ASC) INCLUDE (PrintRef)

Hi Chris, Unfortuantely im still getting the deadlocks. Not as many but they are still coming through 2/3 times a day. Down from double that though 🙂

I think i need to include 'shipdate' with the 'status' in IX_sessionid

03/30/2013 13:46:10,spid25s,Unknown,waiter id=process4f38988

03/30/2013 13:46:10,spid25s,Unknown,waiter-list

03/30/2013 13:46:10,spid25s,Unknown,owner id=process4f38bc8

03/30/2013 13:46:10,spid25s,Unknown,owner-list

03/30/2013 13:46:10,spid25s,Unknown,exchangeEvent id=Pipe2c4dd0480 WaitType=e_waitPipeGetRow nodeId=3

03/30/2013 13:46:10,spid25s,Unknown,waiter id=process6894c8 mode=IX requestType=convert

03/30/2013 13:46:10,spid25s,Unknown,waiter-list

03/30/2013 13:46:10,spid25s,Unknown,owner id=process4f38988 mode=S

03/30/2013 13:46:10,spid25s,Unknown,owner-list

03/30/2013 13:46:10,spid25s,Unknown,pagelock fileid=1 pageid=4326436 dbid=7 objectname=DBXXXX.dbo.orderhash id=lock16c4bda00 mode=SIU associatedObjectId=72057610676011008

03/30/2013 13:46:10,spid25s,Unknown,waiter id=process4f38bc8 mode=S requestType=wait

03/30/2013 13:46:10,spid25s,Unknown,waiter-list

03/30/2013 13:46:10,spid25s,Unknown,owner id=process6894c8 mode=X

03/30/2013 13:46:10,spid25s,Unknown,owner-list

03/30/2013 13:46:10,spid25s,Unknown,keylock hobtid=72057602492792832 dbid=7 objectname=DBXXXX.dbo.orderhash indexname=PK_orderhash id=lockc2f5af00 mode=X associatedObjectId=72057602492792832

03/30/2013 13:46:10,spid25s,Unknown,resource-list

03/30/2013 13:46:10,spid25s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderhash where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('UK1') and batch=orderhash.printref)

03/30/2013 13:46:10,spid25s,Unknown,inputbuf

03/30/2013 13:46:10,spid25s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderhash where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('UK1') and batch=orderhash.printref)

03/30/2013 13:46:10,spid25s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000018e398116843fa6de41dc8a1f11bf4a75d347fdb

03/30/2013 13:46:10,spid25s,Unknown,executionStack

03/30/2013 13:46:10,spid25s,Unknown,process id=process4f38988 taskpriority=0 logused=10000 waittime=3649 schedulerid=10 kpid=7668 status=suspended spid=65 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-03-30T13:46:06.610 lastbatchcompleted=2013-03-30T13:46:06.610 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=39510680523 currentdb=9 lockTimeout=4294967295

03/30/2013 13:46:10,spid25s,Unknown,update orderhash set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7681484' and orderhash=1

03/30/2013 13:46:10,spid25s,Unknown,inputbuf

03/30/2013 13:46:10,spid25s,Unknown,update orderhash set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7681484' and orderhash=1

03/30/2013 13:46:10,spid25s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006fdaf306adf2ff10652c70fc17ac0e18688ecad8

03/30/2013 13:46:10,spid25s,Unknown,UPDATE [orderhash] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [orderhash]%%=%%@4

03/30/2013 13:46:10,spid25s,Unknown,frame procname=adhoc line=1 stmtstart=86 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a

03/30/2013 13:46:10,spid25s,Unknown,executionStack

03/30/2013 13:46:10,spid25s,Unknown,process id=process6894c8 taskpriority=0 logused=624 waitresource=PAGE: 9:1:4326436 waittime=3819 ownerId=39510680857 transactionname=UPDATE lasttranstarted=2013-03-30T13:46:06.800 XDES=0x1ff2dc3b0 lockMode=IX schedulerid=1 kpid=5412 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-03-30T13:46:06.800 lastbatchcompleted=2013-03-30T13:46:06.730 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=39510680857 currentdb=9 lockTimeout=4294967295

03/30/2013 13:46:10,spid25s,Unknown,inputbuf

03/30/2013 13:46:10,spid25s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderhash where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('UK1') and batch=orderhash.printref)

03/30/2013 13:46:10,spid25s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000018e398116843fa6de41dc8a1f11bf4a75d347fdb

03/30/2013 13:46:10,spid25s,Unknown,executionStack

03/30/2013 13:46:10,spid25s,Unknown,process id=process4f38bc8 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (41000f1dbb8f) waittime=3765 ownerId=39510680523 transactionname=SELECT lasttranstarted=2013-03-30T13:46:06.657 XDES=0x86c43cf0 lockMode=S schedulerid=10 kpid=5248 status=suspended spid=65 sbid=0 ecid=7 priority=0 trancount=0 lastbatchstarted=2013-03-30T13:46:06.610 lastbatchcompleted=2013-03-30T13:46:06.610 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 isolationlevel=read committed (2) xactid=39510680523 currentdb=9 lockTimeout=4294967295

03/30/2013 13:46:10,spid25s,Unknown,process-list

03/30/2013 13:46:10,spid25s,Unknown,deadlock victim=process4f38bc8

03/30/2013 13:46:10,spid25s,Unknown,deadlock-list

03/30/2013 13:46:10,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000086C43CF0 Mode: S SPID:65 BatchID:0 ECID:7 TaskProxy:(0x000000035F2CF930) Value:0xa6d30f00 Cost:(0/0)

03/30/2013 13:46:10,spid5s,Unknown,Victim Resource Owner:

03/30/2013 13:46:10,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/30/2013 13:46:10,spid5s,Unknown,ResType:ExchangeId Stype:'AND' SPID:65 BatchID:0 ECID:0 TaskProxy:(0x00000001CFE86510) Value:0x4f38988 Cost:(0/10000)

03/30/2013 13:46:10,spid5s,Unknown,Port: 0x00000002C4DD0480 Xid Slot: 0<c/> Wait Slot: -1<c/> Task: 0x0000000004F38988<c/> (Coordinator)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

03/30/2013 13:46:10,spid5s,Unknown,Node:3

03/30/2013 13:46:10,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/30/2013 13:46:10,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001FF2DC3B0 Mode: IX SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000002145BE538) Value:0xbfea9b40 Cost:(0/624)

03/30/2013 13:46:10,spid5s,Unknown,Requested by:

03/30/2013 13:46:10,spid5s,Unknown,Input Buf: Language Event: Select isnull(count(rsn)<c/>0) as value from orderhash where status < 3 and <nl/>exists (select rsn from printbatches where shippingloc in ('UK1') and batch=orderhash.printref)

03/30/2013 13:46:10,spid5s,Unknown,SPID: 65 ECID: 0 Statement Type: SELECT Line #: 1

03/30/2013 13:46:10,spid5s,Unknown,Owner:0x00000001AE252D00 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:65 ECID:0 XactLockInfo: 0x0000000080025AB0

03/30/2013 13:46:10,spid5s,Unknown,Grant List 2:

03/30/2013 13:46:10,spid5s,Unknown,Grant List 0:

03/30/2013 13:46:10,spid5s,Unknown,PAGE: 9:1:4326436 CleanCnt:2 Mode:SIU Flags: 0x3

03/30/2013 13:46:10,spid5s,Unknown,Node:2

03/30/2013 13:46:10,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/30/2013 13:46:10,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000086C43CF0 Mode: S SPID:65 BatchID:0 ECID:7 TaskProxy:(0x000000035F2CF930) Value:0xa6d30f00 Cost:(0/0)

03/30/2013 13:46:10,spid5s,Unknown,Requested by:

03/30/2013 13:46:10,spid5s,Unknown,Input Buf: Language Event: update orderhash set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7681484' and orderhash=1

03/30/2013 13:46:10,spid5s,Unknown,SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1

03/30/2013 13:46:10,spid5s,Unknown,Owner:0x000000025354BD80 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:63 ECID:0 XactLockInfo: 0x00000001FF2DC3F0

03/30/2013 13:46:10,spid5s,Unknown,Grant List 0:

03/30/2013 13:46:10,spid5s,Unknown,KEY: 9:72057602492792832 (41000f1dbb8f) CleanCnt:2 Mode:X Flags: 0x1

03/30/2013 13:46:10,spid5s,Unknown,Node:1

03/30/2013 13:46:10,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

03/30/2013 13:46:10,spid5s,Unknown,Wait-for graph

03/30/2013 13:46:10,spid5s,Unknown,Deadlock encountered .... Printing deadlock information

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

The SELECT query is no longer using index IX_suspect_status_rsn_shipopt, it's now using IX_sessionID, same as the UDATE query, because of the changes made in the last iteration. Using IX_sessionID, the SELECT now requires a key lookup to collect printref. This column was added to IX_suspect_status_rsn_shipopt in the last iteration to remove the key lookup, but wasn't added to IX_sessionID because it wasn't required.

If you can prevent this key lookup, you remove one half of the resource pair (now) causing the deadlock. Add printref as an INCLUDE column to index IX_sessionID. So long as the SELECT query uses this index after the change, it will only have one resource shared with the UPDATE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bugg SSCertifiable Points: 7567 More actions · Answer 11

ChrisM@Work (4/2/2013)
The SELECT query is no longer using index IX_suspect_status_rsn_shipopt, it's now using IX_sessionID, same as the UDATE query, because of the changes made in the last iteration. Using IX_sessionID, the SELECT now requires a key lookup to collect printref. This column was added to IX_suspect_status_rsn_shipopt in the last iteration to remove the key lookup, but wasn't added to IX_sessionID because it wasn't required.
If you can prevent this key lookup, you remove one half of the resource pair (now) causing the deadlock. Add printref as an INCLUDE column to index IX_sessionID. So long as the SELECT query uses this index after the change, it will only have one resource shared with the UPDATE.

Hi Chris,

Thanks for all you help on this but im still getting deadlocks 🙁

Its a different select that is being killed off though 🙂 .. I've attached the sqlplans

select count(orderha.rsn) as outstanding

, DATEDIFF(day, GETDATE(), printbatches.datecreated) as frompd

from printbatches inner join orderha on orderha.printref=printbatches.batch

where orderha.status<3 and orderha.oos=0

group by DATEDIFF(day, GETDATE(), printbatches.datecreated)

order by DATEDIFF(day, GETDATE(), printbatches.datecreated) desc

Will creating this index help?

GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [orderha] ([oos],[status])

INCLUDE ([printref])

GO

DEADLOCK info:

04/19/2013 13:56:51,spid27s,Unknown,exchangeEvent id=Port803e4400 WaitType=e_waitPortOpen nodeId=4

04/19/2013 13:56:51,spid27s,Unknown,waiter id=process689048 mode=IX requestType=wait

04/19/2013 13:56:51,spid27s,Unknown,waiter-list

04/19/2013 13:56:51,spid27s,Unknown,owner id=process4fa1dc8 mode=S

04/19/2013 13:56:51,spid27s,Unknown,owner-list

04/19/2013 13:56:51,spid27s,Unknown,pagelock fileid=1 pageid=3294240 dbid=X objectname=DBXXXX.dbo.orderha id=lock2d19d0100 mode=S associatedObjectId=72057610676142080

04/19/2013 13:56:51,spid27s,Unknown,waiter id=processd33288 mode=S requestType=wait

04/19/2013 13:56:51,spid27s,Unknown,waiter-list

04/19/2013 13:56:51,spid27s,Unknown,owner id=process689048 mode=X

04/19/2013 13:56:51,spid27s,Unknown,owner-list

04/19/2013 13:56:51,spid27s,Unknown,keylock hobtid=72057602492792832 dbid=X objectname=DBXXXX.dbo.orderha indexname=PK_orderha id=lock256869a80 mode=X associatedObjectId=72057602492792832

04/19/2013 13:56:51,spid27s,Unknown,resource-list

04/19/2013 13:56:51,spid27s,Unknown,inputbuf

04/19/2013 13:56:51,spid27s,Unknown,select count(orderha.rsn) as outstanding<c/> DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) as frompd<nl/>from printbatches <nl/>inner join orderha on (orderha.printref=printbatches.batch and orderha.status<3 and orderha.oos=0)<nl/>group by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) <nl/>order by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) desc

04/19/2013 13:56:51,spid27s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000001d53f531a71b0059f1d525f6d990b723bc16a607

04/19/2013 13:56:51,spid27s,Unknown,executionStack

04/19/2013 13:56:51,spid27s,Unknown,process id=processd4d4c8 taskpriority=0 logused=10000 waittime=3779 schedulerid=3 kpid=8152 status=suspended spid=60 sbid=0 ecid=17 priority=0 trancount=0 lastbatchstarted=2013-04-19T13:56:48.050 lastbatchcompleted=2013-04-19T13:56:48.047 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 isolationlevel=read committed (2) xactid=45974145624 currentdb=9 lockTimeout=4294967295

04/19/2013 13:56:51,spid27s,Unknown,inputbuf

04/19/2013 13:56:51,spid27s,Unknown,select count(orderha.rsn) as outstanding<c/> DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) as frompd<nl/>from printbatches <nl/>inner join orderha on (orderha.printref=printbatches.batch and orderha.status<3 and orderha.oos=0)<nl/>group by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) <nl/>order by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) desc

04/19/2013 13:56:51,spid27s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000001d53f531a71b0059f1d525f6d990b723bc16a607

04/19/2013 13:56:51,spid27s,Unknown,executionStack

04/19/2013 13:56:51,spid27s,Unknown,process id=processd4c748 taskpriority=0 logused=10000 waittime=3779 schedulerid=3 kpid=7132 status=suspended spid=60 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2013-04-19T13:56:48.050 lastbatchcompleted=2013-04-19T13:56:48.047 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 isolationlevel=read committed (2) xactid=45974145624 currentdb=9 lockTimeout=4294967295

04/19/2013 13:56:51,spid27s,Unknown,select count(orderha.rsn) as outstanding<c/> DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) as frompd<nl/>from printbatches <nl/>inner join orderha on (orderha.printref=printbatches.batch and orderha.status<3 and orderha.oos=0)<nl/>group by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) <nl/>order by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) desc

04/19/2013 13:56:51,spid27s,Unknown,inputbuf

04/19/2013 13:56:51,spid27s,Unknown,select count(orderha.rsn) as outstanding<c/> DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) as frompd<nl/>from printbatches <nl/>inner join orderha on (orderha.printref=printbatches.batch and orderha.status<3 and orderha.oos=0)<nl/>group by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) <nl/>order by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) desc

04/19/2013 13:56:51,spid27s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000001d53f531a71b0059f1d525f6d990b723bc16a607

04/19/2013 13:56:51,spid27s,Unknown,executionStack

04/19/2013 13:56:51,spid27s,Unknown,process id=process4fa1dc8 taskpriority=0 logused=10000 waittime=3853 schedulerid=14 kpid=5776 status=suspended spid=60 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-04-19T13:56:48.050 lastbatchcompleted=2013-04-19T13:56:48.047 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=45974145624 currentdb=9 lockTimeout=4294967295

04/19/2013 13:56:51,spid27s,Unknown,update orderha set status=2 where sessionid='7832820' and orderha=1

04/19/2013 13:56:51,spid27s,Unknown,inputbuf

04/19/2013 13:56:51,spid27s,Unknown,update orderha set status=2 where sessionid='7832820' and orderha=1

04/19/2013 13:56:51,spid27s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000041864e27d1973de4cfc1b7b494611e0458b28084

04/19/2013 13:56:51,spid27s,Unknown,UPDATE [orderha] set [status] = @1 WHERE [sessionid]%%=%%@2 AND [orderha]%%=%%@3

04/19/2013 13:56:51,spid27s,Unknown,frame procname=adhoc line=1 stmtstart=72 sqlhandle=0x02000000081af51b83be73b58c6ce121c73eb03ecbb156b1

04/19/2013 13:56:51,spid27s,Unknown,executionStack

04/19/2013 13:56:51,spid27s,Unknown,process id=process689048 taskpriority=0 logused=220 waitresource=PAGE: 9:1:3294240 waittime=3820 ownerId=45974145846 transactionname=UPDATE lasttranstarted=2013-04-19T13:56:48.080 XDES=0x2c34463b0 lockMode=IX schedulerid=1 kpid=3696 status=suspended spid=66 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-04-19T13:56:48.080 lastbatchcompleted=2013-04-19T13:56:48.080 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=45974145846 currentdb=9 lockTimeout=4294967295

04/19/2013 13:56:51,spid27s,Unknown,inputbuf

04/19/2013 13:56:51,spid27s,Unknown,select count(orderha.rsn) as outstanding<c/> DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) as frompd<nl/>from printbatches <nl/>inner join orderha on (orderha.printref=printbatches.batch and orderha.status<3 and orderha.oos=0)<nl/>group by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) <nl/>order by DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) desc

04/19/2013 13:56:51,spid27s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000001d53f531a71b0059f1d525f6d990b723bc16a607

04/19/2013 13:56:51,spid27s,Unknown,executionStack

04/19/2013 13:56:51,spid27s,Unknown,process id=processd33288 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (a700039e7d5b) waittime=3788 ownerId=45974145624 transactionname=SELECT lasttranstarted=2013-04-19T13:56:48.050 XDES=0x2be862450 lockMode=S schedulerid=2 kpid=6476 status=suspended spid=60 sbid=0 ecid=21 priority=0 trancount=0 lastbatchstarted=2013-04-19T13:56:48.050 lastbatchcompleted=2013-04-19T13:56:48.047 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 isolationlevel=read committed (2) xactid=45974145624 currentdb=9 lockTimeout=4294967295

04/19/2013 13:56:51,spid27s,Unknown,process-list

04/19/2013 13:56:51,spid27s,Unknown,deadlock victim=processd33288

04/19/2013 13:56:51,spid27s,Unknown,deadlock-list

04/19/2013 13:56:51,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000002BE862450 Mode: S SPID:60 BatchID:0 ECID:21 TaskProxy:(0x00000001EC9BE080) Value:0xe2c1dd80 Cost:(0/0)

04/19/2013 13:56:51,spid5s,Unknown,Victim Resource Owner:

04/19/2013 13:56:51,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

04/19/2013 13:56:51,spid5s,Unknown,Input Buf: No Event:

04/19/2013 13:56:51,spid5s,Unknown,SPID: 60 ECID: 17 Statement Type: SELECT Line #: 1

04/19/2013 13:56:51,spid5s,Unknown,ResType:ExchangeId Stype:'AND' SPID:60 BatchID:0 ECID:17 TaskProxy:(0x00000001A4C7BE10) Value:0xd4d4c8 Cost:(0/10000)

04/19/2013 13:56:51,spid5s,Unknown,Port: 0x00000001D3E0B500 Xid Slot: 0<c/> Wait Slot: -1<c/> Task: 0x0000000000D4D4C8<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

04/19/2013 13:56:51,spid5s,Unknown,Node:5

04/19/2013 13:56:51,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

04/19/2013 13:56:51,spid5s,Unknown,Input Buf: No Event:

04/19/2013 13:56:51,spid5s,Unknown,SPID: 60 ECID: 1 Statement Type: SELECT Line #: 1

04/19/2013 13:56:51,spid5s,Unknown,ResType:ExchangeId Stype:'AND' SPID:60 BatchID:0 ECID:1 TaskProxy:(0x00000001A4C7B810) Value:0xd4c748 Cost:(0/10000)

04/19/2013 13:56:51,spid5s,Unknown,Port: 0x00000001D3E0A880 Xid Slot: 0<c/> Wait Slot: -1<c/> Task: 0x0000000000D4C748<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

04/19/2013 13:56:51,spid5s,Unknown,Node:4

04/19/2013 13:56:51,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

04/19/2013 13:56:51,spid5s,Unknown,ResType:ExchangeId Stype:'AND' SPID:60 BatchID:0 ECID:0 TaskProxy:(0x00000002830E4510) Value:0x4fa1dc8 Cost:(0/10000)

04/19/2013 13:56:51,spid5s,Unknown,Port: 0x00000000803E4400 Xid Slot: 0<c/> Wait Slot: 1<c/> Task: 0x0000000004FA1DC8<c/> (Coordinator)<c/> Exchange Wait Type: e_waitPortOpen<c/> Merging: 0

04/19/2013 13:56:51,spid5s,Unknown,Node:3

04/19/2013 13:56:51,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

04/19/2013 13:56:51,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000002C34463B0 Mode: IX SPID:66 BatchID:0 ECID:0 TaskProxy:(0x00000000EF2BC538) Value:0x46099a00 Cost:(0/220)

04/19/2013 13:56:51,spid5s,Unknown,Requested by:

04/19/2013 13:56:51,spid5s,Unknown,Input Buf: Language Event: select count(orderha.rsn) as outstanding<c/> DATEDIFF(day<c/> GETDATE()<c/> printbatches.datecreated) as frompd<nl/>from printbatches <nl/>inner join orderha on (orderha.printref=printbatches.batch and orderha.status<3 and orderha.oos=0)<nl/>group by DATEDIFF(day<c/> G

04/19/2013 13:56:51,spid5s,Unknown,SPID: 60 ECID: 0 Statement Type: SELECT Line #: 1

04/19/2013 13:56:51,spid5s,Unknown,Owner:0x000000021361FB00 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:60 ECID:0 XactLockInfo: 0x00000002512BADB0

04/19/2013 13:56:51,spid5s,Unknown,Grant List 3:

04/19/2013 13:56:51,spid5s,Unknown,PAGE: 9:1:3294240 CleanCnt:2 Mode:S Flags: 0x3

04/19/2013 13:56:51,spid5s,Unknown,Node:2

04/19/2013 13:56:51,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

04/19/2013 13:56:51,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000002BE862450 Mode: S SPID:60 BatchID:0 ECID:21 TaskProxy:(0x00000001EC9BE080) Value:0xe2c1dd80 Cost:(0/0)

04/19/2013 13:56:51,spid5s,Unknown,Requested by:

04/19/2013 13:56:51,spid5s,Unknown,Input Buf: Language Event: update orderha set status=2 where sessionid='7832820' and orderha=1

04/19/2013 13:56:51,spid5s,Unknown,SPID: 66 ECID: 0 Statement Type: UPDATE Line #: 1

04/19/2013 13:56:51,spid5s,Unknown,Owner:0x000000027A643900 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:66 ECID:0 XactLockInfo: 0x00000002C34463F0

04/19/2013 13:56:51,spid5s,Unknown,Grant List 0:

04/19/2013 13:56:51,spid5s,Unknown,KEY: 9:72057602492792832 (a700039e7d5b) CleanCnt:2 Mode:X Flags: 0x1

04/19/2013 13:56:51,spid5s,Unknown,Node:1

04/19/2013 13:56:51,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

04/19/2013 13:56:51,spid5s,Unknown,Wait-for graph

04/19/2013 13:56:51,spid5s,Unknown,Deadlock encountered .... Printing deadlock information

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

Since you are getting deadlocks between a select and an update, you can probably eliminate the problem by setting the database to use row version isolation (Read Committed Snapshot)

use master;

alter database [MyDb] set allow_snapshot_isolation on;

-- Will break all current database connections,

-- so run when the database is not being used.

alter database [MyDb] set read_committed_snapshot on with rollback immediate;

You should read about row version isolation before you make the change.

Using Row Versioning-based Isolation Levels

http://msdn.microsoft.com/en-us/library/ms179599.aspx

bugg SSCertifiable Points: 7567 More actions · Answer 13

Michael Valentine Jones (4/19/2013)
Since you are getting deadlocks between a select and an update, you can probably eliminate the problem by setting the database to use row version isolation (Read Committed Snapshot)
use master;
alter database [MyDb] set allow_snapshot_isolation on;
-- Will break all current database connections,
-- so run when the database is not being used.
alter database [MyDb] set read_committed_snapshot on with rollback immediate;
You should read about row version isolation before you make the change.
Using Row Versioning-based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms179599.aspx

Not sure if i want to go down that route yet. It affects performance and there are probably other areas impacted that I don't know of.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Instead of giving us pieces, how about the whole pie.

Can you show us how these queries are being executed? Are they a part of a singe stored procedure, or are they from competing executions of the same procedure or different procedures?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 15

bugg (4/19/2013)
Michael Valentine Jones (4/19/2013)
Since you are getting deadlocks between a select and an update, you can probably eliminate the problem by setting the database to use row version isolation (Read Committed Snapshot)
use master;
alter database [MyDb] set allow_snapshot_isolation on;
-- Will break all current database connections,
-- so run when the database is not being used.
alter database [MyDb] set read_committed_snapshot on with rollback immediate;
You should read about row version isolation before you make the change.
Using Row Versioning-based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms179599.aspx
Not sure if i want to go down that route yet. It affects performance and there are probably other areas impacted that I don't know of.

I have never seem any negative impact on performance from row version isolation. It may help performance by eliminating most blocking of select queries.