Open Closed

Tables are Locked For Reading - Impact Delayed Response #6915

User avatar
0
shijo created
  • ABP Framework version: v7.0
  • UI Type: Angular
  • 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:

We are experiencing delays in reading records from the table while heavy jobs are running. How can we read data from the table while ignoring locks without disabling UOW transaction?

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

    hi

    You can set the IsolationLevel for a new UnitOfWork.

    https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

    public static IUnitOfWork Begin(
    [NotNull] this IUnitOfWorkManager unitOfWorkManager,
    bool requiresNew = false,
    bool isTransactional = false,
    IsolationLevel? isolationLevel = null,
    int? timeout = null)
{
    Check.NotNull(unitOfWorkManager, nameof(unitOfWorkManager));

    return unitOfWorkManager.Begin(new AbpUnitOfWorkOptions
    {
        IsTransactional = isTransactional,
        IsolationLevel = isolationLevel,
        Timeout = timeout
    }, requiresNew);
}
  • User Avatar
    0
    shijo created

    isolationLevel

    Hi, Do I need to set UOW isolation level in reading api logic ??

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    If you just read and return to the frontend, I think there is generally no need to set this option. The default is enough.

  • User Avatar
    0
    shijo created

    hi

    If you just read and return to the frontend, I think there is generally no need to set this option. The default is enough.

    Hi, Where should I do add this logic ? Is it in my background job ? After running the background job (which is a long-running process), there is a delay in data reading.

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    Please share some code of your background job

  • User Avatar
    0
    shijo created

    Hi,

    Background job is simple distributted event which is doing CRUD operations by using entity methods. Inside this method we are not using _unitOfWorkManager instead using [UnitOfWork] attribute. Everythings are working fine, but delay to get data from the tables which is using inside this method.

      [UnitOfWork]
  public virtual async Task HandleEventAsync(APIBookEto eventData)
  {
      
      // Here using multiple table CRUD operations
  }
  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    You can use _unitOfWorkManager to begin a new uow in the HandleEventAsync method, and set the isolationLevel if necessary.

  • User Avatar
    0
    shijo created

    hi

    You can use _unitOfWorkManager to begin a new uow in the HandleEventAsync method, and set the isolationLevel if necessary.

    Hi, As I can see in the documentation, the default isolation level is ReadUncommitted, so why is this locking the table for reading?

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    I'm not sure. You can check the https://learn.microsoft.com/en-us/dotnet/api/system.data.isolationlevel?view=net-8.0

  • User Avatar
    0
    shijo created

    hi

    I'm not sure. You can check the https://learn.microsoft.com/en-us/dotnet/api/system.data.isolationlevel?view=net-8.0

    We've encountered this issue in multiple places, such as during the functionality to import users from Excel data. While looping and inserting users, the user table is completely locked, loading the users listing API stuck. There have added this code, but still unable to list users while doing this job. Even when querying from SQL, it doesn't give me results unless **with(nolock) ** is added

      public virtual async Task HandleEventAsync(APIUserImportEto eventData)
  {
      using (var uow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: true, isolationLevel: System.Data.IsolationLevel.ReadUncommitted))
      {
        foreach (var importData in userImportedData)
          {
                  // Logic to insert users
          }
        }
    
    }
  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    How can I reproduce this in a new template project?

    Can you share a minimal project? liming.ma@volosoft.com

  • User Avatar
    0
    shijo created

    Hi, Below is a sample code snippet. The 'userImportedData' table contains master data with user information, such as username, password, etc. Within this distributed event method, we run a loop through this data, performing data validation and creating identity users if the validation is successful. However, during this process, the 'UserImportData' table is completely locked, preventing any records from being read until the loop completes.

     public virtual async Task HandleEventAsync(APIUserImportEto eventData)
 {
     using (var uow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: true, isolationLevel: System.Data.IsolationLevel.ReadUncommitted))
     {
         try
         {

             using (_currentTenant.Change(eventData.TenantId))
             {
                 var newPrincipal = new ClaimsPrincipal(
                                     new ClaimsIdentity(
                                         new Claim[]
                                         {
                                             new Claim(AbpClaimTypes.UserId, eventData.CreatorId.ToString()),
                                             new Claim(AbpClaimTypes.TenantId, eventData.TenantId.ToString()),
                                             new Claim(AbpClaimTypes.UserName, "admin")
                                         }
                                     )
                                  );
                 using (_currentPrincipalAccessor.Change(newPrincipal))
                 {
                     var userImport = (await _userImportRepository.GetQueryableAsync()).Where(x => x.Id == eventData.UserImportId).FirstOrDefault();
                     if (userImport != null)
                     {
                         userImport.Status = UserImportStatus.InProgress;
                         await _userImportRepository.UpdateAsync(userImport, true);
                         await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                             .SendAsync("UserImportInProgressMessage", userImport.Id);

                         SetIdentityOptions();
                         bool isAnyError = false;
                         var userImportedData = (await _userImportDataRepository.GetQueryableAsync()).Where(x => x.UserImportId == eventData.UserImportId).ToList();
                         foreach (var importData in userImportedData)
                         {
                             importData.Status = UserImportStatus.InProgress;
                             await _userImportDataRepository.UpdateAsync(importData, true);
                             await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                                 .SendAsync("UserImportDataInProgressMessage", userImport.Id);
                             try
                             {
                                 List<string> lsrErrors = new List<string>();
                                 if (string.IsNullOrWhiteSpace(importData.UserName))
                                 {
                                     lsrErrors.Add("User Name is empty");
                                 }
                                 if (string.IsNullOrWhiteSpace(importData.Name))
                                 {
                                     lsrErrors.Add("Name is empty");
                                 }

                                 if (!string.IsNullOrWhiteSpace(importData.Name) && importData.Name.Length > 64)
                                 {
                                     importData.Name = importData.Name.Substring(0, 63);
                                 }

                                 if (!string.IsNullOrWhiteSpace(importData.SurName) && importData.SurName.Length > 64)
                                 {
                                     importData.SurName = importData.SurName.Substring(0, 63);
                                 }

                                 if (string.IsNullOrWhiteSpace(importData.Password))
                                 {
                                     lsrErrors.Add("Password is empty");
                                 }
                                 else if (importData.Password.Trim().Length < UserImportDataConsts.PasswordMinLength || importData.Password.Trim().Length > UserImportDataConsts.PasswordMaxLength)
                                 {
                                     lsrErrors.Add("Password length is invalid (Max 20 Character allowed)");
                                 }
                                 if (lsrErrors.Count == 0)
                                 {

                                     var userRole = (await _identityRoleRepository.GetListAsync()).Where(x => x.Name == "user").FirstOrDefault();
                                     if (userRole == null)
                                     {
                                         userRole = new Volo.Abp.Identity.IdentityRole(_guidGenerator.Create(), "user", eventData.TenantId);
                                         await _identityRoleRepository.InsertAsync(userRole, true);
                                     }
                                     var identityUser = new Volo.Abp.Identity.IdentityUser(_guidGenerator.Create(), importData.UserName, importData.UserName, eventData.TenantId);
                                     if (identityUser != null)
                                     {
                                         identityUser.AddRole(userRole.Id);
                                     }
                                     identityUser.Name = importData.Name;
                                     identityUser.Surname = importData.SurName;
                                     var createdUser = await _identityUserManager.CreateAsync(identityUser, importData.Password, false);
                                     createdUser.CheckErrors();

                                     if (!string.IsNullOrEmpty(roles))
                                     {
                                         var rolesIds = roles.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                                         foreach (var roleId in rolesIds)
                                         {
                                             await _userRoleMappingRepository.InsertAsync(new UserRoleMapping(_guidGenerator.Create(), identityUser.Id, roleId, "", ""), true);
                                         }
                                     }

                                     if (!string.IsNullOrEmpty(grades))
                                     {
                                         var gradesIds = grades.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                                         foreach (var gradeId in gradesIds)
                                         {
                                             await _userGradeMappingRepository.InsertAsync(new UserGradeMapping(_guidGenerator.Create(), identityUser.Id, gradeId, gradeId, "", ""), true);
                                         }
                                     }

                                     if (!string.IsNullOrEmpty(subjects))
                                     {
                                         var subjectsIds = subjects.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                                         foreach (var subjectId in subjectsIds)
                                         {
                                             await _userSubjectMappingRepository.InsertAsync(new UserSubjectMapping(_guidGenerator.Create(), identityUser.Id, subjectId, "", ""), true);
                                         }
                                     }

                                     importData.Status = UserImportStatus.Success;
                                     await _userImportDataRepository.UpdateAsync(importData, true);

                                     await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                                     .SendAsync("UserImportDataSuccessMessage", importData.Id);
                                 }
                                 else
                                 {
                                     throw new UserFriendlyException(String.Join(",", lsrErrors.ToArray()));
                                 }
                             }
                             catch (UserFriendlyException ex)
                             {
                                 _logger.LogException(ex);
                                 isAnyError = true;
                                 importData.Status = UserImportStatus.Failed;
                                 importData.StatusDetails = ex.Message;
                                 await _userImportDataRepository.UpdateAsync(importData, true);
                                 await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                                  .SendAsync("UserImportDataFailedMessage", importData.Id, ex.Message);
                             }
                             catch (Exception ex)
                             {
                                 string excpetionMessage = ex.Message;
                                 if (excpetionMessage.Length > 100) excpetionMessage = excpetionMessage.Substring(0, 100);
                                 _logger.LogException(ex);
                                 isAnyError = true;
                                 importData.Status = UserImportStatus.Failed;
                                 importData.StatusDetails = excpetionMessage;
                                 await _userImportDataRepository.UpdateAsync(importData, true);
                                 await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                                  .SendAsync("UserImportDataFailedMessage", importData.Id, ex.Message);
                             }
                             finally
                             {
                                 await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                                         .SendAsync("UserImportInProgressMessage", userImport.Id);
                             }

                         }
                         if (isAnyError)
                         {
                             userImport.Status = UserImportStatus.Failed;
                             await _userImportRepository.UpdateAsync(userImport, true);
                             await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                                 .SendAsync("UserImportFailedMessage", userImport.Id);
                         }
                         else
                         {
                             userImport.Status = UserImportStatus.Success;
                             await _userImportRepository.UpdateAsync(userImport, true);
                             await _msgHubContext.Clients.User(eventData.CreatorId.ToString())
                                 .SendAsync("UserImportSuccessMessage", userImport.Id);
                         }
                     }
                 }
             }

         }
         catch (Exception exp)
         {
             try
             {
                 _logger.LogException(exp);
             }
             catch { }
         }

         await uow.CompleteAsync();
     }
 }

 private void SetIdentityOptions()
 {
     _identityUserManager.Options.User.RequireUniqueEmail = true;
     _identityUserManager.Options.User.AllowedUserNameCharacters = $"{_identityUserManager.Options.User.AllowedUserNameCharacters}"; // add special characters here!
     _identityUserManager.Options.Password.RequireDigit = false;
     _identityUserManager.Options.Password.RequireUppercase = false;
     _identityUserManager.Options.Password.RequireNonAlphanumeric = false;
     _identityUserManager.Options.Password.RequireLowercase = false;
     _identityUserManager.Options.Password.RequiredLength = 1;
 }

    UserImportData GetAPI Code,

    public virtual async Task<PagedResultDto<UserImportDto>> GetListAsync(GetUserImportsInput input)
{
        var totalCount = await _userImportRepository.GetCountAsync(input.FilterText, input.FileDescriptorId, input.Status);
        var items = await _userImportRepository.GetListAsync(input.FilterText, input.FileDescriptorId, input.Status, input.Sorting, input.MaxResultCount, input.SkipCount);

        return new PagedResultDto<UserImportDto>
        {
            TotalCount = totalCount,
            Items = ObjectMapper.Map<List<UserImport>, List<UserImportDto>>(items)
        };
    }
}
  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    The code seems to be fine, can you share a simple project to reproduce the problem? This way I can debug locally to troubleshoot the problem.

    liming.ma@volosoft.com

Assignee
User avatar maliming
Labels
Non yet
Made with ❤️ on ABP v8.2.0-preview Updated on March 25, 2024, 15:11