Forum Discussion
Help! Is it possible to use Row_Number with Concurrent Inserts?
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.
Is the use of the Serializable isolation level above supported in Fabric, though? Per this doc, only snapshot isolation level is supported; all others are ignored at time of query execution:
https://learn.microsoft.com/en-us/fabric/data-warehouse/transactions#transactional-capabilities
I've thought about GUIDs, but my concerns are two-fold:
- Millions of records will require millions of GUIDs.
- I could be wrong, but wouldn't GUIDs complicate things like drop tables and reinsert updated records, which is essential given that Fabric doesn't support ALTER TABLE?
Hi arpost
The internal team has replied as follows:
Good catch... it appears that the customer is employing an OLTP-like process to populate the warehouse. While warehouses prioritize performance over integrity, it might be beneficial for them to reassess their hydration process and make it sequential to avoid these conflicts. Here is another example but I am afraid it may show the same issue under concurrent calls.
https://learn.microsoft.com/en-us/fabric/data-warehouse/generate-unique-identifiers
I would recommend the approach I mentioned earlier, in which the stored procedures can concurrently load into a table, and then CTAS (historical load)/INSERT(incremental) into the final table with the ROW_NUMBER.
Hope this helps.I appreciate the reply, but that approach still introduces a lot of complications around job sequencing. Do we know if/when Microsoft is planning to add some kind of identity feature?
There are often cases where you need to have your primary keys (PK) generated in order to use those PKs as foreign keys (FK) in dependent tables. The lack of an auto-generated identity means all of your pipelines have to be built in such a way that they insert into the PK table, wait until all other loads complete, and THEN resume with inserts into the dependent tables that need to use the PK as an FK. To make matters worse, that means unrelated jobs have to wait on each other (File Type A's load is held up by File Type B's load).
Typically, any given pipeline would run through steps of a process for a particular ELT job. So the logic would be something like this:
- Raw data would be staged via Copy activity into DW.
- "Parent" dimension records would be inserted.
- "Child" dimension records would be inserted with FK references to the PK for #2.
- Dimension keys for #2 and #3 would be assigned to staged data.
- Data with keys would be loaded from stage to final DW tables.
Having to assign PKs at the end for the final table load introduces a huge amount of overhead if you have, say, 15 different data feeds that all insert into the same tables for #2 and #3.
Anonymous, any update on this? Also, I saw mentioned somewhere that some kind of Identity/Primary Key functionality was planned for 2024. Can you confirm this is in Microsoft's scope? I didn't see this listed in any release plan docs.