Power BI Deadlock, no issues while ran on SSMS

[Anonymous]

Hi all,

I am having Deadlocks issues while refreshing data in PBI Desktop. The problem occurs only from time to time and If I'll try to refresh one again sometimes it works. There are no problems when I run these queries in SQL SMS.

I have curently some tables from SQL server and some being created (combined of two other tables). When I try to refresh it drops deadlock issue with table being created of other tables.

Is this because tables from server are not fully refreshed?

Table A

Table B

Table C - being created of tables A and B

It drops Deadlocks sometimes on tables A or B and sometimes on Table C.

Any idea what to do?

thanks

daniel

[v-shex-msft]

Hi @Anonymous,

I guess 'table c' is created in the query editor and you haven't turned on 'parallel loading' options, right?

If this is a case, I think it may be caused by the loading order of query tables, I'd like to suggest you turn on 'parallel loading' to prevent this issue.
Power bi is tried to load the merged table before its source data tables. (For example, C required source A and B, but A or B will be loading after C, so it deadlock with loading source data tables)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

thank you for this reply.

This is exactly what I was thinking about in first place and was looking for option where you can set order for tables to load.

Apparently this option was turned on all the time. Shouldn't I turn it off instead, to prevent table C (created in the query editor) from loading while tables A and B are not fully loaded yet?

As mentioned above, IT guy said "We are aware of the problem and this is because we have some issues on server". I know this can be one from many resons why it is deadlocking though.

thanks

daniel

[v-shex-msft]

HI @Anonymous,

How your database connections configured? Is that device running with a heavy workload? If they did not exist enough ideal connection session resources, the parallel loading feature also not works for this scenario.

Regards,

Xiaoxin Sheng

[vanessafvg]

@Anonymous  are you doing reads with (nolock) in your sql?   If not i suggest you do uncommited reads as its possible the tables are updating or there is a table lock on them while you trying to pull down the data.  

[Anonymous]

Hi Vanessa,

I don't really want to use NOLOCK as this may cause some issues with numbers. Although, your post forced me to go to IT guys and ask them about it and they said its something with server itself and they are working on fixing it.

I'll give you point as your reply really helped with getting to the point where I am satisfied.

thanks

daniel