Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
Hello everyone,
I'm encountering a deadlock issue in Power BI Report Server, and I'm not sure how to resolve it. When refreshing my dataset, I receive the following error:
SessionID: 6b031eca-c092-45d9-b5f4-f0ebdb551880
[0] 0: Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I've tried refreshing the dataset multiple times, but the problem still persists. I understand that deadlocks typically occur due to conflicts between transactions in SQL Server. However, I am unable to identify the specific cause in this case.
Here are some additional details:
Are there any recommended best practices to avoid deadlocks in Power BI, How to fix this.
Solved! Go to Solution.
I already found the problem.
When I checked System_health in extended events.
I see the details of deadlock as you said.
But there, it's not problem about my code. It's about PowerBI Report Server bug.
After translate all these hard-to-understand-xml. I found out that have a problem with table CatalogItemExtendedContent . Made by PowerBI Report Server.
Finally, the deadlock is from sp: InitializeCatalogExtendedContentWrite
The code here:
It deadlock because of that UPDATE.
Now I just need add WITH (ROWLOCK, UPDLOCK).
UPDATE [dbo].[CatalogItemExtendedContent] WITH (ROWLOCK, UPDLOCK)
SET Content = 0x
WHERE ItemID = @CatalogItemID AND ContentType = @ContentType;
Voila . It no longer deadlock in my situation
I already found the solution is in the stored procedure name: "InitializeCatalogExtendedContentWrite"
This sp created bug is not from us. It from PBIRS.
Just go to the Server where PowerBI located.
And add " WITH (ROWLOCK, UPDLOCK)" after the UPDATE command.
It will lock the rows instead of entire table.
And Voila, The deadlock does not appear again.
Hello @3hungdc,
Given the context, deadlocks can be more likely due to high resource contention during data retrieval. Refreshing data create connection to SQL and picks latest data so probably your server has resource intensive operations running (Some DMLs probably).You have already tried usual methods (query optimization, reduction, less joins , parallel processing , reducing data volume etc), you can do some additional steps :
1. Use NOLOCK (to read data ignoring locks) in your select queries whereever applied.
2. Pre-Aggregate Data: Instead of applying complex filters and joins in the Power BI query, consider pre-aggregating the data in SQL Server or a staging table.
3. DirectQuery Mode: If the data is too large and frequent updates are necessary, consider switching from Import to DirectQuery mode, which queries the database in real-time, avoiding memory overload during data import. This will be slower than import but you will save data refresh.
4. Monitor Lock Contention:
Use can use below query in ssms to check potential locking
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
OR
SELECT
blocking_session_id AS BlockingSessionID,
session_id AS VictimSessionID,
wait_type, wait_time, wait_resource,
[status], command, blocking_session_id,
(SELECT text FROM sys.dm_exec_sql_text(request_sql_handle)) AS QueryText
FROM
sys.dm_exec_requests
WHERE
blocking_session_id <> 0;
5. You can use extended events or profilers to find more details about deadlock and can take action accordingly
I hope this helps.
Did I answer your query ? Please mark this as solution if this helps. Appreciate your Kudos 🙂
Cheers
Hi @divyed .
I already set NO LOCK for the query that I think it make Deadlock. But it does not work.
Should I set NO LOCK for all tables query in the report ?
And also. When I check extended events. I saw this message, tell which process got locked. But I still cannot find the solution. Can you help ?
UPDATE
[dbo].[CatalogItemExtendedContent]
SET
ModifiedDate = @ModifiedDate
WHERE
ItemID = @CatalogItemID AND
ContentType = @ContentType AND
<process id="process224256b1468"
I already found the problem.
When I checked System_health in extended events.
I see the details of deadlock as you said.
But there, it's not problem about my code. It's about PowerBI Report Server bug.
After translate all these hard-to-understand-xml. I found out that have a problem with table CatalogItemExtendedContent . Made by PowerBI Report Server.
Finally, the deadlock is from sp: InitializeCatalogExtendedContentWrite
The code here:
It deadlock because of that UPDATE.
Now I just need add WITH (ROWLOCK, UPDLOCK).
UPDATE [dbo].[CatalogItemExtendedContent] WITH (ROWLOCK, UPDLOCK)
SET Content = 0x
WHERE ItemID = @CatalogItemID AND ContentType = @ContentType;
Voila . It no longer deadlock in my situation
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.