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 CollectDataAsync(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(formData);
    }

    Dictionary keyValueParams = new Dictionary();

    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())
    {
        return await _reportDataRepository.CollectDataAsync(reportCode, paraObject.ReportDate, paraObject.Department, keyValueParams);
    }
}

CollectDataAsync method in ReportDataRepository

public async Task CollectDataAsync(string reportCode, DateTime reportDate, string department, Dictionary 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?


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

    image.png

    image.png

  • User Avatar
    0
    duyan11110 created

    Hi,

    I updated AppService like this:

    [HttpPost]
    //[UnitOfWork(true, System.Data.IsolationLevel.ReadCommitted)]
    public async Task CollectDataAsync(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(formData);
        }
    
        Dictionary keyValueParams = new Dictionary();
    
        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())
                {
                    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
    image.png

  • 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

    image.png

  • 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
  • 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

Boost Your Development
ABP Live Training
Packages
See Trainings
Mastering ABP Framework Book
Do you need assistance from an ABP expert?
Schedule a Meeting
Mastering ABP Framework Book
The Official Guide
Mastering
ABP Framework
Learn More
Mastering ABP Framework Book
Made with ❤️ on ABP v9.2.0-preview. Updated on March 13, 2025, 04:08