Deadlock issue with "x" lock trying to convert to "u" lock

• jlj.hook

  SSC Veteran

  Points: 222

  More actions

  December 10, 2015 at 7:02 am

  #323371

  We are having problems with high concurrency spikes(end of month invoicing) in heavily accessed and modified index.

  Before deadlock occurs rows participating in deadlock are already updated once so they already have X locks. This second update causes deadlocks when X lock is converted to U lock. Should I use lock hint "xlock" in the update statement?

  I understood that this would just keep X lock throughout transaction?

  deadlock-list

  deadlock victim=process19a1b22108

  process-list

  process id=process19a1b22108 taskpriority=0 logused=16536 waitresource=KEY: 11:72057602628386816 (ed2643dc91cf) waittime=4628 ownerId=1454199501 transactionname=user_transaction lasttranstarted=2015-12-02T11:28:46.953 XDES=0x131650c3b0 lockMode=U schedulerid=16 kpid=13372 status=suspended spid=52 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-12-02T11:28:47.033 lastbatchcompleted=2015-12-02T11:28:47.033 lastattention=2015-12-02T11:28:47.030 clientapp=.Net SqlClient Data Provider hostname=315728-WEB1 hostpid=11640 loginname=user isolationlevel=read committed (2) xactid=1454199501 currentdb=11 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

  executionStack

  frame procname=adhoc line=1 stmtstart=150 stmtend=542 sqlhandle=0x02000000dac7021cffde957fbfc462b546f750d73410c93e0000000000000000000000000000000000000000

  unknown

  frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

  unknown

  inputbuf

  (@HoursIn [EntityTableType] READONLY,@InvoiceRowID int,@OrganizationID int)update [Table] set [Table].InvoiceRowID = @InvoiceRowID From [Table] Inner Join @TablesIn as TableIn On TableIn.ID=[Table].ID where [Table].OrganizationID = @OrganizationID and [Table].InvoiceID is not null

  process id=process1f700e48c8 taskpriority=0 logused=195400 waitresource=KEY: 11:72057602628386816 (3091caa6e4a6) waittime=5935 ownerId=1453842087 transactionname=user_transaction lasttranstarted=2015-12-02T11:27:53.090 XDES=0xd85592d90 lockMode=U schedulerid=6 kpid=12048 status=suspended spid=91 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-12-02T11:28:45.210 lastbatchcompleted=2015-12-02T11:28:45.203 lastattention=2015-12-02T11:27:53.220 clientapp=.Net SqlClient Data Provider hostname=315728-WEB1 hostpid=11640 loginname=user isolationlevel=read committed (2) xactid=1453842087 currentdb=11 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

  executionStack

  frame procname=adhoc line=1 stmtstart=150 stmtend=542 sqlhandle=0x02000000dac7021cffde957fbfc462b546f750d73410c93e0000000000000000000000000000000000000000

  unknown

  frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

  unknown

  inputbuf

  (@TablesIn [EntityTableType] READONLY,@InvoiceRowID int,@OrganizationID int)update [Table] set [Table].InvoiceRowID = @InvoiceRowID From [Table] Inner Join @TablesIn as TableIn On TableIn.ID=[Table].ID where [Table].OrganizationID = @OrganizationID and [Table].InvoiceID is not null

  resource-list

  keylock hobtid=72057602628386816 dbid=11 objectname=Database.dbo.Table indexname=IX_Table_Task_Eventdate id=lockef6a6bc80 mode=X associatedObjectId=72057602628386816

  owner-list

  owner id=process1f700e48c8 mode=X

  waiter-list

  waiter id=process19a1b22108 mode=U requestType=wait

  keylock hobtid=72057602628386816 dbid=11 objectname=Database.dbo.Table indexname=IX_Table_Task_Eventdate id=lock1af2941900 mode=X associatedObjectId=72057602628386816

  owner-list

  owner id=process19a1b22108 mode=X

  waiter-list

  waiter id=process1f700e48c8 mode=U requestType=wait

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 10, 2015 at 8:46 am

  #1845252

  Can you provide an execution plan of one of these updates please, also the definition of index IX_Table_Task_Eventdate.

  ^{“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

• pghdisturbed1

  Newbie

  Points: 4

  More actions

  December 10, 2015 at 8:47 am

  #1845254

  If it were me, I would make some changes to code/indexes prior to putting in locking hints.

  The index locking seems to be based on a date. Does that index cover what your join/where clause is searching for? You may need a different index.

  Also, the join is based on a table variable. I'm not sure how many records are in that variable, but remember that variables do not have statistics. Also, when table variable is part of the query, there is no parallelism. I would change this to a #tmp table, with a possible index.

  Of course, test this like crazy before making any changes.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 10, 2015 at 9:05 am

  #1845272

  pghdisturbed1 (12/10/2015)

  ---

  If it were me, I would make some changes to code/indexes prior to putting in locking hints.

  The index locking seems to be based on a date. Does that index cover what your join/where clause is searching for? You may need a different index.

  Also, the join is based on a table variable. I'm not sure how many records are in that variable, but remember that variables do not have statistics. Also, when table variable is part of the query, there is no parallelism. I would change this to a #tmp table, with a possible index.

  Of course, test this like crazy before making any changes.

  I'd usually advocate a temp table too. Just as a matter of interest, the plan could be parallel.

  ^{“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

• jlj.hook

  SSC Veteran

  Points: 222

  More actions

  December 10, 2015 at 12:35 pm

  #1845326

  Deadlock occures at the end of long invoicing transaction. First each hour row gets information about invoice from update. Lastly comes creation of invoice rows and these are updated to relevant hours. So X locks are already present on this index. I don't understand why update lock is necessary if it already has X lock.

  About the temp table. That table variable usually has less than 10 rows and rarely max 100 so I don't believe temp table would be useful here.

  Here's the index causing issues:

  [IX_XXX]

  (

  [OrganizationID] ASC,

  [TaskID] ASC,

  [EventDate] ASC,

  [InvoiceID] ASC

  )

  INCLUDE ( [Quantity],

  [InvoiceRowID],

  [UnitCost],

  [UnitPriceInBaseCurrency],

  [UserID],

  [InvoiceQuantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

  GO

  And the execution 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.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0198065" StatementText="insert into @p3 values(200,NULL,NULL,NULL,NULL,NULL)" StatementType="INSERT" QueryHash="0x16EF4C0FBC6C1F01" QueryPlanHash="0xFC47A8B49E388F2C" RetrievedFromCache="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 DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="120">

  <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />

  <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="655701" EstimatedPagesCached="122944" EstimatedAvailableDegreeOfParallelism="1" />

  <RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.0198044" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="0.0198065">

  <OutputList />

  <RunTimeInformation>

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

  </RunTimeInformation>

  <ScalarInsert DMLRequestSort="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1003" />

  <ScalarOperator ScalarString="(200)">

  <Const ConstValue="(200)" />

  </ScalarOperator>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Expr1004" />

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Expr1005" />

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Expr1006" />

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Expr1007" />

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Expr1008" />

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <Object Table="[@p3]" Storage="RowStore" />

  <SetPredicate>

  <ScalarOperator ScalarString="[ID] = [Expr1003],[Var1] = [Expr1004],[Var2] = [Expr1005],[Var3] = [Expr1006],[Var4] = [Expr1007],[Var5] = [Expr1008]">

  <ScalarExpressionList>

  <ScalarOperator>

  <MultipleAssign>

  <Assign>

  <ColumnReference Column="ID" />

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1003" />

  </Identifier>

  </ScalarOperator>

  </Assign>

  <Assign>

  <ColumnReference Column="Var1" />

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1004" />

  </Identifier>

  </ScalarOperator>

  </Assign>

  <Assign>

  <ColumnReference Column="Var2" />

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1005" />

  </Identifier>

  </ScalarOperator>

  </Assign>

  <Assign>

  <ColumnReference Column="Var3" />

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1006" />

  </Identifier>

  </ScalarOperator>

  </Assign>

  <Assign>

  <ColumnReference Column="Var4" />

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1007" />

  </Identifier>

  </ScalarOperator>

  </Assign>

  <Assign>

  <ColumnReference Column="Var5" />

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1008" />

  </Identifier>

  </ScalarOperator>

  </Assign>

  </MultipleAssign>

  </ScalarOperator>

  </ScalarExpressionList>

  </ScalarOperator>

  </SetPredicate>

  </ScalarInsert>

  </RelOp>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="3" StatementEstRows="1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0398626" StatementText="update [Hour] set [Hour].InvoiceRowID = @InvoiceRowID From [Hour] Inner Join @HoursIn as HourIn On HourIn.ID=[Hour].ID where [Hour].OrganizationID = @OrganizationID and [Hour].InvoiceID=100" StatementType="UPDATE" QueryHash="0xA0B8DE469D94028A" QueryPlanHash="0x24544030FE5E3C3F" RetrievedFromCache="true">

  <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="0" NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="56" CompileTime="7" CompileCPU="7" CompileMemory="872">

  <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />

  <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="655701" EstimatedPagesCached="122944" EstimatedAvailableDegreeOfParallelism="1" />

  <RelOp AvgRowSize="9" EstimateCPU="7.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Assert" NodeId="1" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.0398626">

  <OutputList />

  <RunTimeInformation>

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

  </RunTimeInformation>

  <Assert StartupExpression="false">

  <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0398618">

  <OutputList>

  <ColumnReference Column="Expr1014" />

  <ColumnReference Column="Pass1015" />

  </OutputList>

  <RunTimeInformation>

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

  </RunTimeInformation>

  <NestedLoops Optimized="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1014" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Pass1015" />

  </DefinedValue>

  </DefinedValues>

  <PassThru>

  <ScalarOperator ScalarString="[Database].[dbo].[Hour].[InvoiceRowID] IS NULL">

  <Logical Operation="IS NULL">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </Identifier>

  </ScalarOperator>

  </Logical>

  </ScalarOperator>

  </PassThru>

  <OuterReferences>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </OuterReferences>

  <ProbeColumn>

  <ColumnReference Column="Expr1014" />

  </ProbeColumn>

  <RelOp AvgRowSize="15" EstimateCPU="3E-06" EstimateIO="0.03" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0365744">

  <OutputList>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </OutputList>

  <RunTimeInformation>

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

  </RunTimeInformation>

  <Update DMLRequestSort="false">

  <Object Database="[Database]" Schema="[dbo]" Table="[Hour]" Index="[PK_Hour]" IndexKind="Clustered" Storage="RowStore" />

  <Object Database="[Database]" Schema="[dbo]" Table="[Hour]" Index="[IX_Hour_Task_Eventdate]" IndexKind="NonClustered" Storage="RowStore" />

  <Object Database="[Database]" Schema="[dbo]" Table="[Hour]" Index="[IX_Hour_InvoiceRowID]" IndexKind="NonClustered" Storage="RowStore" />

  <SetPredicate>

  <ScalarOperator ScalarString="[Database].[dbo].[Hour].[InvoiceRowID] = [@InvoiceRowID]">

  <ScalarExpressionList>

  <ScalarOperator>

  <MultipleAssign>

  <Assign>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="@InvoiceRowID" />

  </Identifier>

  </ScalarOperator>

  </Assign>

  </MultipleAssign>

  </ScalarOperator>

  </ScalarExpressionList>

  </ScalarOperator>

  </SetPredicate>

  <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00657136">

  <OutputList>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="ID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  <ColumnReference Column="Expr1010" />

  <ColumnReference Column="Expr1011" />

  </OutputList>

  <RunTimeInformation>

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

  </RunTimeInformation>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1010" />

  <ScalarOperator ScalarString="[Expr1010]">

  <Identifier>

  <ColumnReference Column="Expr1010" />

  </Identifier>

  </ScalarOperator>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Expr1011" />

  <ScalarOperator ScalarString="[Expr1011]">

  <Identifier>

  <ColumnReference Column="Expr1011" />

  </Identifier>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00657136">

  <OutputList>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="ID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  <ColumnReference Column="Expr1010" />

  <ColumnReference Column="Expr1011" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1010" />

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

  <IF>

  <Condition>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1008" />

  </Identifier>

  </ScalarOperator>

  </Condition>

  <Then>

  <ScalarOperator>

  <Const ConstValue="(0)" />

  </ScalarOperator>

  </Then>

  <Else>

  <ScalarOperator>

  <Const ConstValue="(1)" />

  </ScalarOperator>

  </Else>

  </IF>

  </ScalarOperator>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Expr1011" />

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

  <IF>

  <Condition>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1008" />

  </Identifier>

  </ScalarOperator>

  </Condition>

  <Then>

  <ScalarOperator>

  <Const ConstValue="(0)" />

  </ScalarOperator>

  </Then>

  <Else>

  <ScalarOperator>

  <Const ConstValue="(1)" />

  </ScalarOperator>

  </Else>

  </IF>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="16" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00657126">

  <OutputList>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="ID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  <ColumnReference Column="Expr1008" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1008" />

  <ScalarOperator ScalarString="CASE WHEN [Database].[dbo].[Hour].[InvoiceRowID] = [@InvoiceRowID] THEN (1) ELSE (0) END">

  <IF>

  <Condition>

  <ScalarOperator>

  <Compare CompareOp="BINARY IS">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="@InvoiceRowID" />

  </Identifier>

  </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="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657116">

  <OutputList>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="ID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </OutputList>

  <RunTimeInformation>

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

  </RunTimeInformation>

  <NestedLoops Optimized="false">

  <Predicate>

  <ScalarOperator ScalarString="[Database].[dbo].[Hour].[ID]=@HoursIn.[ID] as [HourIn].[ID]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="ID" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="@HoursIn" Alias="[HourIn]" Column="ID" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  <RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="8" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="21987000">

  <OutputList>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="ID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </OutputList>

  <RunTimeInformation>

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

  </RunTimeInformation>

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

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="ID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[Database]" Schema="[dbo]" Table="[Hour]" Index="[IX_Hour_Task_Eventdate]" IndexKind="NonClustered" Storage="RowStore" />

  <SeekPredicates>

  <SeekPredicateNew>

  <SeekKeys>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[@OrganizationID]">

  <Identifier>

  <ColumnReference Column="@OrganizationID" />

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekKeys>

  </SeekPredicateNew>

  </SeekPredicates>

  <Predicate>

  <ScalarOperator ScalarString="[Database].[dbo].[Hour].[InvoiceID]=(100)">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceID" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="(100)" />

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="9" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">

  <OutputList>

  <ColumnReference Table="@HoursIn" Alias="[HourIn]" Column="ID" />

  </OutputList>

  <RunTimeInformation>

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

  </RunTimeInformation>

  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Table="@HoursIn" Alias="[HourIn]" Column="ID" />

  </DefinedValue>

  </DefinedValues>

  <Object Table="[@HoursIn]" Alias="[HourIn]" Storage="RowStore" />

  </TableScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </Update>

  </RelOp>

  <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="24" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="6521110">

  <OutputList />

  <RunTimeInformation>

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

  </RunTimeInformation>

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

  <DefinedValues />

  <Object Database="[Database]" Schema="[dbo]" Table="[InvoiceRow]" Index="[PK_InvoiceRow]" IndexKind="Clustered" Storage="RowStore" />

  <SeekPredicates>

  <SeekPredicateNew>

  <SeekKeys>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[InvoiceRow]" Column="ID" />

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[InvoiceRow]" Column="OrganizationID" />

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[Database].[dbo].[Hour].[InvoiceRowID]">

  <Identifier>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="InvoiceRowID" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator ScalarString="[Database].[dbo].[Hour].[OrganizationID]">

  <Identifier>

  <ColumnReference Database="[Database]" Schema="[dbo]" Table="[Hour]" Column="OrganizationID" />

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekKeys>

  </SeekPredicateNew>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  <Predicate>

  <ScalarOperator ScalarString="CASE WHEN NOT [Pass1015] AND [Expr1014] IS NULL THEN (0) ELSE NULL END">

  <IF>

  <Condition>

  <ScalarOperator>

  <Logical Operation="AND">

  <ScalarOperator>

  <Logical Operation="NOT">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Pass1015" />

  </Identifier>

  </ScalarOperator>

  </Logical>

  </ScalarOperator>

  <ScalarOperator>

  <Logical Operation="IS NULL">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1014" />

  </Identifier>

  </ScalarOperator>

  </Logical>

  </ScalarOperator>

  </Logical>

  </ScalarOperator>

  </Condition>

  <Then>

  <ScalarOperator>

  <Const ConstValue="(0)" />

  </ScalarOperator>

  </Then>

  <Else>

  <ScalarOperator>

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </Else>

  </IF>

  </ScalarOperator>

  </Predicate>

  </Assert>

  </RelOp>

  <ParameterList>

  <ColumnReference Column="@InvoiceRowID" ParameterCompiledValue="(49254)" ParameterRuntimeValue="(49254)" />

  <ColumnReference Column="@OrganizationID" ParameterCompiledValue="(8)" ParameterRuntimeValue="(8)" />

  </ParameterList>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  December 10, 2015 at 12:44 pm

  #1845333

  That's not a lock conversion, and SQL will never convert an X lock to a U. Lock conversions are to more restrictive lock modes. U is less restrictive than X.

  From the deadlock graph, each session has an X lock on one record and wants a U lock on a *different* record. You can see that because the lock hashes are different between the two resources.

  Basically, session 1 has an X lock on row 1 and wants a U lock on row 2. Session 2 has an X lock on row 2 and wants a U lock on row 1. Hence the deadlock.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• jlj.hook

  SSC Veteran

  Points: 222

  More actions

  December 10, 2015 at 1:05 pm

  #1845338

  Yes that's true. I was wondering should I do select in the beginning of the transaction with all the participating hour Ids with xlock like:

  SELECT * FROM [hour] WITH(XLOCK) WHERE Hour.OrganizationID=1

  AND ID IN(

  1,

  2,

  3,

  4,

  5,

  6

  )

  This would hold X locks on these resources until end of transaction.

  We are using Read committed snapshot isolation so this would not block readers.

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  December 10, 2015 at 1:48 pm

  #1845341

  The deadlock is not coming from a select.

  What you have, at the time the deadlock happens, is one session that holds an X lock already on one row (and will hold it to the end of the transaction, as is required for X locks) and is trying to get a UPD lock on a different row. You need to investigate why it's taking those locks. It could be an insert, update or delete taking the first X lock, it'll be an update trying to get the UPD lock.

  Trace through the procedure line by line and look at the locks taken and requested and see where the X and UPD locks are coming from.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• jlj.hook

  SSC Veteran

  Points: 222

  More actions

  December 10, 2015 at 2:09 pm

  #1845343

  Yes I've done that already to an extent and didn't find way to remove this since 99% of time this transaction runs just fine.

  Why I asked about the xlock select is to lock set PK key locks immediately in the beginning of transaction this way no other transaction can simultaneously access same data due to blocking of X locks which will be set immediately in the beginning and released only in the end. I believe the snapshot isolation has part in this making it possible for two transactions to access same rows.

  Although inside transaction read committed is used.

  I believe this article describes the scenario pretty well:

  http://mikedimmick.blogspot.fi/2004/03/selectupdate-problem-or-why-updlock.html

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  December 10, 2015 at 2:15 pm

  #1845347

  You can try that. It does mean that your invoicing will be serialised and so might run longer and be slower. If that's acceptable, give it a try.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass