Open Closed

Unit of work with Read Committed still dirty read #8874

User avatar
0
duyan11110 created
  • ABP Framework version: v7.3.0
  • UI Type: MVC
  • Database System: EF Core (SQL Server)
  • Tiered (for MVC) or Auth Server Separated (for Angular): yes
  • Exception message and full stack trace:
  • Steps to reproduce the issue: In AppService I have this method:
[HttpPost]
[UnitOfWork(true, System.Data.IsolationLevel.ReadCommitted)]
public async Task<Guid> CollectDataAsync<T>(string reportCode, string formData, string? permission = "") where T : CollectDataBaseDto
{
    if (string.IsNullOrEmpty(permission))
    {
        throw new UserFriendlyException(_localizer["common_msg_DoNotPermission"]);
    }
    if (!await AuthorizationService.IsGrantedAsync(permission))
    {
        throw new UserFriendlyException(string.Format(_localizer["common_msg_DoNotPermission_Item"], permission));
    }

    if (CurrentUser == null)
    {
        throw new UserFriendlyException(new ArgumentNullException(nameof(CurrentUser)).Message);
    }

    if (CurrentTenant == null)
    {
        throw new UserFriendlyException(new ArgumentNullException(nameof(CurrentTenant)).Message);
    }

    T? paraObject = default(T);
    if (!string.IsNullOrEmpty(formData))
    {
        paraObject = JsonConvert.DeserializeObject<T>(formData);
    }

    Dictionary<string, object> keyValueParams = new Dictionary<string, object>();

    if (paraObject == null)
    {
        return Guid.Empty;
    }

    paraObject.ReportCode = reportCode;
    if (CurrentUser != null && CurrentUser.Id.HasValue)
    {
        paraObject.CurrentUserId = CurrentUser.Id.Value;
        string department = await GetCurrentDepartmentAsync();
        if (!string.IsNullOrEmpty(department))
        {
            paraObject.Department = department;
        }
    }
    if (CurrentTenant != null && CurrentTenant.Id.HasValue)
    {
        paraObject.TenantId = CurrentTenant.Id.Value;
    }

    Logger.LogInformation("Para object: {0}", paraObject.ObjectToString());
    var properties = paraObject.GetType().GetProperties();
    foreach (var prop in properties)
    {
        keyValueParams.Add(prop.Name, prop.GetValue(paraObject) ?? string.Empty);
    }

    using (_dataFilter.Disable<ISoftDelete>())
    {
        return await _reportDataRepository.CollectDataAsync(reportCode, paraObject.ReportDate, paraObject.Department, keyValueParams);
    }
}

CollectDataAsync method in ReportDataRepository

public async Task<Guid> CollectDataAsync(string reportCode, DateTime reportDate, string department, Dictionary<string, object> keyValueParams)
{
    if (string.IsNullOrEmpty(department))
        return Guid.Empty;

    var dbContext = await GetDbContextAsync();

    await SetCommandTimeout(dbContext);

    ReportType? reportType = await dbContext.ReportTypes.FirstOrDefaultAsync(a => a.ReportCode == reportCode && !a.IsDeleted);
    if (reportType == null)
        return Guid.Empty;

    ReportStructure? reportStructure = await dbContext.ReportStructures.FirstOrDefaultAsync(a => a.ReportTypeId == reportType.Id && a.IsActive && !a.IsDeleted);
    if (reportStructure == null)
        return Guid.Empty;

    ReportPeriod? reportPeriod = await dbContext.ReportPeriods.FirstOrDefaultAsync(a => a.ReportTypeId == reportType.Id && a.ReportDate.Date == reportDate.Date && !a.IsDeleted);
    if (reportPeriod == null)
    {
        //create new report period for this report type
        reportPeriod = new ReportPeriod(Guid.NewGuid());
        reportPeriod.ReportTypeId = reportType.Id;
        reportPeriod.ReportStructureId = reportStructure.Id;
        reportPeriod.ReportDate = reportDate;
        await dbContext.ReportPeriods.AddAsync(reportPeriod);
        await dbContext.SaveChangesAsync();
    }


    if (await dbContext.ReportData.AnyAsync(a => a.ReportPeriodId == reportPeriod.Id && a.Department.ToLower() == department.ToLower()))
        return reportPeriod.Id;

    //Find all datasources and execute scripts
    var datasources = dbContext.ReportDataSources.Where(a => a.ReportStructureId == reportStructure.Id && a.Department.Contains(department) && !a.IsDeleted
    ).OrderBy(a => a.RunningOrder);

    foreach (var ds in datasources)
    {
        await ExecuteSQLCommand(ds, keyValueParams);
    }

    return reportPeriod.Id;
}

I check ReportPeriod table with these conditions: ReportPeriod? reportPeriod = await dbContext.ReportPeriods.FirstOrDefaultAsync(a => a.ReportTypeId == reportType.Id && a.ReportDate.Date == reportDate.Date && !a.IsDeleted); and expect that it can not have > 1 record with the same ReportType, ReportDate and not deleted.

But when I quickly click Collect Data button on UI 4 times. It still creates 4 records in database.

Where am I wrong?

