Forum Discussion
Help! Is it possible to use Row_Number with Concurrent Inserts?
Greetings, community. I have a scenario where I need several stored procedures to be able to insert data into the same table. Since Fabric doesn't have an Identity/Auto-increment solution yet, I am using the ROW_NUMBER trick (example here). This works fine in cases where I can run sequential loads, but I am now needing to run numerous inserts into the same table at the same time.
Since the ROW_NUMBER solution depends on getting the Max Key from the destination table, I believe concurrent inserts will conflict since two jobs may start at the same time and use the same max value to start the numbering. ☹️
Does anyone know of a possible way to ensure that duplicate numbers aren't generated? Right now, there's no real way of which I'm aware to do so.
12 Replies
Hi arpost
Thanks for using Fabric Community.
Just wanted to share some information, if that could help you.
In Microsoft Fabric, you can generate unique identifiers in a warehouse table using a workaround technique .
Generate unique identifiers in a warehouse table in Microsoft Fabric - Microsoft Fabric | Microsoft LearnThis method ensures that each row inserted into the table has a unique identifier, even when multiple stored procedures are inserting data into the same table concurrently.
Hope this helps. Please let me know if you have any further questions.Anonymous, thank you for that link. The issue is this doesn't ensure unique identifiers. If two jobs run concurrently, they insert duplicate keys. This workaround gets the max key and uses that for the row-numbering.
In the example below, the yellow items were script 1 and the red were script 2 executed almost simultaneously:
Hi arpost
1) Have you considered a control table for storing and managing that key value?2) I agree that for concurrent load requirement, you will need additional logic to maintain and check for the high water mark value before the insert. If this is a batch process, you can load your data in parallel threads into a stage table, and then have a transform process and use the ROW_NUMBER() over to transform (built IDENTITY column) into the final table. This will be much more simplified process and more optimal throughput.
Please try this and let me know if the issue still persists.
Thanks.
Hi arpost
Thanks for using Fabric Community.
Just wanted to share some information, if that could help you.
In Microsoft Fabric, you can generate unique identifiers in a warehouse table using a workaround technique .
Generate unique identifiers in a warehouse table in Microsoft Fabric - Microsoft Fabric | Microsoft LearnThis method ensures that each row inserted into the table has a unique identifier, even when multiple stored procedures are inserting data into the same table concurrently.
Hope this helps. Please let me know if you have any further questions.
