topic Re: Warehouse: Parallel update on status table fails in Data Warehouse

Warehouse: Parallel update on status table fails

cw88 — Fri, 03 Nov 2023 07:28:08 GMT

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!

Re: Warehouse: Parallel update on status table fails

AndyDDC — Fri, 03 Nov 2023 07:50:24 GMT

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

Re: Warehouse: Parallel update on status table fails

AndyDDC — Fri, 03 Nov 2023 18:31:12 GMT

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

Re: Warehouse: Parallel update on status table fails

HimanshuS-msft — Fri, 03 Nov 2023 18:40:38 GMT

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

Re: Warehouse: Parallel update on status table fails

cw88 — Mon, 06 Nov 2023 12:58:55 GMT

Hi Himanshu,

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

Re: Warehouse: Parallel update on status table fails

AndyDDC — Mon, 06 Nov 2023 15:52:05 GMT

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