Spaceman
Reputation: 1339
How to use row locking with efcore to avoid table blocking?
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
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>
Upvotes: 1
Views: 1983
Answers (1)
Dan Guzman
Reputation: 46203
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
Upvotes: 1
Related Questions
- Lock table before inserting in EF Core 5
- Prevent concurrent reads on single table with EFCore and SQL server
- How can I lock a table on read, using Entity Framework?
- Best way to lock table entity framework core
- Entity Framework Core pessimistic locking row table Mysql
- Locking tables in Entity Framework
- Locking a table during 2 statements in Entity Framework
- How to totally lock a row in Entity Framework
- Locking a table with a select in Entity Framework
- Lock table to handle concurrency in Entity Framework 3.5