Forum Discussion

arpost's avatar
arpost
Post Prodigy
2 years ago

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 u...
Data Integration
data warehouse
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 Learn

This 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.

arpost's avatar
arpost
Post Prodigy
2 years ago

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:

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi @arpost 
      We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi @arpost 
        We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
        Thanks

    • arpost's avatar
      arpost
      Post Prodigy
      2 years ago

      Can you expound a bit more on both #1 and #2? Trying to think through how that'd work or solve the issue.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi arpost 
        #1
        I presume you are working on a Warehouse. Given that delta tables support transactions (Transactions in Warehouse tables - Microsoft Fabric | Microsoft Learn), you can use a (control) table just to persist the max key, using the strategy of your preferences.

        Just a simple example: Lets suppose you have a table ControlTable with a column id. The following sp will update the id and return the new value atomically, therefore all the processes must invoke this sp to get their unique id.

        CREATE PROC [dbo].[GetMaxId]
    @currentID INT OUTPUT
AS
BEGIN
    -- Start a new transaction
    BEGIN TRANSACTION;

    -- Set the transaction isolation level to SERIALIZABLE
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    -- Get the current ID
    SELECT @currentID = id FROM dbo.ControlTable;

    -- Increment the ID
    SET @currentID = @currentID + 1;

    -- Update the table
    UPDATE dbo.ControlTable SET id = @currentID;

    -- Commit the transaction
    COMMIT TRANSACTION;
END



        Another option would be using GUID instead of a number if the data type is not strictly necessary. You can get a unique identifier by running SELECT NEWID();

        Hope this helps. Please let me know if you have any further questions.