VersionedConcurrencyControl error when adding a child entity (after deleting one)

BIZ TMS XData
1

I have two related (1-M) entities: TWell and TWellbore. The relations are defined as:

    // If a well is deleted, ALL associated wellbores must be deleted (from database)
    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAllRemoveOrphan, 'FWell')]
    FWellbores: Proxy<TList<TWellbore>>;

and

    // If a wellbore is deleted, the associated well (parent) must NEVER be deleted (from database)
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [ForeignKey('FK_Wellbore_Well')]
    [JoinColumn('ID_Parent', [TColumnProp.Required], 'ID')]
    FWell: Proxy<TWell>;

I am using two TAureliusDatasets here: adsWell and adsWellbore, with adsWellbore linked to a dataset field in adsWell. When I delete a TWellbore from the child list (by adsWellbore.Delete) all is OK. If immediately thereafter I try to insert a new entity (by adsWellbore.Insert), I get a VersionedConcurrencyControl exception on the TWell (parent) entity:

Status code: 500
Error Code: VersionedConcurrencyControl
Could not perform database operation due to optimistic concurrency control. Entity class: "TWell", Id: "21FF2717-C66E-4719-B644-88CEEB73EFF0", Version: 2'.

I suspect it's caused by the CascadeTypeAll - [TCascadeType.Remove] in the child (TWellbore) to parent (TWell) association but I don't understand why/how as the TWell entity wasn't changed.

Or is it that inserting in adsWellbore, leaves adsWell in edit state and I must always post the 'top-level' dataset (i.e. adsWell)?

Either way, I am not sure how to solve this. Any suggestion is welcome!

Version control with Master-Detail in XData
2

Forgot to mention that the data comes from an XData server through an TXDataClient.

3

That shouldn't be it, because the version control system happens at database level, not dataset level.

I'd suggest you use the OnSqlExecuting event to track exactly which SQL statements are being executed when you delete the TWellbore instance and see if a statement is being executed in the Well table.

If you can inspect it further you can also put a breakpoint in the event and see the call stack at the moment the SQL statement in Well table is being executed, and let us know, to see where that modification is coming from.

4

I have been working with this but I can't seem to "get to the bottom" of this problem. I therefore prepared a small project group to demonstrate the issue(s). It comprises:

  • An "Aurelius-only" application (WellDoc_Aurelius)
  • An XData Server (WellDoc_Server)
  • An XData Client application (WellDoc_XData)

All applications have been configured to build the EXE to a Win32 sub-folder (assuming it's built for Win32), with the generated dcu's in the Win32\dcu sub-folder. The SQLite database (WellDoc.db) will automatically be created in the Win32 folder (either starting the XData Server or the Aurelius client).

In all grids, fields Id, Created, Modified & RowVersion are read-only. Rank and OperationType are Integer fields (any unique value will do).

I have the following observations:

  1. In the Aurelius Client I can add and delete records without any issues (i.e. no concurrency exceptions).
  2. If editing in the Aurelius Client, I note that in Well (top grid) RowVersion remains at 0. The two sub-tables go straight to RowVersion 2 (i.e. skip 0 and 1).
  3. In the Aurelius Client, setting the inherited base fields (Created and Modified) via events works fine.
  4. In the XData Server, I can only subscribe to the OnSqlExecuting event via the TMappingExplorer (Module doesn't have this event). Yet it works, so I wonder what the (subtle) difference is in these subscriptions...
  5. In the XData Client, it can be seen (ref. annotated log file, included in the attached zip file)
    that SQL is executed to update the RowVersion of the parent entity, yet the parent entity is not updated (refreshed) in the TAureliusDataSet. Ultimately, this causes the VersionedConcurrencyControl exception. Clearly this is a update/refresh issue in the parent entity. Is this a bug or am I missing a (mandatory) update/refresh in my code?
  6. In the context of the above (5), it is also interesting that I do need the SetBaseProperties procedure (called from OnBeforePost for all TAureliusDataSets) in the XData Client. If I don't include it, then the fields are null in the dataset on some occasions and overwrite the values created by the OnInserting event (i.e. Created and Modified are (re)set to null).

I hope you can give some guidance on how to solve this? I see that the entire problem can be solved by omitting versioning but the application I'm developing is aimed at multi-user environments (all working on the same TWell-TWellbore-TOperation) so it's almost mandatory to use it.

My conclusion (without finding a solution) is that it's a refresh issue. This should also solved item 6. If entities are updated/refreshed somehow, all fields (Created, Modified and RowVersion) are up-to-date and "in sync" with the database.

Looking forward to some suggestions...

XDataConcurrencyEvents.zip (300.6 KB)

RE: VersionedConcurrencyControl error when adding a child entity (after deleting one)
5

Thank you very much for the project.

It's indeed a refresh issue. Simply put, when you call TXDataClient.Put or Post, the object is sent to the server, updated, but the client does not receive information about the updated data.

So, after a Put, for example, the RowVersion field is not updated.

With current version there is no way to workaround it, using the TXDataClient itself. We have made an improvement here internally, by adding new methods that will return the updated object from the server (which adds overhead, so we need to keep the old version). With that update, you will be able do something like this to properly update the RowVersion:

procedure TfrmXData.ObjectUpdate(Dataset: TDataSet; AObject: TObject);
var
  Updated: TObject;
begin
  Updated := FXDataClient.PutFetch(AObject);
  TData_Base(AObject).RowVersion := TData_Base(Updated).RowVersion;
end;