Snapshot isolation transaction aborted due to update conflict

[amitbhavsar]

We have a stored procedure in fabric warehouse running thorugh a pipeline Stored Procedure activity. It runs only once overnight updating a table which is not updated by anything else so no question of blocking or anything with anything else. We intermittently get Snapshot isolation transaction aborted due to update conflict for the same statement id running over two threads where one  shows Succeeded and other failed:

I'm thinking to add Retry on the pipeline activity running this stored proc but that does not sound a clean solution to me and believe it could still end up in the same situation.

I would apprciate any help on a resolution.

Thanks

[amitbhavsar]

Sorry v-kpoloju-msft for the late reply.

I applied Retry policy as a fallback for that's the only relevant thing in my scenario. The stored proc runs only once overnight hence it cannot clash with anything. If you look at the screenshot, Same Statement ID is used across 2 different nodes. 

[AnmolGan81]

We have encountered this issue on our side, we have added 1 retry and 20mins timeout, we dont get this issue on a regular basis, it somewhat fixed for us.

[v-kpoloju-msft]

Hi @amitbhavsar,

Thank you for getting back and sharing the details. I completely understand your point since the stored procedure runs only once overnight, concurrency from your side isn’t the cause. Seeing the same statement ID executing across two nodes indicates the issue may be related to how Fabric’s distributed engine handles internal parallelism.

As you have already implemented the retry policy (which is the main supported fallback), the next best step would be to open a support ticket with Microsoft Support. This will allow the engineering team to review the backend logs for your workspace and confirm why multiple nodes are picking up the same statement ID.

You can raise support ticket using this link: How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you for using the Microsoft Fabric Community Forum.

[amitbhavsar]

Same stored proc runs only once on a schedule. If you look at the distributed statement id, it is the same. one UPDATE query split into two threads and one succeeds and another fails.

[v-kpoloju-msft]

Hi @amitbhavsar,

Thank you for the follow-up question.

You are right in Fabric Warehouse, a single UPDATE statement can be split into multiple distributed threads under the same query ID. This is expected behaviour of the distributed engine, but when those threads try to touch the same rows, snapshot isolation conflicts can occur (one succeeds, the other fails).

To avoid this, you will need to make the update logic “parallel-friendly.” A few options you can try:

Batch or partition the update so that each thread only works on a distinct set of rows (e.g., by key range). Use locking hints such as UPDLOCK or ROWLOCK to coordinate row access and prevent two threads from conflicting. If it fits your scenario, wrap the update in an explicit transaction with a stricter isolation level (like REPEATABLE READ or SERIALIZABLE). 

This isn’t a bug with your schedule or pipeline, it is simply how Fabric’s distributed execution works. The long-term resolution is to restructure the query (or add locking) so it plays nicely with parallelism.

Kindly refer to this links:
1. Performance Guidelines - Microsoft Fabric | Microsoft Learn
2. Optimized Locking - SQL Server | Microsoft Learn
3. SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - SQL Server | Microsoft Learn
4. Table Hints (Transact-SQL) - SQL Server | Microsoft Learn

Hope this helps you move forward. Please give it a try and let us know how it goes.
Thanks for using the Microsoft Fabric Community Forum and keeping us updated.

[v-kpoloju-msft]

Hi @amitbhavsar,
Thank you for reaching out to the Microsoft Fabric Community Forum.

It looks like your stored procedure is being picked up by two threads at the same time, which is why one succeeds while the other fails with the snapshot isolation conflict. Even though the table isn’t updated by anything else, the same procedure executing twice can still trigger this conflict under Fabric Warehouse’s default snapshot isolation.

The cleanest fix is to make sure only one instance of the procedure runs at a time. You can check your pipeline trigger settings and limit concurrency so that parallel runs don’t happen.

If you want a database-side safeguard, you could adjust your update logic to use explicit locks (e.g., UPDLOCK) or wrap it in a transaction block, which avoids snapshot conflicts by queuing updates instead of aborting. Retrying in the pipeline is an option, but I would recommend treating it as a backup plan rather than the main fix.

Refer these links:
1. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?... 
2. https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers#retry-poli... 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[amitbhavsar]

Many thanks for your reply.

1)
I read the following @ https://learn.microsoft.com/en-us/fabric/data-warehouse/transactions

Only the snapshot isolation level is supported in Microsoft Fabric. If you use T-SQL to change your isolation level, the change is ignored at Query Execution time and snapshot isolation is applied.

2) Table Hints are not allowed either in Fabric Warehouse.

[v-kpoloju-msft]

Hi @amitbhavsar,

You are right in Fabric Warehouse only snapshot isolation is supported, and table hints aren’t allowed, so options like UPDLOCK or changing isolation levels won’t work.

The way to handle this is to:
• Limit concurrency so the stored procedure runs only once at a time (pipeline/activity settings).
• Batch or partition the update so each thread works on a distinct set of rows and avoids row conflicts.
• Keep a retry policy as a fallback, since snapshot conflicts can still occur intermittently.

I understand this feels different from SQL Server, but these patterns (concurrency control, batching, retries) are the recommended approaches in Fabric Warehouse today. 

For reference, here’s the list of supported statements and limitations: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area 
Also refer this link: https://learn.microsoft.com/en-us/fabric/data-warehouse/transactions 

Refer these links:
https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers#retry-poli... 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @amitbhavsar,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @amitbhavsar,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @amitbhavsar,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[amitbhavsar]

@v-kpoloju-msft 

I had to resort to retry policy as a fallback option. 

The stored proc in question runs only once on a schedule. If you look at the distributed statement id in the screenshot, it is the same on both the nodes. One UPDATE query split into two threads and one succeeds and another fails. I beleive we cannot control that threading so retry was the only option left for me.

Many Thanks for all your help on this.