Cannot work correctly with DB Transaction?

[TonyHoi]

Hi,

I am testing your v3.10.58 version. It's strange when using BulkInsert or BulkMerge in an Unit Work or Transaction with Oracle DB.

For Example, I am using aspnetboilerplate framework, BulkInsert seems only work when Disable Unit of Work and BulkMerge seems work in own transaction (no rollback even throw exception before completing Unit of Work).

using (var uow = _uow.Begin()) //TransactionScope Unit of Work
{
this.Context.BulkInsert(customers); // it's not work when in a TransactionScope
this.Context.BulkMerge(customers); // its work but seems use own transaction and cannot rollback
this.Context.BulkSaveChanges();
uow.Complete();
}

I'm not sure if it's ABP framework issue, kindly can you give me some advise ?

this is ABP unit of work document

[JonathanMagnan]

Hello @TonyHoi ,

Thank you for reporting,

We are currently downloading/trying this framework to make sure to provide an answer according to it.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @TonyHoi,

Unfortunately, we get some weird error by trying to compile aspnetboilerplate project. Since it's a little bit late here, we will try again tomorrow or during the weekend.

Normally, BulkInsert & BulkMerge doesn't use a transaction unless one is started from the context while BulkSaveChanges always use a transaction even if none have been started (like SaveChanges).

However, since you use a Framework, some behavior could be modified, that's why we want to try aspnetboilerplate for providing a good answer.

Best Regards,

Jonathan

[TonyHoi]

@JonathanMagnan ,
Thank you very much for your quick response. Good Night~.

Sorry for wasting your time, I found BulkInsert should be okay with above framework in unit of work. As my mistake it's not work as throw an exception after uow.Complete executed in same method (rollback by unit of work).

Anyway I'm still testing BulkMerge if okay.

using (var uow = _uow.Begin()) //TransactionScope Unit of Work
{
this.Context.BulkInsert(customers);
uow.Complete();
}
throw exception // it's not work as throw an exception caused rollback of unit work TransactionScope in same method

[TonyHoi]

Hi @JonathanMagnan ,

I tested BulkMerge should not work with TransactionScope, Merge works and not rollback even throw exception in a new transactionScope.

using (var uow = this.UnitOfWorkManager.Begin(System.Transactions.TransactionScopeOption.RequiresNew))
{
BulkInsert(customers);
BulkMerge(customers);
throw new Exception("NOT UPDATE"); //BulkInsert will be rollback but BulkMerge not
uow.Complete();
}

[JonathanMagnan]

Hello @TonyHoi ,

We make the project successful work today with BoilerPlate.

BoiletPlate uses a TransactionScope so everything should work as expected since no transaction needs to be passed to our library.

We made a test, and everything was committed/rollbacked as expected.

We are not sure how to reproduce this issue since it seems impossible to reproduce. Every connection inside the transaction scope automatically uses the transaction.

There is some common cause which may cause this kind of behavior:

• A connection created outside the TransactionScope
• A global context with some concurrency issue scenario on the connection

Do you believe you could provide us a non-working example?

try
{
    using (var uow = _unitOfWorkManager.Begin(System.Transactions.TransactionScopeOption.RequiresNew))
    {
        var list = new List<EntitySimple>();
        list.Add(new EntitySimple() { ColumnInt = 2 });
        var context = new BoilerPlate2DbContext();
        context.BulkInsert(list);

        throw new Exception("Custom...");
        uow.Complete();
    }
}
catch
{
}

using (var uow = _unitOfWorkManager.Begin(System.Transactions.TransactionScopeOption.RequiresNew))
{
    var list = new List<EntitySimple>();
    list.Add(new EntitySimple() {ColumnInt = 2});
    var context = new BoilerPlate2DbContext();
    context.BulkInsert(list);

    uow.Complete();
}

try
{
    using (var uow = _unitOfWorkManager.Begin(System.Transactions.TransactionScopeOption.RequiresNew))
    {
        var list = new List<EntitySimple>();
        list.Add(new EntitySimple() { ColumnInt = 4 });
        var context = new BoilerPlate2DbContext();
        context.BulkMerge(list);

        throw new Exception("Custom...");
        uow.Complete();
    }
}
catch
{
}

using (var uow = _unitOfWorkManager.Begin(System.Transactions.TransactionScopeOption.RequiresNew))
{
    var list = new List<EntitySimple>();
    list.Add(new EntitySimple() {ColumnInt = 4});
    var context = new BoilerPlate2DbContext();
    context.BulkMerge(list);

    uow.Complete();
}

[TonyHoi]

Hi @JonathanMagnan ,

Thanks for your support.

Have you tested above BulkMerge code in Oracle DB ? I tested BulkMerge not rollback in Oracle DB rather than SQL Server DB. It's works in SQL Server DB.

Here is my testing code,

public class BulkExecuter : IBulkExecuter, ITransientDependency
{
	private readonly IDbContextProvider<TonyDemoDbContext> _dbContextProvider;
			
	public ILogger Logger { get; set; }
	public IUnitOfWorkManager UnitOfWorkManager { get; set; }

	public BulkExecuter(IDbContextProvider<TonyDemoDbContext> dbContextProvider)
	{
		_dbContextProvider = dbContextProvider;
	}

