Concurrency control

Question

Hello
I would like to know what is the best way to implement concurrency control in a three-tier application? At first I thought:

• The client wants to edit the record from the data set.
• send a request to the server with a request to block this record
• the server accepts / rejects an edit request based on the lock table

Based on this scenario, the locks should have a link to both the record locked and the client using this record.
The client must periodically send messages to the server. Saving alive is used to release locked records in case we lost the client in edit mode.

I will use Delphi with datasnap. This may be a newbie, but I have to ask !!

Answer 1

I built on jachguate Optimistic Concurrency Control answered the question asked in the comments.

I prefer to use OCC wherever I can, because the implementation is simpler. I will talk about a three-level application using the structure of saving objects . There are three levels to my proposed scheme:

• control of the level of lines or objects where a unique version identifier is stored on each object. If you try to update the object, the version identifier will automatically change. If your version identifier does not match what has already happened, your update does not work.

• Lock fields or columns. You send a full copy of the original object, as well as the updated one. Each field in your update has actual and old values ​​compared before applying the new value. You can ask the user to resolve conflicts rather than discard them, but this becomes erratic as the amount of data in the commit increases.

• Pessimistic blocking. Each object has a castle owner, which is usually null (the object is not locked). When you want to edit an object, you first lock it. The problem here is that locks need to be picked up, and the business rules around can be ugly (which timeout is desired).

The advantage of this is that most of the time, an inexpensive OCC path is taken. For things that happen a lot, but with low competition, the benefits are significant. Think about tracking a product in a warehouse - products are constantly moving, but very rarely the same items are moving at the same time, and resolving them is easy (left quantity = original less than my removal and deletion). For the difficult case when (say) a product moves, it probably makes sense to block the product during its transit (since this reflects the physical situation).

When you have to return to the lock, it is often useful to be able to notify both users and have a communication channel. At the very least, to notify the user who wants the lock when it is available, it is advisable to allow them to send a message to the lock holder and, possibly, even allow them to lock. Then tell the blocking loser that "Joe Smith made you block, you lost your changes." Let office politics sort it :)

I usually manage the fault-tolerant process with user complaints, not error messages. If users complain that they too often lose their rights in a particular process, change it. If users complain that records are locked too often, you will have to reorganize object mappings to increase the granularity of the lock or change the business process.

Answer 2

I create my applications using Optimistic concurrency control , without blocking any record when the user wants to edit it or try to control concurrency.

Important calculations and updates are performed on the server side (application or database) after installing the built-in database locking function when processing updates used by the client. Automatic DataSnap transaction rollback prevents these locks to block other concurrent users in the event of a failure.

In DataSnap, you have full control to prevent data loss when two user conflicts collide using the appropriate ProviderFlags fields for your fields. Set pfInWhere for any field that you want to check automatically to have the same value at the time of release / deletion as when reading a record.

In addition, in a collision, you can program the program on the application server (OnUpdateError event of the provider), on the client (TClientDataSet OnReconcileError event), or even ask the user about the correct resolution of conflicts (look at ReconcileErrorDialog in the New Item Repository).

At the same time, IMHO avoids the complexity necessary to maintain lock lists, client lists, lock lists for each client, activity messages, reliable recovery of application server failure and all possible failures that you will end with a cleaner and the best solution.

Answer 3

The jachgate approach is excellent and probably better, but if you want to implement this, you will need a TThreadList on the server that is created when the service starts. Use a TThreadList because it is thread safe. You can have a TThreadList for each table so that you can minimize performance when navigating lists. To control the lock, you need an object that was created and transferred to the list

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

To accomplish the actual lock, you 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; 

This is just the ideology of what you need. You will need to make an unlock method with the same logic. You probably need a list for clients that will support the point of each TLockItem stored by each client in case of a lost connection. This is not the final answer, just a push in the direction if you want to implement this approach.
Good luck.