Concurrency control

Hello

I would like to know the best way to implement concurrency control in 3 tier application? May first thought is:

1. A client wants to edit a record from a dataset.
2. send a request to the server ask开发者_运维问答ing a lock on that record
3. the server accepts/denies the edit request based on lock table

Based on this scenario the locks should have a reference to both the record locked and client using that record.

The client has to send periodic keep alive messages to the server. The keep alive is used to free locked records in case we lost the client in meddle of editing operation.

I will be using Delphi with datasnap. Maybe this is a novice question but I have to ask!!

I'm building on jachguate's Optimistic Concurrency Control answer to answer a question posed in comments.

I prefer to use OCC wherever I can because the implementation is easier. I'm going to talk about a three tier app using an object persistence framework. There are three levels to my prferred scheme:

1. row or object level control, where a unique version ID is stored on each object. If you try to update the object the version id is automatically changed. If your version id doesn't match what's already there your update fails.

2. field or column level locking. You send a complete copy of the original object as well as the updated one. Each field in your update has the actual and old values compared before the new value is applied. It's possible to ask the user to resolve the conflicts rather than discarding them, but this becomes messy as the amount of data in the commit increases.

3. pessimistic locking. Each object has a lock owner which is usually null (the object is not locked). When you want to edit the object you first lock it. The problem here is that locks need to be tidied up and the business rules around that can be ugly (what timeout is desirable).

The advantage of this is that most of the time the low-cost OCC path is taken. For things that happen a lot but with low contention the benefits are significant. Think of product tracking in a warehouse - products move all the time, but very rarely do identical items move at the same time, and when they do resolving is easy (quantity left = original less my removal and your removal). For the complex case where (say) a product is relocated it probably makes sense to lock the product while it's in transit (because that mirrors the physical situation).

When you do have to fall back to locking, it's often useful to be able to notify both users and have a communication channel. At least notify the user who wants the lock when it's available, preferably allow them to send a message to the lock holder and possibly even allow them to force the lock. Then notify the lock loser that "Jo Smith has taken you lock, you lose your changes". Let office politics sort that one out :)

I usually drive the fallback process by user complaints rather than bug reports. If users are complaining that they lose their edits too often in a particular process, change it. If users complain that records are locked too often, you will have to refactor your object mappings to increase lock granularity or make business process changes.

I Design my applications with a Optimistic concurrency control in mind, by no locking any record when a user want to edit it nor trying to control concurrency.

Important calculations and updates are done server side (Application or database) after proper built-in database locking functionality is set while processing the updates applied by the client. DataSnap automatic transaction rollback prevent these locks to block other concurrent users in case of failure.

With DataSnap, you have total control to prevent data loss when two users edits collide by appropriate using the ProviderFlags for your fields. Set the pfInWhere for any field you want to check automatically to have the same value at edition/deletion time as when the record was read.

Additionally, when a collision occurs you can react programatically at the Application Server (provider OnUpdateError event), at the client (TClientDataSet OnReconcileError event) or even ask the user for proper conflict resolution (take a look at the ReconcileErrorDialog in the New Item repository).

In the meantime, IMHO avoiding the complexity required to maintain lock lists, client lists, locks-per-client-lists, keep-alive messages, robust application server failure recovery and all possible glitches you'll end with a cleaner and better solution.

The approach given by jachgate is great, and probably better, but in case you do want to implement this, you will need a TThreadList on the server that is created when the service is started. Use the TThreadList because it's thread-safe. You can have on TThreadList per table so that you can minimize the performance hit of navigating the lists. To control what's locked, you'll need an object that's created and passed to the list

  TLockedItem = class(TObject)
  public
    iPK: Integer;
    iClientID: Integer;
  end;

To do the actual locking, you'd need something like this:

function LockItem(pPK, pClientID: Integer): Boolean;
var
  oLockedItem: TLockedItem;
  oInternalList: TList;
  iCont: Integer;
  bExists: Boolean;
begin
  bExists := False;
  if (Assigned(oLockedList)) then
  begin
    oInternalList := oLockedList.LockList;
    try
      if (oInternalList.Count > 0) then
      begin
        iCont := 0;
        while ((not bExists) and (iCont < oInternalList.Count)) do
        begin
          oLockedItem := TLockedItem(oInternalList[iCont]);
          if (oLockedItem.iPK = pPk) then
            bExists := True
          else
            Inc(iCont);
        end;
      end;
    finally
      oLockedList.UnlockList;
    end;
    if (not bExists) then
    begin
      oLockedItem := TLockedItem.Create;
      oLockedItem.iPK := pPK;
      oLockedItem.iClientID := pClientID;
      oInternalList := oLockedList.LockList;
      try
        oInternalList.Add(oLockedItem);
      finally
        oLockedList.UnlockList;
      end;
    end;
  end;
  Result := bExists;
end;

That's just an ideia of what you'd need. You would have to do an unlock method with similar logic. You'd probably need a list for the clients, that would keep a point of each TLockItem held by each client, in case of lost connection. This is not a definitive answer, just a push on the direction, in case you want to implement this approach.
Good luck