Go to accepted answer
11 Answer(s)
  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    Please try to begin a new UOW instead of using [UnitOfWork(true, System.Data.IsolationLevel.ReadCommitted)]

    See https://abp.io/community/articles/understanding-transactions-in-abp-unit-of-work-0r248xsr

  • User Avatar
    0
    duyan11110 created

    Hi,

    I updated AppService like this:

    [HttpPost]
//[UnitOfWork(true, System.Data.IsolationLevel.ReadCommitted)]
public async Task<Guid> CollectDataAsync<T>(string reportCode, string formData, string? permission = "") where T : CollectDataBaseDto
{
    if (string.IsNullOrEmpty(permission))
    {
        throw new UserFriendlyException(_localizer["common_msg_DoNotPermission"]);
    }
    if (!await AuthorizationService.IsGrantedAsync(permission))
    {
        throw new UserFriendlyException(string.Format(_localizer["common_msg_DoNotPermission_Item"], permission));
    }

    if (CurrentUser == null)
    {
        throw new UserFriendlyException(new ArgumentNullException(nameof(CurrentUser)).Message);
    }

    if (CurrentTenant == null)
    {
        throw new UserFriendlyException(new ArgumentNullException(nameof(CurrentTenant)).Message);
    }

    T? paraObject = default(T);
    if (!string.IsNullOrEmpty(formData))
    {
        paraObject = JsonConvert.DeserializeObject<T>(formData);
    }

    Dictionary<string, object> keyValueParams = new Dictionary<string, object>();

    if (paraObject == null)
    {
        return Guid.Empty;
    }

    paraObject.ReportCode = reportCode;
    if (CurrentUser != null && CurrentUser.Id.HasValue)
    {
        paraObject.CurrentUserId = CurrentUser.Id.Value;
        string department = await GetCurrentDepartmentAsync();
        if (!string.IsNullOrEmpty(department))
        {
            paraObject.Department = department;
        }
    }
    if (CurrentTenant != null && CurrentTenant.Id.HasValue)
    {
        paraObject.TenantId = CurrentTenant.Id.Value;
    }

    Logger.LogInformation("Para object: {0}", paraObject.ObjectToString());
    var properties = paraObject.GetType().GetProperties();
    foreach (var prop in properties)
    {
        keyValueParams.Add(prop.Name, prop.GetValue(paraObject) ?? string.Empty);
    }

    Guid reportPeriodId;
    try
    {
        using (var uow = UnitOfWorkManager.Begin(requiresNew: true, isTransactional: true, isolationLevel: System.Data.IsolationLevel.ReadCommitted))
        {
            using (_dataFilter.Disable<ISoftDelete>())
            {
                reportPeriodId = await _reportDataRepository.CollectDataAsync(reportCode, paraObject.ReportDate, paraObject.Department, keyValueParams);
            }

            await uow.CompleteAsync();
        }

        return reportPeriodId;
    }
    catch (Exception ex)
    {
        throw new UserFriendlyException(ex.Message);
    }
}

    and no change in Repository, but still error

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    I can't understand your problem. Can you share some test code so that I can reproduce it on my computer?

    Thanks.

  • User Avatar
    0
    duyan11110 created

    Hi, Sorry, I can't share my code. Please guide me which code I need to check. Any doubt that you need me to clarify?

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    You have started a new uow. That’s no problem.

    using (var uow = UnitOfWorkManager.Begin(requiresNew: true, isTransactional: true, isolationLevel: System.Data.IsolationLevel.ReadCommitted))
{
    using (_dataFilter.Disable())
    {
        reportPeriodId = await _reportDataRepository.CollectDataAsync(reportCode, paraObject.ReportDate, paraObject.Department, keyValueParams);
    }

    await uow.CompleteAsync();
}

    You can enable the debug log of EF Core to see the SQL statement(EnableSensitiveDataLogging).

    https://abp.io/support/questions/8622/How-to-enable-Debug-logs-for-troubleshoot-problems

  • User Avatar
    0
    duyan11110 created

    Hi, I solved this issue. Isolation level must be Serializable not Read Committed, because Report Period is newly inserted. Read Committed can not lock it.

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    Great!

  • User Avatar
    0
    duyan11110 created

    But I still don't understand why I can't use UnitOfWork attribute like this [UnitOfWork(true, System.Data.IsolationLevel.Serializable)]. It does not work. Can you explain it?

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    Please check: https://abp.io/community/articles/understanding-transactions-in-abp-unit-of-work-0r248xsr

  • User Avatar
    0
    duyan11110 created

    Hi,

    OK, so the secret here is the transaction must be newly created. I tested with requiresNew: false and isolationLevel: System.Data.IsolationLevel.Serializable it still inserts duplicated records.

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    Yes, requiresNew should be true

Assignee
User avatar maliming
Labels
None yet
Learn More, Pay Less
33% OFF
All Trainings!
Get Your Deal
Mastering ABP Framework Book
The Official Guide
Mastering
ABP Framework
Learn More
Mastering ABP Framework Book
Made with ❤️ on ABP v10.0.0-preview. Updated on September 15, 2025, 10:51