Concurrent update problem (XDataset/XData)

BIZ TMS RemoteDB
1

Hi,

We are using TMS RemoteDB with Firebird in VCL application. We've run into a concurrency problem and would like advice on the recommended approach.

Setup

One table has three related fields: total = price * quantity. On the client, total is calculated in XDataWebDataSet1.OnBeforePost:

delphi

procedure TForm1.XDataWebDataSet1BeforePost(DataSet: TDataSet);
begin
  DataSet.FieldByName('total').AsFloat :=
    DataSet.FieldByName('price').AsFloat * DataSet.FieldByName('quantity').AsFloat;
end;

Scenario

Initial record: quantity = 1, price = 1500, total = 1500

  1. User A and User B open the same record at the same time.
  2. User A changes quantity from 1 to 2. OnBeforePost calculates total = 2 * 1500 = 3000. Saved: quantity = 2, total = 3000.
  3. User B only changes price from 1500 to 2500. Since User B still sees quantity = 1, OnBeforePost calculates total = 1 * 2500 = 2500. Saved: price = 2500, total = 2500.

Result in the database

quantity = 2, price = 2500, total = 2500

The correct total should be 2 * 2500 = 5000. Each user's update is valid on its own, but the combined result is inconsistent because User B's total was calculated from stale data.

Questions

  1. What is the recommended way to handle this in XDataset/RemoteDB? Is there built-in support for optimistic locking (e.g. a version/timestamp field) so that User B's update is rejected or User B is notified that the record was changed?
  2. Should fields like total be calculated on the server side instead (e.g. in an XData service method, an entity event, or a Firebird trigger) rather than in the client's OnBeforePost?

What is the best practice for this pattern, since many of our documents (orders, invoices) have similar dependent fields?

2

This is not related to RemoteDB, but XData.

  1. And yes, you can use Version attribute for optimistic locking: Manipulating Objects | TMS Aurelius documentation
  2. Ideally the more code and business logic you move to the server, the better.