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.

deadlocks nodeorder

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