Prevent database locking using direct query in Power BI

[ptepichin]

I have a question regarding how to prevent locking a database if i'm using DirectQuery in PowerBI.

Steps: 

I select a server and database and click OK and don't enter sql syntax in the Advanced Options:

 

I select a table and click Load:

When I right click on the dataset and select edit query I get the following detail 

In order to prevent datalock when i entered sql i used  WITH(NOLOCK) after each table. If I select the table directly is there configuration i need to do to prevent data locks? I'm having to connect to a production database. I might possibly be able to get this data from a replicated database but i'm also concerned if i might get a dirty read if there is a conflict with pulling data during a replication.

 

 

  

[v-yalanwu-msft]

Hi, @ptepichin ;

You can check if these related post can is useful to you.

DirectQuery model troubleshooting in Power BI Desktop - Power BI | Microsoft Learn

Resolve blocking problem caused by lock escalation - SQL Server | Microsoft Learn

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ptepichin]

Thank you for your response. It's very useful. In addition do you know if I need to do further configuration on power bi desktop on the datasets to prevent locking similar to using WITH(NoLock) in the sql query?

[ptepichin]

Oh, something else. Is there anything additionally I need to do to prevent locking for System-Versioned tables?