Forum Discussion
Transaction (Process ID) was deadlocked on lock resources with another process
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
Super UserHello 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