BulkInsert doesn't generate primary key for multiple concurrent submission

[Sashi13]

Hi. Recently I am trying to simulate multiple submission using JMeter. Right now I am facing issue where the primary key is not generated when the data try insert to table. I tried 3 concurrent submission at a time. Below I have provided the code. Each transaction contains 10k serials.

DapperPlusManager.Entity<a_item_serial>().UseBulkOptions(options =>
{
    options.InsertIfNotExists = true;
    options.InsertKeepIdentity = true;
}).Key(x => x.Serial);
foreach (var serial in receivingList.ItemSerialList)
{
    a_item_serial newSerial = new a_item_serial()
    {
        Serial = serial.SerialNo,
        a_receiving_serial = new List<a_receiving_serial>
        {
          new a_receiving_serial
          {
              ReceivingDetailId = serial.DetailId,
          }
       }
    };
    dapper_item_serial.Add(newSerial);
}
using (var connection2 = new DataEntities().Database.Connection)
{
    connection2.Open();
    using (var trans = connection2.BeginTransaction())
    {
        try
        {
            DapperExtensions.pipeline.Execute(() =>
            {
                trans.BulkInsert(dapper_item_serial).ThenForEach(x => x.a_receiving_serial.ToList().ForEach(r => r.ItemSerialId = x.ItemSerialId)).AlsoBulkInsert(x => x.a_receiving_serial);
                trans.Commit();
            });
        }
        catch (Exception ex)
        {
            resp.Success = false;
            resp.Message = "Something went wrong. Please retry transaction.";
            trans.Rollback();
            Logger.LogWithConn(TransactionType.Pairing.ToString(), new List<string> { "Error: " + LogHelper.GetExceptionMessage(ex) });
            return resp;
        }
    }
}

Error: Cannot add or update a child row: a foreign key constraint fails (antz_padini.a_receiving_serial, CONSTRAINT FK_a_item_serial_a_receiving_serial_ItemSerialId FOREIGN KEY (ItemSerialId) REFERENCES a_item_serial (ItemSerialId))

I have added polly to retry transaction in case of any deadlock. Thank you.

• Dapper version: 2.0.123
• Dapper Plus version: 5.0.0
• Database Provider: MySql

[JonathanMagnan]

Hello @Sashi13 ,

Do you want your key to be generated or not? In your title, you specify BulkInsert doesn't generate primary key, but you use the InsertKeepIdentity = true option (to insert the identity value from your entity, so doesn't generate it).

I believe there is something we do not understand correctly in your request.

Is it possible to have more information or create a runnable project with the issue? It doesn’t need to be your project, just a new solution with the minimum code to reproduce the issue.

Best Regards,

Jon

[Sashi13]

Hi @JonathanMagnan

Thank you for the reply. Sorry I forgot to remove the InsertKeepIdentity option. Anyhow it still didn't fix my problem. The primary key is auto generated by mysql during insert. The problem happens when I try bulkinsert the dapper_item_serial primary key for the data is not auto generated by mysql (the primary key is set to auto increment) and it returns as 0 (exception is thrown during the insert I have attached the exception below). This only happens if multiple user submit the result to the backend at same time. If one user submit at different timestamp then everything runs as intended.

StackTrace:

at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at .(DbCommand , BulkOperation )
   at .( , DbCommand )
   at .Execute(List`1 actions)
   at .(List`1 )
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.BulkOperations.BulkOperation.BulkInsert()
   at Z.Dapper.Plus.DapperPlusAction.Execute()
   at Z.Dapper.Plus.DapperPlusActionSet`1..(Object )
   at System.Collections.Generic.List`1.ForEach(Action`1 action) in /_/src/libraries/System.Private.CoreLib/src/System/Collections/Generic/List.cs:line 554
   at Z.Dapper.Plus.DapperPlusExtensions.AlsoBulkInsert[T](IDapperPlusActionSet`1 actionSet, String mapperKey, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusExtensions.AlsoBulkInsert[T](IDapperPlusActionSet`1 actionSet, Func`2[] selectors)
   at Antz.Core.V2.Logics.Receiving.<>c__DisplayClass40_4.<UpdatePadiniPairingSerials_Retry>b__15() in D:\Projects\StandardIMS\ANTZ_IMS\Antz.Core\V2\Logics\Receiving.cs:line 8683
   at Polly.ResiliencePipeline.<>c.<Execute>b__28_0(ResilienceContext _, Action state)

Exception:

Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll
Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll
Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll
Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll
Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in Z.Dapper.Plus.dll
Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in Z.Dapper.Plus.dll

[JonathanMagnan]

Hello @Sashi13 ,

My developer tried, but everything worked as expected.

Having a project that reproduces this issue will surely ensure us that we have the same configuration as you. You can send it in private here: info@zzzprojects.com

Best Regards,

Jon

[Sashi13]

Hi @JonathanMagnan,

I have sent the project file to the specified email. Sorry for the delay.

Thank you

[JonathanMagnan]

Hello @Sashi13 ,

I should have known faster what is the possible cause of this issue.

You are currently using the Dapper Global Context Mapping instead of using an Instance Context Mapping

What this means is that the mapping keeps being modified in a concurrency scenario, which could impact options and mapping when executing the Bulk Insert.

If the mapping is always the same (or you use a mapper key for different mapping, that's perfectly fine to use the global context but somewhere that will only called once, so not in a method.

Could you try again by either:

• Make sure that the mapping is only done once if you are using the Global Context Mapping
• By using an Instance Context Mapping in your method

Let me know if the possible cause and solution are clear.

Best Regards,

Jon

[JonathanMagnan]

Hello @Sashi13,

Since our last conversation, we haven't heard from you.

Did the solution proposed work?

Let me know if you need further assistance.

Best regards,
Jon

[Sashi13]

Hi sorry for late reply. Yeah it did fix the issue. Thank you.