How to prevent two or more users from editing the same hierarchy in asp.net core - database locking

Question

Would like to prevent two or more users from editing the same hierarchy (Possibly using optimistic locking). My general approach to prevent concurrency(two users editing the same record at same time)was as below, but using this approach (row version) does not seem to work for this hierarchy. Think the reason was when a user updates(add or remove child), the record opens and closes(when a click is made) in a fraction of second. Other user editing the same hierarchy page does not get conflict as the same record would not be open and edited in-between that time period.

HierarchySearch retrieves all children(itemNos) which has same parent. Add or remove icon updates record's 'parentItemNo'. Can anyone suggest me how do I go about throwing conflict in this scenario. Thank you.

Only one table is being used. Table is designed in a way that each record has 'parentItemNo' field. We query for children by using parentItemNo field in 'where' clause. There is no separate record for parent with all associated children as its field.

Model:

public byte[] Timestamp { get; set; }   

DBcontext:

entity.Property(e => e.Timestamp)   
                .IsRequired()   
                .IsRowVersion()   
                .IsConcurrencyToken()   
                .ValueGeneratedOnAddOrUpdate()    
                .HasColumnName("timestamp");   

Below method is called if user adds or removes an item from hierarchy.

    public async Task<itemTable> Update parentItemNo(itemTable item)   
    {   
        var result = await itemDbContext.items   
             .FirstOrDefaultAsync(e => e.itemNo == asset.itemNo);   

       // RowVersioning using Timestamp field- for conflict   
        assetDbContext.Entry(result).OriginalValues["Timestamp"] = asset.Timestamp;   
        
        if (result != null)   
        {   
            result.itemNo = item.itemNo;   
            result.parentItemNo= item.parentItemNo;   
            
            try{   
               itemDbContext.SaveChanges(); // for conflict   
               return result;   
            }   
            catch (DbUpdateConcurrencyException ex)   
            {   
                var entry = ex.Entries.Single();   
                var clientValues = (itemTable)entry.Entity;   
                var databaseEntry = entry.GetDatabaseValues();           
      
                var databaseValues = (itemTable)databaseEntry.ToObject();   

                if (databaseValues.parentItemNo != clientValues.parentItemNo)                      
                return StatusCode(409); // conflict                      
            }   
        }   
        return null;   
    }   

Below method for Hierarchy search:

      public async Task<IEnumerable<string>> GetChild(string parentItemNo)   
       {   
           var result =  itemDbContext.items.Where(p => p.parentItemNo== parentItemNo).Select(p => p.itemNo).ToListAsync(); ;   

        if (result != null)   
        {   
            
            return await result;   
        }   

        return null;   

    }   

Answer 1

If you really need the hierarchy locked, then you will need to lock the parent, not just the child.

For optimistic locking, in a transaction always update both the parent and child, so the parent row version and child row version change in the same transaction. Then on update always join to parent on its id and it’s row version, also join to child on its row version.

implement pessimistic locking, so you can use a lock table, and check out the parent. Only update if you have a valid lock, also the locks should timeout. I also auto renew on update, if the lock has not been used by another.