1

I'm hitting some deadlocking issues on EFCore when I have a high frequency of updates on the same tables.

The DB is hosted on azure.

We have multiple different services that have multiple threads that all have a unique DataContext that are updating the DB. Sometimes transactions get stuck forever in a state of LCK_M_U.

The quires are quite simple:

UPDATE [SomeTable] SET [Status] = @p0, [StatusLastChanged] = @p1 WHERE [Id] = @p2 AND [Status] = @p3 AND [StatusLastChanged] = @p4

The tables are setup in the context like this.

modelBuilder.Entity<SomeTable>().ToTable(nameof(SomeTable)); modelBuilder.Entity<SomeTable>(entity => { entity.Property(e => e.Id).UseIdentityColumn(); entity.Property(e => e.EmailId); entity.Property(e => e.OrganisationId); entity.Property(e => e.MessageId); entity.Property(e => e.MimeType).HasMaxLength(256); entity.Property(e => e.Name).HasMaxLength(256); entity.Property(e => e.Status).IsConcurrencyToken(); entity.Property(e => e.StatusLastChanged).IsConcurrencyToken(); entity.Property(e => e.ErpOrderNumber); });

There is only ever 1 update in the batch. A simplified version of the logic is:

using (var innerScope = _services.CreateScope()) { var _context = innerScope.ServiceProvider.GetService<IDataContext>(); var row = _context.SomeTable.FirstAsync(_ => _.Id == someint && _.Status = somestring); // Some business logic row.Status = newStatusString; var cts = new CancellationTokenSource(5000); await _context.SaveChangesAsync(cts.Token); }

When the transaction get stuck the thread stalls and nothing happens the cancellationToken does not throw.

There will never be a write transaction against the same row at the same time but there could be many concurrent transactions against the same table.

All writes are locked to the row id.

Is there a way to enable row locking in efcore as a way of mitigating table locks?

Update 1:

Here are all the indexes on the table in question.

ALTER TABLE [dbo].[SomeTable] ADD PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [nci_wi_SomeTable_D30B07CA6D9DA2DF1D34A2E0631935A7] ON [dbo].[SomeTable] ( [EmailId] ASC ) INCLUDE([ErpOrderNumber],[MimeType],[Name],[Status],[StatusLastChanged]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_SomeTable_OrganisationId] ON [dbo].[SomeTable] ( [OrganisationId] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] GO

There are no triggers on the table

Here is an a query plan

<?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.539" Build="15.0.1900.210" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="3" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.0232854" StatementText="UPDATE [SomeTable] SET [Status] = @p0, [StatusLastChanged] = @p1&#xD;&#xA;WHERE [Id] = @p2 AND [Status] = @p3 AND [StatusLastChanged] = @p4" StatementType="UPDATE" QueryHash="SOMEHASH" QueryPlanHash="SOMEHASH" RetrievedFromCache="true" StatementSqlHandle="SOMEHASH" DatabaseContextSettingsId="13" ParentObjectId="0" StatementParameterizationType="1" SecurityPolicyApplied="false"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="3" CompileCPU="3" CompileMemory="320"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="22649218" EstimatedPagesCached="2831152" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="8244936" /> <QueryTimeStats CpuTime="0" ElapsedTime="0" /> <RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0232854"> <OutputList /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" /> </RunTimeInformation> <Update DMLRequestSort="false"> <Object Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Index="[PK__SomeTa__3214EC0794FBBF60]" IndexKind="Clustered" Storage="RowStore" /> <Object Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Index="[nci_wi_SomeTable_D30B07CA6D9DA2DF1D34A2E0631935A7]" IndexKind="NonClustered" Storage="RowStore" /> <SetPredicate> <ScalarOperator ScalarString="[someDataBase].[dbo].[SomeTable].[Status] = RaiseIfNullUpdate([Expr1002]),[someDataBase].[dbo].[SomeTable].[StatusLastChanged] = [@p1]"> <ScalarExpressionList> <ScalarOperator> <MultipleAssign> <Assign> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Status" /> <ScalarOperator> <Intrinsic FunctionName="RaiseIfNullUpdate"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1002" /> </Identifier> </ScalarOperator> </Intrinsic> </ScalarOperator> </Assign> <Assign> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="StatusLastChanged" /> <ScalarOperator> <Identifier> <ColumnReference Column="@p1" /> </Identifier> </ScalarOperator> </Assign> </MultipleAssign> </ScalarOperator> </ScalarExpressionList> </ScalarOperator> </SetPredicate> <RelOp AvgRowSize="274" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328338"> <OutputList> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Id" /> <ColumnReference Column="Expr1002" /> <ColumnReference Column="Expr1007" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" /> </RunTimeInformation> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1007" /> <ScalarOperator ScalarString="[Expr1007]"> <Identifier> <ColumnReference Column="Expr1007" /> </Identifier> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="274" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328338"> <OutputList> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Id" /> <ColumnReference Column="Expr1002" /> <ColumnReference Column="Expr1007" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1002" /> <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(255),[@p0],0)"> <Convert DataType="nvarchar" Length="510" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="@p0" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1007" /> <ScalarOperator ScalarString="CASE WHEN CASE WHEN [someDataBase].[dbo].[SomeTable].[Status] = CONVERT_IMPLICIT(nvarchar(255),[@p0],0) THEN (1) ELSE (0) END AND CASE WHEN [someDataBase].[dbo].[SomeTable].[StatusLastChanged] = [@p1] THEN (1) ELSE (0) END THEN (0) ELSE (1) END"> <IF> <Condition> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="BINARY IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Status" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="nvarchar" Length="510" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="@p0" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="BINARY IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="StatusLastChanged" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@p1" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </Logical> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="280" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="5198"> <OutputList> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Id" /> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Status" /> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="StatusLastChanged" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Id" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Status" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="StatusLastChanged" /> </DefinedValue> </DefinedValues> <Object Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Index="[PK__SomeTa__3214EC0794FBBF60]" IndexKind="Clustered" Storage="RowStore" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Id" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[@p2]"> <Identifier> <ColumnReference Column="@p2" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[someDataBase].[dbo].[SomeTable].[StatusLastChanged]=[@p4] AND [someDataBase].[dbo].[SomeTable].[Status]=[@p3]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="StatusLastChanged" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@p4" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[someDataBase]" Schema="[dbo]" Table="[SomeTable]" Column="Status" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@p3" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </ComputeScalar> </RelOp> </Update> </RelOp> <ParameterList> <ColumnReference Column="@p1" ParameterDataType="datetimeoffset(7)" ParameterCompiledValue="'2019-11-05 10:28:13.0568842 +10:00'" ParameterRuntimeValue="'2019-11-05 10:28:13.0568842 +10:00'" /> <ColumnReference Column="@p0" ParameterDataType="nvarchar(4000)" ParameterCompiledValue="N'DocumentMapProcessed'" ParameterRuntimeValue="N'DocumentMapProcessed'" /> <ColumnReference Column="@p4" ParameterDataType="datetimeoffset(7)" ParameterCompiledValue="'2019-11-05 00:25:07.1376149 +00:00'" ParameterRuntimeValue="'2019-11-05 00:25:07.1376149 +00:00'" /> <ColumnReference Column="@p3" ParameterDataType="nvarchar(4000)" ParameterCompiledValue="N'MultiOrderParsed'" ParameterRuntimeValue="N'MultiOrderParsed'" /> <ColumnReference Column="@p2" ParameterDataType="int" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" /> </ParameterList> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>
Improve this question
7
  • You might end up updating 1 row because of your filters, but that doesn't mean that the lock actually locks 1 row. This depends on the indexes of the table and a few other mechanics like lock escalations and such. Can you track down the SQL that EF executes with the update and check it execution plan? Also post the table's indexes and triggers if you have 'em. Commented Nov 7, 2019 at 10:50
  • @EzLo sure ill add what I can get my hands on. Commented Nov 7, 2019 at 10:54
  • @EzLo I have added everything I could get, thanks for having a look :). Commented Nov 7, 2019 at 11:09
  • @Spaceman, your question mentions deadlocks but the symptoms suggest only blocking. Although both involve locks, these are different things from a SQL perspective. If you are not getting SqlExceptions due to deadlocks, consider turning on the blocked process threshold (s) and create an extended events trace to capture the blocked_process_report event. That will provide details of the processes involved that you can add to your question. Commented Nov 7, 2019 at 12:00
  • @DanGuzman Sorry I didn't specifically know there was a difference between blocking and deadlocks. Im not sure how to "blocked process threshold (s) and create an extended events trace to capture the blocked_process_report event" I'll do some googling and see if I can figure it out. Commented Nov 7, 2019 at 12:27

