Forum Discussion
Prevent database locking using direct query in Power BI
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.
3 Replies
Oh, something else. Is there anything additionally I need to do to prevent locking for System-Versioned tables?
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.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?
