cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Data Flows - Extract and Transform with linked tables - Avoid "Cannot acquire lock for model"

Hi everybody,

 

I wanted to create an ETL process using Data Flows in a Premium per User workspace. I tried to stick to Best Practices, so I created one Extraction Dataflow per Data Source and Transformation Dataflows using Linked Tables. Since we have plenty of sources, I tried to create an easy to understand model (Please ignore that "Extract - Data Part 2" has two sources, it is just my Demo data)

boca87_0-1651681245757.png

 

My final Data Flow - Transform and Load - is a merge of the two previous Dataflows. 

When I refresh my two Extraction Data Flows at the same time, they try both to refresh the downstream data flows. One of them will eventually throw an Error "Cannot acquire lock for model...".

 

My ideas to avoid this:

  • Disable load of linked tables - this would also lead to having to schedule my transformations separately, which I would like to avoid. Also I lose some of the advantages of linked tables.
  • Seperate Extraction and Transformation workspaces - this would also lead to separate schedules. Also, the problem would persist, since I sometimes still need to get results of two transformations in another transformation
  • Use a real DWH with proper ETL tools - maybe...

 

Do you have any ideas, links etc. about best practices for ETL in this case? 

 

Thanks in advance

Patrick

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @Anonymous,

 

The error ‘Cannot acquire lock for model… because it is currently in use’ occurs when the dataflow has been blocked which is caused by the refresh logic of linked dataflows. For refresh logic of linked dataflows, you could refer to Link entities between dataflows - Power Query | Microsoft Docs. Here is an example for state the cause. You create a dataflow A. Then you create a dataflow B which connects to dataflow A. When you refresh dataflow B, dataflow A is not allowed to be changed or refreshed. If we change or refresh dataflow A when refreshing dataflow B, this will cause dataflow B to get inconsistent data. So to make sure the consistent, Power BI will block dataflow A from being refreshed or changed when dataflow B is refreshing.

 

To avoid such kind of issue, you could consider schedule the refresh of the dataflows per the refresh logic.

1 Put source dataflows and destination dataflows are in different workspaces, then source dataflows will be treated as the external data sources of destination dataflows. Then, you could set scheduled refresh for them separately. But you need make sure the refresh of source dataflows will be completed before destination dataflows starts to refresh.

2 If you put source dataflows and destination dataflows in the same workspace, only set scheduled refresh on source dataflows since it will event automatically trigger a refresh process for dependent entities in all destination dataflows in the same workspace.

 

Once you get this issue, you could check workspace lineage view then check if there’s overlap between dataflow and linked dataflow. If there’s no refresh overlap and dataflow refreshed failed consistently, please consider raising support ticket.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

View solution in original post

1 REPLY 1
v-cazheng-msft
Community Support
Community Support

Hi @Anonymous,

 

The error ‘Cannot acquire lock for model… because it is currently in use’ occurs when the dataflow has been blocked which is caused by the refresh logic of linked dataflows. For refresh logic of linked dataflows, you could refer to Link entities between dataflows - Power Query | Microsoft Docs. Here is an example for state the cause. You create a dataflow A. Then you create a dataflow B which connects to dataflow A. When you refresh dataflow B, dataflow A is not allowed to be changed or refreshed. If we change or refresh dataflow A when refreshing dataflow B, this will cause dataflow B to get inconsistent data. So to make sure the consistent, Power BI will block dataflow A from being refreshed or changed when dataflow B is refreshing.

 

To avoid such kind of issue, you could consider schedule the refresh of the dataflows per the refresh logic.

1 Put source dataflows and destination dataflows are in different workspaces, then source dataflows will be treated as the external data sources of destination dataflows. Then, you could set scheduled refresh for them separately. But you need make sure the refresh of source dataflows will be completed before destination dataflows starts to refresh.

2 If you put source dataflows and destination dataflows in the same workspace, only set scheduled refresh on source dataflows since it will event automatically trigger a refresh process for dependent entities in all destination dataflows in the same workspace.

 

Once you get this issue, you could check workspace lineage view then check if there’s overlap between dataflow and linked dataflow. If there’s no refresh overlap and dataflow refreshed failed consistently, please consider raising support ticket.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors