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

lalitChougule created 4 years ago

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

Go to accepted answer

7 Answer(s)

0
alper created 4 years ago
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.
0

hikalkan created 4 years ago
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.

liangshiwei created 4 years ago

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

0
lalitChougule created 4 years ago
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)

alper created 4 years ago

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

lalitChougule created 4 years ago

Hi @alper,

As stated above my custom repo method performs 2 operations

Bulk Update
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 ?

0

alper created 4 years ago
Support Team Director

it should roll back.

Have an answer to this question? Log in and write your answer.