Forum Discussion
Helper IVWarehouse: Parallel update on status table fails
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
Microsoft EmployeeHello
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
