- 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?
13 Answer(s)
-
0
hi
You can set the
IsolationLevelfor a newUnitOfWork.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
isolationLevel
Hi, Do I need to set UOW isolation level in reading api logic ??
-
0
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
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
hi
Please share some code of your background job
-
0
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
hi
You can use
_unitOfWorkManagerto begin a new uow in theHandleEventAsyncmethod, and set theisolationLevelif necessary. -
0
hi
You can use
_unitOfWorkManagerto begin a new uow in theHandleEventAsyncmethod, and set theisolationLevelif 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
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
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
hi
How can I reproduce this in a new template project?
Can you share a minimal project? liming.ma@volosoft.com
-
0
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
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
