Unit of work with Read Committed still dirty read #8874

duyan11110 created 20 days ago

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?

maliming created 19 days ago

Support Team Fullstack Developer

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

duyan11110 created 19 days ago

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

maliming created 19 days ago

Support Team Fullstack Developer

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

Thanks.

duyan11110 created 19 days ago

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?

maliming created 19 days ago

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

duyan11110 created 18 days ago

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.

maliming created 18 days ago

Support Team Fullstack Developer

Great!

duyan11110 created 18 days ago

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?

maliming created 18 days ago

Support Team Fullstack Developer

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

duyan11110 created 18 days ago

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.