Deadlock on NodeOrder assignment

Justin Naidl asked on October 5, 2017 22:35

I'm getting a bunch of deadlocks and this is what I am seeing in the Event log

WITH To_Be_Ordered
AS
(
SELECT [NodeOrder], ROW_NUMBER() OVER (ORDER BY NodeOrder, DocumentName ASC, NodeAlias ASC) AS CMS_RN FROM (SELECT * FROM (SELECT NodeID, NodeOrder, DocumentName, NodeAlias, NodeParentID, ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY CASE WHEN DocumentCulture = 'en-US' THEN 1 ELSE 2 END) AS Priority FROM CMS_Document LEFT JOIN CMS_Tree ON CMS_Document.DocumentNodeID = CMS_Tree.NodeID OR CMS_Document.DocumentNodeID = CMS_Tree.NodeLinkedNodeID WHERE [NodeParentID] = @NodeParentID) AS LANG_VERSIONS WHERE Priority = 1) AS DOCS WHERE [NodeParentID] = @NodeParentID
)
UPDATE To_Be_Ordered SET [NodeOrder] = CMS_RN

Order isn't important for what I'm doing so I just want to find a way to get the deadlocks to go away.

Correct Answer

Justin Naidl answered on October 9, 2017 21:42

I was able to solve by adding this:

 DocumentEvents.ChangeOrder.AddBefore().Call((e) => e.Cancel()); 

I added a filter so it was only for the page type I was having an issue with.

0 votesVote for this answer Unmark Correct answer

Recent Answers


Matt Nield answered on October 6, 2017 10:19

Justin, do you know what is creating this query? If you have access to the SQL, you could change your query as follows:

WITH To_Be_Ordered
AS
(
SELECT 
    [NodeOrder], ROW_NUMBER() OVER (ORDER BY NodeOrder, DocumentName ASC, NodeAlias ASC) AS CMS_RN 
FROM 
    (
        SELECT * 
        FROM 
            (
                SELECT 
                    NodeID, NodeOrder, DocumentName, NodeAlias, NodeParentID, ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY CASE WHEN DocumentCulture = 'en-US' THEN 1 ELSE 2 END) AS Priority 
                FROM 
                    CMS_Document (nolock)
                    LEFT JOIN CMS_Tree (nolock) ON CMS_Document.DocumentNodeID = CMS_Tree.NodeID OR CMS_Document.DocumentNodeID = CMS_Tree.NodeLinkedNodeID 
                WHERE [NodeParentID] = @NodeParentID) AS LANG_VERSIONS WHERE Priority = 1
            ) AS DOCS 
        WHERE [NodeParentID] = @NodeParentID
    )

UPDATE To_Be_Ordered SET [NodeOrder] = CMS_RN
0 votesVote for this answer Mark as a Correct answer

Justin Naidl answered on October 6, 2017 14:10

Hey Matt, Thanks for the response I have no idea what is creating this. I searched the sprocs and didn't find it also didn't find it in the code base I am working with. I'm assuming it's an internal to Kentico call

0 votesVote for this answer Mark as a Correct answer

Himanshu mishra answered on October 9, 2017 10:30

Hello guys if you loves to play free online game then here on our homepage you will play fireboy and watergirl game online on your pc tablet and smartphone without any download and registration this is the multiplayer game here you have to collect the diamonds to score more in this game.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.