Trasaction deadlocked on lock resources

Hi jnlucas

Past 7 or 8 tables is the clue. With that many writes running at once, two of them are likely colliding in the warehouse, even when nothing else touches it. Learn says Warehouse locks at table level, and that CREATE, DROP, TRUNCATE and ALTER take a schema lock that blocks other work on that table. I can't see which statements Dataflow Gen2 sends when it writes, so I can't say which pair collides.

If you go back to Dataflow Gen2, split the tables into 2 or 3 dataflows and run them one after another from a pipeline, not side by side. Add a retry with a delay on the pipeline activity too, since Learn says to retry when concurrent transactions conflict.

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

Hi v-saisrao-msft, 

Yes I did, and the problem persists. As I mentioned, there is only one dataflow writing to the warehouse. The solution is currently being developed, and I am the only person working on it, so I can assure you that no one and no other process is writing in the warehouse.

In the end, I implemented the solution using a different approach, avoiding Dataflows Gen 2. 

Thank you all for your help. 

Regards,

HI jnlucas​,

Have you had a chance to review the solution shared by Shubham_rai955​ Murtaza_Ghafoor​ ? If the issue persists, feel free to reply so we can help further.

Thank you.

jnlucas​ 

This error suggest a SQL deadlock in Fabric Warehouse, where multiple transactions compete for the same resources.

Possible reasons:

• Multiple dataflows or pipelines running simultaneously.
• Concurrent writes to the same warehouse tables.
• Overlapping notebook or SQL operations.

Recommended approach:

1. Check concurrent executions in Fabric Monitoring Hub.
2. Avoid overlapping refreshes and concurrent writes to the same tables.
3. Try sequential loading and rerun the failed refresh.

If the issue persists, investigate the specific destination table and loading operation.

If this helps, ✓ Mark as Kudos | Help Other

Hi Shubham, 

Thank you for your quick reply. 

I've already checked all that. The DataFlow Gen 2 is the only process writing to the Warehouse. However, it looks like the DataFlow is processing all its tables in parallel, which might causing the conflict.  

The deadlock occurs every time I run the DataFlow so it's an not occasional issue.

I duplicated the DataFlow and ran it again with fewer tables, and in this case it worked, but as soon as I increased the number of tables beyond 7/8 tables the error started occuring again. 

Any help will be welcome. 

This error usually occurs when two or more transactions try to access or modify the same Warehouse resources at the same time, creating a deadlock.

I would check the following:

1. Check concurrent operations: Make sure no other Dataflow, pipeline, or SQL query is writing to the same Warehouse tables while the Dataflow Gen2 is running.
2. Avoid parallel writes: If multiple entities load into the same Warehouse simultaneously, try disabling parallel loading or splitting the refresh into sequential batches.
3. Check destination tables: Look for overlapping loads, truncate-and-load operations, or other processes that modify the same tables.
4. Retry the transaction: If the deadlock is occasional, configure a retry in the pipeline or orchestration process.
5. Review query performance: Optimize transformations and SQL operations to reduce the time transactions hold locks.
6. Test individual tables: Run the Dataflow with one table at a time to identify which entity causes the deadlock.

I would first check for concurrent writes to the Warehouse, then test the Dataflow entities individually. If the issue persists, capture the timing of the failed refreshes and check whether other workloads were running at the same time.