	public void BulkMergeBanks()
	{
		#region****Test Batch Merge****
		int istart = 0;
		int customerCount = 10000;
		List<Bank> customers = new List<Bank>();
		for (int i = istart; i < customerCount; i++)
		{
			Bank customer = new Bank()
			{
				Id = "T_" + i,
				BANK_ENAME = DateTime.Now.ToString() + "_TNAME_" + i,
				LAST_UPDAT = DateTime.Now
			};
			customers.Add(customer);
		}
		////
		System.Diagnostics.Stopwatch watch = System.Diagnostics.Stopwatch.StartNew();

		//try
		//{
			using (var uow = UnitOfWorkManager.Begin(System.Transactions.TransactionScopeOption.RequiresNew))
			{
				_dbContextProvider.GetDbContext().BulkMerge(customers);

				throw new Exception("Custom...");
				uow.Complete();
			}            
		//}
		//catch
		//{
		//}

		watch.Stop();

		this.Logger.InfoFormat("{0} Banks are created, cost {1} milliseconds.", customerCount, watch.ElapsedMilliseconds);
		#endregion
	}

}

[JonathanMagnan]

Hello @TonyHoi ,

Thank you, we should have tested with more rows the first time.

When using many entities instead of one, we can successfully replicate this behavior.

We will read more about this subject since it appears PL/SQL doesn't handle transaction scope correctly.

Best Regards,

Jonathan

[JonathanMagnan]

We can now reproduce the error without our library and by using the Rollback method

It appears the following thing must be used:

• Transaction or TransactionScope
• A PL/SQL (BEGIN END)
• ArrayBinding with at least 2 items
• The returning statement

(There is for sure other variation to get the same issue)

using (var ctx = new CodeFirstEntities())
{
    ctx.Database.Connection.Open();
    var transaction = ctx.Database.Connection.BeginTransaction();

    using (var command = (OracleCommand)ctx.Database.Connection.CreateCommand())
    {
        command.BindByName = true;

        command.CommandText = @"
BEGIN
INSERT  INTO ""SYSTEM"".""EF_0104"" (""IntColumn"") VALUES(:P_0) RETURNING ""ID"" INTO :ZZZOUTPUT_0;
END;
";
        int[] array = new int[] { 1, 2 };

        var param1 = command.CreateParameter();
        param1.ParameterName = "P_0";
        param1.DbType = DbType.Int32;
        param1.Value = array;

        var param2 = command.CreateParameter();
        param2.ParameterName = "P_1";
        param2.DbType = DbType.Int32;
        param2.Value = array;

        var param3 = command.CreateParameter();
        param3.Direction = ParameterDirection.Output;
        param3.ParameterName = "ZZZOUTPUT_0";
        param3.DbType = DbType.Int32;
        param3.Value = array;

        command.Parameters.Add(param1);
        command.Parameters.Add(param2);
        command.Parameters.Add(param3);

        command.ArrayBindCount = array.Length;
        command.ExecuteNonQuery();
    }

    transaction.Rollback(); // <-- nothing will rollback!
}

I will search a little bit to find an answer but I may end up by creating a Stack Overflow question with this.

[TonyHoi]

Hi @JonathanMagnan ,

Well noted, thanks for your great invesitigation.

[JonathanMagnan]

Since I'm not sure of the reason why such behavior happen, I have asked the question on Stack Overflow
http://stackoverflow.com/questions/42684657/oracle-transaction-rollback-doesnt-work-insert-arraybinding-returning

I will keep you updated.

[JonathanMagnan]

Hello @TonyHoi ,

We modified the Merge code to make it compatible with Transaction. We do not longer use a PL/SQL.

A new release should be soon available today or tomorrow.

Best Regards,

Jonathan

[TonyHoi]

@JonathanMagnan ,

Thanks for your kindly support.

[JonathanMagnan]

Hello @TonyHoi ,

The v3.11.4 has been released
https://www.nuget.org/packages/Z.EntityFramework.Extensions/

Could you try it and let us know if the issue is fixed?

Best Regards,

Jonathan

[TonyHoi]

Hi @JonathanMagnan ,

I downloaded new version and tested it. It seems BulkMerge do nothing when my oracle table id auto generated by sequence.

Now I am using this Url method to BulkMerge. Thanks anyway.

        using (var uow = this.UnitOfWorkManager.Begin(
                System.Transactions.TransactionScopeOption.RequiresNew))
        {
            List<IF_ProcessLog> iflogs = new List<IF_ProcessLog>();
            IF_ProcessLog testLog1 = new IF_ProcessLog();
            testLog1.Id = 48;//UPDATE TEST
            testLog1.Imp_Action = "TEST1";
            iflogs.Add(testLog1);
            IF_ProcessLog testLog2 = new IF_ProcessLog();
            testLog1.Id = 47;//INSERT TEST
            testLog1.Imp_Action = "TEST2";
            iflogs.Add(testLog1);
            _IF_ProcessLogRepository.BulkMerge(iflogs); 
            //throw new Exception("BULK MERGE CUSTOM");
            uow.Complete(); //Nothing insert or update to DB after commit
        }