Now that I am curious, what are some good guidelines for function locking in a situation where you have 6 users in an ASP.NET application, where all users work against the same SQL Database.

What is a good way to store the information about what functions are locked?

1. A flag type field in the table where the function is required? In a Customer table, there would be the regular fields such as name, address, and then a locked field? Whenever a user tries to update the record, it first looks to see if the record is locked, if it is, it displays a message, informing the user that the row is locked, and they should try again later? it would also be prudent to disable any input fields that the user would normally interact with.

2. Create a separate table for funciton locking, and have all other tables interact with it? The table would have columns like the following... [TableName], [RowID], [CurrentUser], [CheckOutTime]. If you want to update the object, you have to "Check it out". The checkout process ensures that it is OK to check it out, and if it is ok, adds a row to tthe funciton locking table. When the user is done updating, the row is deleted. If another user tries to update the same piece of data, they recieve a warning, and are informed about who has the object checked out etc?

I guess what I would like is a practicle example of how to impliment this functionality. Because I've never attempted it before, I could really use the advice of someone who has.

Leo

leoduran wrote:

Now that I am curious, what are some good guidelines for function locking in a situation where you have 6 users in an ASP.NET application, where all users work against the same SQL Database.

What is a good way to store the information about what functions are locked?

First define the functions you want to lock, using for example an enumeration definition. Then define a table in which you store the function ID and a user ID plus a time on which the user has locked the feature.

All logic on the higher level in your application (GUI mostly) has to check whether a given user may ENTER a functionality path. He may only do so if the function isn't locked in the database. Do this by always writing a row in the table with locks. if that fails because of a PK violation, the function is locked. You then never have concurrency issues with that table. If the function is locked you can decide to check the time and if that's over a set constant amount of time (say 20 minutes) the lock is removed by a central process (called by your gui logic trying to set the lock) and the lock is tried again.

It's mostly a semantic thing, like role based security interpretation by your application.

(I'm moving this thread to 'Architecture' soon)

I use a separate table. Right now, I only keep track of when a row was locked. Here's my code which manages locks (any constructive criticism is most welcome):

CREATE TABLE dbo.RowLock
(
  ID int IDENTITY (1, 1) NOT NULL,

  TableName varchar(30) NOT NULL,
  PrimaryKey varchar(30) NOT NULL,
  LockDate datetime NOT NULL
    DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT PK_RowLock_ID PRIMARY KEY CLUSTERED (ID),
  CONSTRAINT UC_RowLock_TableName_PrimaryKey UNIQUE NONCLUSTERED
   (TableName, PrimaryKey)
)

using System;
using SD.LLBLGen.Pro.ORMSupportClasses;
using EZPharmSoft.Dms.DatabaseServices;

namespace EZPharmSoft.Dms.BusinessServices
{
  /* EntityLockManager provides pessemistic concurrency services.
   * 
   * Entity locks are rows in the database's RowLock table. This class
   * can create, delete, and find specific entity locks, as well as delete
   * all entity locks. Transactions are not used here because the classes
   * which use this class need to use them (nested transactions aren't
   * supported by the current SqlServer data provider).
   * 
   * See the Concurrency section of the Developer's Guide for 
   * an overview of how this class fits into the business framework.
   */
    public class EntityLockManager : PersistenceServicesBase
    {
        public EntityLockManager(IDataAccessAdapter dataAccessAdapter) :
      base(dataAccessAdapter)
        {
        }


    // Returns true if an entity was successfully locked, otherwise false.
        public bool LockEntity(IEntity2 entity)
        {
          ValidateEntityParameter(entity);
    
              if (FindLock(entity) != null)
              {
                    return false;
              }
              else
              {
                    return DataAccessAdapter.SaveEntity(CreateRowLock(entity), true);
              }
        }

        // Returns true if an entity was successfully unlocked, otherwise false.
        public bool UnlockEntity(IEntity2 entity)
        {
          ValidateEntityParameter(entity); 
        
          RowLockEntity rowLock = FindLock(entity);
    
              if (rowLock != null)
              {
                    return DataAccessAdapter.DeleteEntity(rowLock);
              }
              else
              {
                    return false;
              }
        }

        // Returns a RowLockEntity if a lock is found, otherwise 'null'.
        public RowLockEntity FindLock(IEntity2 entity)
        {
          ValidateEntityParameter(entity); 
    
          RowLockEntity rowLock = CreateRowLock(entity);
    
          bool fetchedEntity = DataAccessAdapter.FetchEntityUsingUniqueConstraint
            (rowLock, rowLock.ConstructFilterForUCTableNamePrimaryKey());

              if (fetchedEntity == true)
              {
                    return rowLock;
              }
              else
              {
                    return null;
              }
        }

        public void UnlockAllEntities()
        {
          DataAccessAdapter.DeleteEntitiesDirectly("RowLockEntity", null);
        }


        private RowLockEntity CreateRowLock(IEntity2 entity)
        {
          RowLockEntity rowLock = new RowLockEntity();
          rowLock.TableName = entity.LLBLGenProEntityName;
          rowLock.PrimaryKey = EntityUtilities.GetPrimaryKeyValues(entity);
    
          return rowLock;
        }

        private void ValidateEntityParameter(IEntity2 entity)
        {
              if (entity == null)
              {
                    throw new ArgumentNullException("entity");
              }
              else if (entity.IsNew == true)
              {
                    throw new ArgumentException
                      (Resources.GetString("NewEntityLockingError"), "entity");
              }
        }
      }
}

-> moved to architecture