Microsoft Fabric - Synapse Data Warehouse - Update Conflict - Concurrency

[Sathish133]

Hi Team,

I have come across the scenario is to run the update statement concurretly to update the data in a same table (Different Rows) in Microsoft Fabric (Synapse Warehouse).

But my most of the statements got failed "due to update conflict" which usually not happened in other Databases like Azure SQL, Oracle, Snowflake.

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

Is it a limitation of Microsoft Fabric Synapse Warehouse Architecture ?

Microsoft Reference : https://learn.microsoft.com/en-us/fabric/data-warehouse/transactions

Anyone faced same kind of issues and how to resolve this ? Any suggestions ?

Regards,

Sathish Kumar Mathivanan

[AndyDDC]

Hi @Sathish133 this is because you can't run concurrent update statements on a Fabric Warehouse table.  If multiple UPDATE statements are issued on a table concurrently, the first update to commit will "win" and the others will be aborted.  This is a limitation of snapshop isolation (which is the only isolation level in Fabric Warehouses).

You can read more in my blog here which covers a few data modification scenarios Transactions and Isolation Levels in Fabric Warehouse (serverlesssql.com)

------------------------------------------

If my answer has been helpful please

consider providing Kudos and marking

the reply as the Solution

------------------------------------------

[Sathish133]

Thanks @AndyDDC for the clarification.

Do you have any alternate solution to implement the concurrency or which is not at all possible in Microsoft Fabric Warehouse.

[AndyDDC]

@Sathish133 currently no you won't be able to use UPDATE (or DELETE) concurrently in the Warehouse service.  Only INSERTs work concurrently.

------------------------------------------

If my answer has been helpful please

consider providing Kudos and marking

the reply as the Solution

------------------------------------------

[AndyDDC]

Hi @Sathish133 if this has answered your question could you please mark as the solution? thanks

[v-cboorla-msft]

Hi @Sathish133 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thank you.

[v-cboorla-msft]

Hi @Sathish133 

Apologies for the issue you are facing.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.
Appreciate your patience.

Thank you.

[Sathish133]

Hi @v-cboorla-msft 

Any update you received from your Internal team on this ?

Regards,

Sathish Kumar Mathivanan

[v-cboorla-msft]

Hi @Sathish133 

Apologies for the inconvenience.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.