Forum Discussion
Warehouse: Parallel update on status table fails
Hi,
i have the following situation:
- Load data from several csv/xls files in warehouse staging tables
- 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!
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
5 Replies
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
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 DataWhat 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
