2

I'm trying to implement storing tree structures in a simple SQL Server database and access them with EntityFramework:

Id | Path
----------
1  | '/1/'
2  | '/1/x/'
3  | '/1/x/y/'
4  | '/2/'
5  | '/3/'

So, there is one tree with 3 nodes and 2 other trees only consisting of roots. Now, I want to move nodes around, and I came up with the following simple C# function:

/// <summary>
/// Move node with all its decendants to new parent
/// </summary>
public static void MoveNode(int nodeToMoveId, int newParentNodeId)
{
    using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew,new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
    {
        using (var dc = new MyDbContext())
        {
            var nodeToMove = dc.Nodes.Single(x => x.Id == nodeToMoveId);
            var newParentNode = dc.Nodes.Single(x => x.Id == newParentNodeId);
            string oldPath = nodeToMove.Path;
            string newPath = newParentNode.Path + nodeToMove.Name + "/";
            nodeToMove.Path = newPath;
            var subNodesToMove = dc.Nodes.Where(x => x.Path.StartsWith(oldPath)).ToList();
            foreach (var subNodeToMove in subNodesToMove)
            {
                subNodeToMove.Path = subNodeToMove.Path.Replace(oldPath, newPath);
            }

            dc.SaveChanges();
        }
        scope.Complete();
    }
}

What I'm concerned about here is concurrency. I used to be under the impression that using IsolationLevel.Serializable was going to solve all concurrency issues, but turns out it does not place exclusive read locks onto the data (as explained in this answer). So the following situation may occur when 2 threads try simultaneously move one node to different new parent nodes:

thread 1: is trying to move node '/1/x/' to new parent '/2/'
thread 2: is trying to move node '/1/x/' to new parent '/3/'
thread 1: oldPath = "1/x"
thread 2: oldPath = "1/x"
thread 1: newPath = "2/x"
thread 1: nodeToMove.Path = "2/x"
thread 1: subNodesToMove = dc.Nodes.Where(x => x.Path.StartsWith("1/x")).ToList() = [ "1/x/y" ]
thread 1: subNodeToMove.Path = "2/x/y"
thread 1: commit transaction
    At this point the hierarchy is the following:
    /1/
    /2/x/
    /2/x/y/
    /2/
    /3/
thread 2: newPath = "3/x"
thread 2: nodeToMove.Path = "3/x"
thread 2: subNodesToMove = dc.Nodes.Where(x => x.Path.StartsWith("1/x")).ToList() = [ ] <-- EMPTY!
thread 2: commit transaction
    At this point the hierarchy is the following:
    /1/
    /3/x/       <- inconsistency
    /2/x/y/     <- here!
    /2/
    /3/

The question is: how can I make sure that this kind of race condition never happens in my MoveNode method? (note: this code may be executed on different computers simultaneously, so a simple .NET lock will not do) I was thinking about using distributed locking mechanisms here, but maybe there is a simpler solution?

Community
  • 1
  • 1
Andre Borges
  • 1,360
  • 14
  • 37

0 Answers0