Warehouse: Parallel update on status table fails

[cw88]

Hi, 

i have the following situation:

1. Load data from several csv/xls files in warehouse staging tables
2. Update a status table with the max timestamp of staging tables

Load and update is located in one parametrized pipeline.

Now i want to run this pipeline in parallel foreach loop to save time, but on the update i get the following error: 

"Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table 'cfgLoadctl_Bronze' directly or indirectly in database 'wh_riskdb_test' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction."

Does anyone have any ideas on how to solve this problem?

Thanks!

[HimanshuS-msft]

Hello  
Thanks for using the Fabric community.
What  pointed  is the core of the problem and I am adding the adding which is relevant for other community members for readablity . Thanks  

Updating Data

What about UPDATEing data? Put simply, if there are multiple open transactions on a single table at the same time, the transaction that commits first wins, the other transactions will rollback and return an error. Yup, it’s not the transaction that started first…which means if you have an UPDATE query that is long running and another UPDATE begins and commits before the long running query, then sorry but your long running UPDATE will fail.

From the MS Docs “Conflicts from two or more concurrent transactions that update one or more rows in a table are evaluated at the end of the transaction. The first transaction to commit completes successfully and the other transactions are rolled back with an error returned.“

Since the snapshot isolation is the only thing offered at this time  , I  suggest you can try to write the max timestamp into a csv file ( WITH APPEND )  and at the end of the process you can read this file and  write into a SQL table . 

Thanks
HImanshu

[cw88]

Hi Himanshu, 

thanks for the (unfortunately very ugly) workaround. i will test this approach.

[AndyDDC]

As I mentioned previously, INSERTs have very minimal chance of locking so I would just only insert into your status table 

[AndyDDC · Accepted solution]

This is because the Warehouse uses optimistic concurrency (snapshot isolation) when writing/reading from tables (based on what delta supports).  You can get clashes when multiple update statements hit the same table in parallel.  Inserts don't cause issues so you may have to modify your process to insert an initial status and then write another row with the new status and timestamp.

I go through transactions behaviour here https://www.serverlesssql.com/transaction-isolation-in-fabric-warehouses/

hope it helps

[AndyDDC]

Hi @cw88 if I have helped resolve your query would you mind marking my reply as the solution to close this thread off?

many thanks