Failed to Propagate Identity for Multiple Concurrent Insertion

[KohCS]

We are facing an issue where the Auto Increment primary keys are not being propagated when multiple users try to submit data at the same time. We have read through another similar issue in #131, and we are already using Instance Context Mapping.

Description:
Attached code snippet is a test function we did to replicate the problem. It inserts 3 lists of orders concurrently to a_misc table using 3 different threads, simulating multiple users submitting data simultaneously. The screenshot shows the logs of MiscId returned from .Identity() after inserting a_misc, but before inserting into child table a_misc_detail. The issue is that the returned MiscId is sometimes 0 (the default int value), and because of that, inserting into a_misc_detail fails with a foreign key constraint error.

Questions:

1. Is there any solution or workaround for this issue?
2. May I know what is the best practice for handling concurrent insertion like this case?

ErrorMessage: Cannot add or update a child row: a foreign key constraint fails ("db.test"."a_misc_detail", CONSTRAINT "FK_a_misc_a_misc_detail_MiscId" FOREIGN KEY ("MiscId") REFERENCES "a_misc" ("MiscId"))

• Dapper version: 2.0.123
• Dapper Plus version: 7.3.0
• Database Provider: MySql 8.0.30

InsertMisc_snippet.txt

[JonathanMagnan]

Hello @KohCS,

Thank you for the snippet. We successfully reproduced the issue.

This is indeed an error on our side that we were not aware of.

When using a transaction, we cannot lock the table to ensure we return the correct identity value in a concurrency scenario, because LOCK TABLES auto-commits the transaction.

A quick workaround is simply to avoid using a transaction, so our library can lock the table correctly during the INSERT statement.

We are currently investigating to provide a better fix on our side, or at least an option that will properly handle this scenario.

Best Regards,

Jon

[KohCS]

Hi Jon,

Thanks for the detailed explanation. We appreciate that you're looking into a fix to handle this scenario, and we’ll keep an eye out for any updates.

Let us know if you need additional information or if there’s anything else we can help test on our end.