SQL query is out of transaction scope. Not able to rollback changes on error. #781

7 Answer(s)

• 

  0

  alper created před 3 lety

  Support Team Director

  hi,

  first of all I advice you to do this long lasting operation in a background job. https://docs.abp.io/en/abp/latest/Background-Jobs For example you can use HangFire ...

  your first approach is not feasible. working with bulk data needs to be going out of the rutin rules. in this point, you can run a Stored Procedure or run a custom executable or you can completely write a new console app with Dapper to run your bulk operation.

  In your 1st approach you can disable transaction. see this document . but it's not practical to iterate over 40K records.

   [UnitOfWork(IsDisabled = true)]
          public virtual async Task FooAsync()
          {
              //...
          }
  

  my advice can be Stored Procedure, pass the parameters to Stored Procedure and you can make it transactional in your Stored Procedure. see this stored procedure sample.

  Save Cancel
• 

  0

  hikalkan created před 3 lety

  Support Team Co-Founder

  Hi @lalitChougule,

  ABP uses standard EF Core transaction approach and it should also cover raw SQL operations.

  I suspect that your UOW is not transactional. Can you put a breakpoint, debug and check if current UOW is transactional (Inject IUnitOfWorkManager and check it's Current property).

  If your request is not GET, ABP automatically starts transaction. If you are sure that your UOW is transactional, then we will try to reproduce it in our side.

  Save Cancel
• 

  0

  liangshiwei created před 3 lety

  Support Team Fullstack Developer

  Hi,

  As hikalkan said, your sql execution may not be in the transaction. Below is my test code and it works fine:

  public class TestAppService : ApplicationService
  {
      private readonly IMyRepository _myRepository;
  
      public TestAppService(IMyRepository myRepository)
      {
          _myRepository = myRepository;
      }
  
      public async Task UpdateManyAsync()
      {
          var roleIds = (await _myRepository.GetListAsync()).Select(x => x.Id).ToArray();
  
          await _myRepository.UpdateManyAsync(roleIds);
  
          await CurrentUnitOfWork.SaveChangesAsync();
  
          throw new UserFriendlyException("test exception");
      }
  }
  
  public class MyRepository : EfCoreRepository<IdentityDbContext, IdentityRole, Guid>, IMyRepository
  {
      public MyRepository(IDbContextProvider<IdentityDbContext> dbContextProvider) : base(dbContextProvider)
      {
      }
  
      public async Task UpdateManyAsync(Guid[] ids)
      {
          var parameter =  string.Join(',',ids.Select(x => $"'{x}'").ToList());
          await DbContext.Database.ExecuteSqlRawAsync(
              $"update AbpRoles set IsPublic = 1 where id in ({parameter})");
      }
  }
  

  Save Cancel
• 

  0

  lalitChougule created před 3 lety

  Hi @hikalkan

  My method is POST method, So it's starting transaction. If you are sure that your UOW is transactional, then we will try to reproduce it in our side -- YES I AM SURE

  My custom repository method is performing 2 operations at a time.

  My Requirement ->

  1st operation : To update table XYZ_1 where I update status of some records in bulk. 2nd operation : Is to insert the id's of updated records and the changed status in another XYZ_2 table which I later on use for auditing and other operations.

  Issue : Even if my update fails I found that, from almost 40k records very small amount of records got inserted in 2nd operation (around 10-20 records)

  ---

  Hi @liangshiwei,

  My code is almost same as your code. Even I thought it was working fine, while trying to recreate the same issue I was able to recreate it only once in 9-10 attempts. If you can read the comments for ExecuteSqlRawAsync in it's Interface it says :

  Executes the given SQL against the database and returns the number of rows affected.
  Note that this method does not start a transaction. To use this method with a
  transaction, first call Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.BeginTransaction(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade,System.Data.IsolationLevel)
  or Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.UseTransaction(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade,System.Data.Common.DbTransaction).
  Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy
  is not used by this method since the SQL may not be idempotent and does not run
  in a transaction. An ExecutionStrategy can be used explicitly, making sure to
  also use a transaction if the SQL is not idempotent.
  

  My question here is how can I use this in my cenario so I can use my methods transaction or in anyway I can able to execute my SQL in same transaction or something : Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.UseTransaction(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade,System.Data.Common.DbTransaction)

  Save Cancel
• 

  0

  alper created před 3 lety

  Support Team Director

  while we check it, here's a workaround to make it transactional in your custom UOW scope.

  private readonly IUnitOfWorkManager _unitOfWorkManager;
     
  public virtual async Task FooAsync()
  {
              using (var uow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: true))
              {
                  //...do your work here...
                  
                  await uow.CompleteAsync();
              }
   }
  

  Save Cancel
• 

  0

  lalitChougule created před 3 lety

  Hi @alper,

  As stated above my custom repo method performs 2 operations

  1. Bulk Update
  2. Bulk Insert

  public virtual async Task CreateQuote()
  {
              using (var uow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: true))
              {
                  //...my code
                  
                  //custom repository bulk update called
                  
                  //ERROR OCCURED
                  
                  await uow.CompleteAsync();
              }
   }
  

  So as per what you are saying all the updates and inserts will be roll backed in the above cenario right ?

  Save Cancel
• 

  0

  alper created před 3 lety

  Support Team Director

  it should roll back.

  Save Cancel