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
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