Open Closed

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


User avatar
0
lalitChougule created

Check the docs before asking a question: https://docs.abp.io/en/commercial/latest/ Check the samples, to see the basic tasks: https://docs.abp.io/en/commercial/latest/samples/index The exact solution to your question may have been answered before, please use the search on the homepage.

  • ABP Framework version: v3.0.4
  • UI type: Angular
  • DB provider: EF Core
  • Tiered (MVC) or Identity Server Seperated (Angular): no / yes
  • Exception message and stack trace:N.A
  • Steps to reproduce the issue:

Hi,

There is a case where I have to update more than 40k records in a single go.

What I tried : 1st Approach

foreach (var item in xyzList_1)
{
    var data = await xyz_repository.GetAsync(item.Some_Id);
    if (data != null)
    {
        data.FieldName = xyz;
        await xyz_repository.UpdateAsync(data);
    }
}

The above code block for 40k invoices was taking too long to execute, So I decided to switch to rawSQL query where I tried to pass all the Id's in IN_CLAUSE as below.

2nd Approach: UPDATE "XYZ_TABLE" SET "FieldName" = 'some_value' WHERE "Id" IN (...)

The above code is in my custom repository , sample given below :

public async Task<int> UpdateRecods(string inClauseData)
{
    return await DbContext.Database.ExecuteSqlRawAsync(UPDATE "XYZ_TABLE" SET "FieldName" = 'some_value' WHERE "Id" IN (inClauseData));

}

1st approach was in UOW transaction and used to roll back if there was any error in the process but is too slow as per performance point of view. 2nd approach is fast but doesn't roll back the changes.

There is a similar thing happening for bulk insert as well. Basically my requirement is to perform BULK CRUD in a single transaction scope.

I need a solution on both aspects which are rollback if any issue and performance. If you can suggest any other approach I am good with it.

Thanks !!!


7 Answer(s)
  • User Avatar
    0
    alper created
    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.

  • User Avatar
    0
    hikalkan created
    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.

  • User Avatar
    0
    liangshiwei created
    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})");
        }
    }
    
  • User Avatar
    0
    lalitChougule created

    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)

  • User Avatar
    0
    alper created
    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();
                }
     }
    
  • User Avatar
    0
    lalitChougule created

    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 ?

  • User Avatar
    0
    alper created
    Support Team Director

    it should roll back.

Made with ❤️ on ABP v9.1.0-preview. Updated on December 05, 2024, 12:19