Tables are Locked For Reading - Impact Delayed Response #6915 | Support

0

maliming created 9 months ago

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);
}

0

shijo created 9 months ago

isolationLevel

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

0

maliming created 9 months ago

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.

0

shijo created 9 months ago

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.

0

maliming created 9 months ago

Support Team Fullstack Developer

hi

Please share some code of your background job

0

shijo created 9 months ago

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
  }

0

maliming created 9 months ago

Support Team Fullstack Developer

hi

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

0

shijo created 9 months ago

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?

0

maliming created 9 months ago

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

0

shijo created 9 months ago

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

0

maliming created 9 months ago

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

0

shijo created 9 months ago

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)
        };
    }
}

0

maliming created 9 months ago

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

Tables are Locked For Reading - Impact Delayed Response #6915

13 Answer(s)