MySQL FK constraint failures with BulkSaveChanges / BulkInsert under parallel partitions (works with SaveChanges and with Oracle provider)

[alfredjchiong]

Environment • EF Core: 7 • EntityFramework-Extensions (ZZZ Projects): 7.103.8 • Provider: Pomelo.EntityFrameworkCore.MySql – version 7.0.0 • MySQL Server: 8.0.0 • OS / Runtime: Windows 11 / Debian 12 Containers (.NET 7) • AutoDetectChangesEnabled: <true/false> (please see note below)

When inserting related entities (parent/child with FK) using either BulkSaveChanges or BulkInsert while processing partitions in parallel, MySQL throws: MySqlConnector.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (FBS_SanJose.FBS_CONTABILIDADES.MOVIMIENTOCONTROL_TRANSFINT, CONSTRAINT FK_MOVIMIENTOCONTROL_TRANSFINT_MOVIMIENTOCONTROL FOREIGN KEY (SECUENCIALMOVIMIENTOCONTROL) REFERENCESFBS_NEGOCI…)

The same code path does not fail when: • We switch the provider to Oracle (same data and entity graph). • We run with regular SaveChanges (no bulk). • We keep bulk ops but avoid parallel execution (single partition / serialized flow).

This points to a MySQL-specific interaction of bulk operations + parallelism + FK checks.

This is a simplified sample of code:

// partition parameters into N partitions and run all in parallel
await Task.WhenAll(
    Partitioner.Create(parametros)
        .GetPartitions(degreeOfParallelization)
        .AsParallel()
        .Select(AwaitPartition));

async Task AwaitPartition(IEnumerator<Parametro> partition)
{
    using var scope = _services.CreateScope();
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();

    var parents = new List<MovimientoControl>();          // parent rows
    var children = new List<MovimientoControlTransfInt>(); // child rows referencing parent PK

    // ... populate parents & children with proper FK values ...

    // Variant A
    db.BulkInsert(parents);
    db.BulkInsert(children);

    // Variant B
    db.AddRange(parents);
    db.AddRange(children);
    db.BulkSaveChanges();
}

Only under the parallel partitions does MySQL raise the FK violation during the bulk statements (not always).

What we verified • Entity and DB schema FK mapping is correct; the FK SECUENCIALMOVIMIENTOCONTROL references the parent PK and works with SaveChanges and Oracle provider. • Setting degreeOfParallelization = 1 (i.e., no effective parallelism) avoids the error. • The exception occurs for both BulkInsert (parents then children) and BulkSaveChanges. • In all cases, regular SaveChanges with the same graph succeeds (suggesting ordering in the change tracker is fine when not using bulk).

[JonathanMagnan]

Hello @alfredjchiong,

My developer will take a look at it. However, I’m not sure we’ll be able to reproduce the issue since the case seems quite simple.

I understand the reason for doing this with SaveChanges, since it inserts one row at a time. But when it comes to bulk inserts, is there a specific reason you want to run them in parallel? Isn’t the performance already fast enough? In most scenarios, trying to write to the same table from multiple threads with bulk insert doesn’t provide much benefit—or sometimes none at all.

Best Regards,

Jon

[JonathanMagnan]

Hello @alfredjchiong,

My developer tried but unfortunately wasn’t able to reproduce the issue on his side.

Could you provide us with a small project or more details to help us reproduce it? That would really help us move forward.

As mentioned, for bulk operations the performance is usually already very fast without parallel execution. In many cases, running multiple bulk inserts in parallel on the same table adds little to no performance gain, and sometimes can even cause contention.

Best Regards,

Jon