I am building a module to edit a hierarchy of nodes. You can think of it as a very large directory structure with many levels of nested directories and files. The nodes for the hierarchy are stored in a relational database table. The only difference is that there is no such thing as a folder/directory. All nodes have the same characteristics. So a node has a parent that is also a node. And there is only one root node, and a node can only have one parent node, so no poly-hierarchies.
Table columns:
node_id [bigint] not null
name [nvarchar(50)]
parent_node_id [bigint]
leaf_node [bit]
The goal is to figure out a way to edit the one hierarchy without users stepping on each other's toes. We either have to design a version control architecture to resolve conflicts, or else use some locking mechanism (pessimistic or optimistic) to prevent others from editing nodes that's part of an ancestry (or sub-tree) of some given parent node within the entire hierarchy tree. If we perform locks, then everyone using the editor would need to constantly refresh their tree to see the changes of others.
There are only three features to worry about. Editing a node/object in the master tree is only allowed by one user. Dragging and dropping a newly created sub-tree into the master tree. Dragging and dropping a sub-tree within the master tree into another node within the master tree, thus making the dropped sub-tree a child of that node.
I don't see this architecture as anything different than an operating system architecture to manage folders and files. How is that typically done with thousands of users on it? I'd rather use locking mechanisms rather than using version control to make it less complex. I'm just not sure what the best approach is.
Here's my plan so far:
if a node is being edited, don't lock it until the save happens and the database is about to update the record. After the database makes the changes, unlock it. If someone else tries to edit the record at precisely the same time the database is making its changes, don't tell the user it's locked. Have the database handle the lock on the record/node.
if a new sub-tree is being dragged into a master tree parent node, lock the new parent. Then insert the new records and update the root parent to point to the master tree parent node. Then notify all clients to update their master tree. So all locking is taking place on the database side after the drop happens.
if an existing master tree parent node is being dragged into an existing master tree parent node, and becoming a child node, then we should lock the old parent (becoming the new child) and lock the new parent. Then update the parent node of the new child. Then update all clients (all users) and update their master tree. So all locking is taking place on the database side after the drop happens.
For 1 you could use just optimistic lock. (Your current approach implies "last data overwrites existing").
Regarding 2 and 3 - it seems that dragging and dropping can be implemented just by re-linking nodes, so you can make it a little bit easier - without excessive data copying.
In that case you can introduce a link lock, which lock both link ends. That prevents simultaneous movements of "source" node to some other "destination" node; also it prevents sudden "destination" node movement, which should be really a surprise for a client.