topic Re: Transaction (Process ID) was deadlocked on lock resources with another process in Report Server

Transaction (Process ID) was deadlocked on lock resources with another process

3hungdc — Tue, 22 Oct 2024 11:03:51 GMT

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:

I am using Import mode to retrieve data from a SQL Server database.
The dataset is quite large, and there are several joins and filters applied in the queries.

What I've tried so far:

Refreshing the data at different times.
Optimizing my queries by reducing the number of joins and filtering data as much as possible.
I looked into Turned off Parrallel loading , but the issue still occurs.

Are there any recommended best practices to avoid deadlocks in Power BI, How to fix this.

Re: Transaction (Process ID) was deadlocked on lock resources with another process

divyed — Tue, 22 Oct 2024 13:26:04 GMT

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;

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

Re: Transaction (Process ID) was deadlocked on lock resources with another process

3hungdc — Wed, 23 Oct 2024 02:25:27 GMT

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"

Re: Transaction (Process ID) was deadlocked on lock resources with another process

3hungdc — Wed, 23 Oct 2024 03:42:46 GMT

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

Re: Transaction (Process ID) was deadlocked on lock resources with another process

3hungdc — Wed, 23 Oct 2024 03:40:52 GMT

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.

Re: Transaction (Process ID) was deadlocked on lock resources with another process

stefanp2 — Tue, 09 Jun 2026 11:03:02 GMT

i also run in this problem sometimes. I guess I understand your fix, but I am wondering if there is no official fix for that?

Re: Transaction (Process ID) was deadlocked on lock resources with another process

masonreed11t — Wed, 10 Jun 2026 04:13:54 GMT

Deadlocks are typically caused by SQL Server locking conflicts during refresh. I'd recommend capturing a deadlock graph (Extended Events) to identify the queries involved, reviewing indexes, and checking for concurrent database activity during refresh.

Re: Transaction (Process ID) was deadlocked on lock resources with another process

carter_gray705 — Thu, 11 Jun 2026 13:21:58 GMT

Deadlock SQL Server side issue hai, Power BI ka direct problem nahi.

Simple fixes try karo:

Tables par indexes add/verify karo (join/filter columns)
Query ko simple karo ya SQL view bana lo
Refresh ko off-peak time pe chalao
Agar possible ho to enable karo READ_COMMITTED_SNAPSHOT
Large tables ki statistics update karo

Parallel load off karna enough nahi hota—real issue DB locking hota hai.