1 Answer 1

Reset to default
1

Your question mentions deadlocks but the symptoms suggest only blocking. Although both involve locks, these are different things from a SQL perspective. If you are not getting SqlExceptions due to deadlocks, consider configuring the blocked process threshold (s) SQL Server configuration option and create an extended events trace to capture the blocked_process_report event. T-SQL example:

--configure blocked process threshold to report blocking longer than 10 seconds EXEC sp_configure 'blocked process threshold (s)', 10; --create trace to capture blocked_process_report events CREATE EVENT SESSION [blocking] ON SERVER ADD EVENT sqlserver.blocked_process_report WITH (STARTUP_STATE=ON); GO

This will provide details of the blocked processes and the locks, which can be viewed live from SSMS Object Explorer (Management-->Extended Events-->Sessions-->blocking, right-click watch live data).

If you can't identify the root cause with this information, add an example blocked_process field from the trace to your question.

EDIT:

With Azure SQL Database, the 'blocked process threshold (s)' is pre-configured (20) so sp_configure is not necessary. The trace needs to be database-scoped:

--create trace to capture blocked_process_report events CREATE EVENT SESSION [blocking] ON DATABASE ADD EVENT sqlserver.blocked_process_report WITH (STARTUP_STATE=ON); GO
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I forgot to mention is this is an azure hosted db. "Database scoped extended event sessions are not available in server scope or system databases in Azure DB." Both queries cannot run on server.
@Spaceman, I added Azure SQL database details to my answer and added the azure-sql-database tag to your question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.