Deletes with SQL Server change tracking in a multi sync infrastructure

[maprado-inetum]

Hi all!
I will try to explain my 'problem' in a multi sync infrastructure.
The version of Dotmim.Sync is 0.9.8-beta-0994

I will work with a multi synchronization databases.
One central database in a server and multi client databases in others instances.
All databases are SQL Server instances and i work with change tracking technology. Like this:

To do this, two synchronization consoles (NET Core 5 C#) are run in parallel, one on each client.

I want to synchronize the StateHistoryOnline table only.
The objective is to synchronize between each client and the server, the unique records per client. The parameter to decide the records of each client is found in the table 'Machines' and it is the field 'InstallationId'.
For this, I use the following filtering with a CustomWhere:

All synchronizations have the same template with its corresponding scope, so they always work with the same procedures stored in the central database. Like this:

When I added or updated new rows in 'StateHistoryOnline', the sync works fine. The 'problem' is with deletes.

I will try explain this:

EXECUTE 1. Sync -> Client 1 (PVEGFF) - Server
ADD 5 rows from Client 1: -> OK

EXECUTE 2. Sync -> Client 2 (EGYSO2) - Server
ADD 5 rows from Client 2: -> OK

EXECUTE 3. Sync -> Client 2 (EGYSO2) - Server
DELETE 1 row from Client 2: -> OK

EXECUTE 4. Sync -> Client 1 (PVEGFF) - Server
DELETE 1 row from Client 1: -> OK but Conflicts!!

Why are conflicts generated?
I think this ocurred because the condition OR [side].[sync_row_is_tombstone] = 1 in the scope filter is not correct in my case.
I don't know the correct form of use deletes with change tracking in this situation.
Can you help me??

The complete examples
Dotmim.Sync - #935 Q&A.zip

[Mimetis]

To understand correctly, you need to know how changes are tracked.
Basically, the tracking tables have a 1-1 relation with the main table, using the table Primary Keys.
SQL Server Change Tracking works the same (at least this is how we are using it in DMS)
Tracking the rows changed, and returning the primary keys.

The main problem with Deletes (when you have filters) is that the only thing you have to retrieve the deletes rows is the rows in the tracking tables, identified by the primary keys (and sync_row_is_tombstone = 1)

When rows are deleted, you just can't apply filters (as the rows are deleted :D)

That means that you can't apply filters when you are retrieving the deleted rows, as the rows don't exist anymore in the tables themselves.
That means also that DMS will get ALL the rows WITHOUT filters when retrieving DELETED rows.

That means a row deleted on Client 1 will be sent to all other clients as a delete operation.
But other clients may or may not have this row locally.
When DMS will try to delete this row on the other clients, it will not find it locally and then will raise a conflict.
If you are using an interceptor on the local client, using this kind of code:

agent.RemoteOrchestrator.OnApplyChangesConflictOccured(async args =>
{
    args.Resolution = ConflictResolution.ClientWins;
    var conflict = await args.GetSyncConflictAsync();

    Debug.WriteLine(conflict.Type);
});

You will probably have a conflict type RemoteIsDeletedLocalIsDeleted

In that particular case, DMS will just pass, and will do nothing locally (as the row is not existing) but will increment the conflicts count.

Make sense ?

[maprado-inetum]

Yes Sebastien, I understand.
I will work with this 'particularity' even if I don't like it.
The truth is that the deletions are punctual and it is not the casuistry that occurs most often in my infrastructure.
Thanks as always.
Best regards!

[Mimetis]

I will work with this 'particularity' even if I don't like it.
I don't like it too, but I didn't find a proper way to do it.
If you have any idea, please let me know !!

[maprado-inetum]

I think use a logical DELETE for these table, is my solution.
Add the column IsDeleted and mark that flag. The sync will UPDATE that flag in server and with other daily process delete rows in database.
Then I can stop using the condition to check sync_row_is_tombstone