abpframework
Timeout (task cancelled) when using "DeleteManyAsync" or "DeleteAsync" with predicate on a couple of thousand records
ABP 5.1.2
I was trying to delete data in chunks to avoid slow performance, but still getting timeout ("Task was cancelled"):
public virtual async Task<bool> DeleteAsync(PlateListKeyDto key, CancellationToken cancellationToken = default)
{
const int chunkSize = 10;
var item = await GetInnerAsync(key.Id);
try
{
//var dbContext = await _plateListAuthorizationRepository.GetDbContextAsync();
//dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
var plateListAuthorizationDbSet = await _plateListAuthorizationRepository.GetDbSetAsync();
var plateListAuthorizations = plateListAuthorizationDbSet.Where(x => x.PlateListId == item.Id).ToList();
for (var skip = 0; skip < plateListAuthorizations.Count; skip += chunkSize)
{
await _plateListAuthorizationRepository.DeleteManyAsync(plateListAuthorizations.Skip(skip).Take(chunkSize), true, cancellationToken); // EXCEPTION HAPPENS HERE
}
await _plateListRepository.DeleteAsync(item, true, cancellationToken);
}
catch(DbUpdateException dbExtension) when (dbExtension.IsChildRecordFoundException())
{
throw new BusinessException(DomainErrorCodes.ChildRecordFound, _stringLocalizer.GetString("ChildRecordFound"));
}
return true;
}
This is the exception stack:
at Oracle.EntityFrameworkCore.Update.Internal.OracleModificationCommandBatch.<ConsumeAsync>d__27.MoveNext() at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__29.MoveNext() at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__29.MoveNext() at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext() at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext() at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext() at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__103.MoveNext() at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__107.MoveNext() at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.<ExecuteAsync>d__8
2.MoveNext() at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__60.MoveNext() at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__60.MoveNext() at Volo.Abp.EntityFrameworkCore.AbpDbContext1.<SaveChangesAsync>d__41.MoveNext() at Volo.Abp.Domain.Repositories.EntityFrameworkCore.EfCoreRepository2.<DeleteManyAsync>d__25.MoveNext() at Castle.DynamicProxy.AsyncInterceptorBase.<ProceedAsynchronous>d__13.MoveNext() at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.<ProceedAsync>d__7.MoveNext() at Volo.Abp.Uow.UnitOfWorkInterceptor.<InterceptAsync>d__2.MoveNext() at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.<InterceptAsync>d__2.MoveNext() at AbxEps.MasterData.PlateLists.PlateListsAppService.<DeleteAsync>d__11.MoveNext() in C:\CT\AbxEps.MasterData\aspnet-core\src\AbxEps.MasterData.Application\PlateLists\PlateListAppService.cs:line 97
When I try to use DeleteAsync - it is the same. And - BTW - why it is so weirdly translated to SQL?
public virtual async Task<bool> DeleteAsync(PlateListKeyDto key, CancellationToken cancellationToken = default)
{
const int chunkSize = 10;
var item = await GetInnerAsync(key.Id);
try
{
var dbContext = await _plateListAuthorizationRepository.GetDbContextAsync();
dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
var plateListAuthorizationDbSet = await _plateListAuthorizationRepository.GetDbSetAsync();
var plateListAuthorizationIds = plateListAuthorizationDbSet.Where(x => x.TenantId == item.TenantId && x.PlateListId == item.Id).OrderBy(x => x.Id).Select(x => x.Id);
for (var skip = 0; skip < plateListAuthorizationIds.Count(); skip += chunkSize)
{
var chunky = plateListAuthorizationIds.Skip(skip).Take(chunkSize).ToList();
await _plateListAuthorizationRepository.DeleteAsync(x => chunky.Contains(x.Id), true, cancellationToken);
}
await _plateListRepository.DeleteAsync(item, true, cancellationToken);
}
catch(DbUpdateException dbExtension) when (dbExtension.IsChildRecordFoundException())
{
throw new BusinessException(DomainErrorCodes.ChildRecordFound, _stringLocalizer.GetString("ChildRecordFound"));
}
return true;
}
DeleteAsync(x => chunky.Contains(x.Id), ...) always translated into bunch of DELETEs per entry, instead of the single DELETE FROM MD_LA_AUTH m WHERE m.N_AUTH IN (102761, 102762, 102763, 102764, 102765, 102766, 102767, 102768, 102769, 102770):
SELECT m.C_TENANT, m.N_AUTH, m.D_CREATE, m.S_AUTH, m.C_AUTH_TYPE, m.D_INS, m.C_USR_INS, m.D_END, m.D_UPD, m.C_USR_UPD, m.N_LOCATION, m.S_PLATE, m.C_COUNTRY, m.N_PLTLST, m.S_PLATE_NORM, m.ROWID, m.D_START, m.N_VEH_CAT, m.N_VEH_GRP FROM MD_LA_AUTH m WHERE m.N_AUTH IN (102761, 102762, 102763, 102764, 102765, 102766, 102767, 102768, 102769, 102770)
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (118ms) [Parameters=[:p0='102761', :p1='3', :p2='102762', :p3='3', :p4='102763', :p5='3', :p6='102764', :p7='3', :p8='102765', :p9='3', :p10='102766', :p11='3', :p12='102767', :p13='3', :p14='102768', :p15='3', :p16='102769', :p17='3', :p18='102770', :p19='3', :cur1=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur2=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur3=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur4=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur5=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur6=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur7=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur8=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur9=NULL (Nullable = false) (Direction = Output) (DbType = Object), :cur10=NULL (Nullable = false) (Direction = Output) (DbType = Object)], CommandType='Text', CommandTimeout='0']
DECLARE v_RowCount INTEGER; BEGIN DELETE FROM MD_LA_AUTH WHERE N_AUTH = :p0 AND C_TENANT = :p1; v_RowCount := SQL%ROWCOUNT; OPEN :cur1 FOR SELECT v_RowCount FROM DUAL; DELETE FROM MD_LA_AUTH WHERE N_AUTH = :p2 AND C_TENANT = :p3; v_RowCount := SQL%ROWCOUNT; OPEN :cur2 FOR SELECT v_RowCount FROM DUAL; DELETE FROM MD_LA_AUTH WHERE N_AUTH = :p4 AND C_TENANT = :p5; v_RowCount := SQL%ROWCOUNT; OPEN :cur3 FOR SELECT v_RowCount FROM DUAL; DELETE FROM MD_LA_AUTH WHERE N_AUTH = :p6 AND C_TENANT = :p7; v_RowCount := SQL%ROWCOUNT; OPEN :cur4 FOR SELECT v_RowCount FROM DUAL; DELETE FROM MD_LA_AUTH WHERE N_AUTH = :p8 AND C_TENANT = :p9; v_RowCount := SQL%ROWCOUNT; OPEN :cur5 FOR SELECT v_RowCount FROM DUAL;
I've resolved it by using raw SQL "delete from [table] where [predicate]". It's weird that your DeleteAsync is not transformed into this kind of SQL, but instead is transformed into one SELECT and sequential DELETEs - each for one SELECT result...