SqlSyncChangeTrackingProvider and sync_scope_last_clean_timestamp updates

[slawomirpiotrowski]

Hello,

I am trying to use SqlSyncChangeTrackingProvider with IsolationLevel.Snapshot in my one way synchronisation from server database to multiple client databases (1:N). I use snapshot to avoid blocking changes in original database during synchronisation of the data as with a lot of clients almost always at least some of the are being synchronized.

It looks like it works (I need to disable snapshot isolation level when creating scope_info table as DDL updates are not allowed in snapshot), but it causes frequent exceptions that look like those:

Dotmim.Sync.SyncException: [InternalApplyThenGetChangesAsync]..[InternalApplyThenGetChangesAsync]..[InternalSaveScopeInfoClientAsync]..Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.scope_info' directly or indirectly in database '<database name>' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.scope_info' directly or indirectly in database '<database name>' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__195_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Dotmim.Sync.BaseOrchestrator.InternalSaveScopeInfoClientAsync(ScopeInfoClient scopeInfoClient, SyncContext context, DbConnection connection, DbTransaction transaction, IProgress`1 progress, CancellationToken cancellationToken)
   at Dotmim.Sync.BaseOrchestrator.InternalSaveScopeInfoClientAsync(ScopeInfoClient scopeInfoClient, SyncContext context, DbConnection connection, DbTransaction transaction, IProgress`1 progress, CancellationToken cancellationToken)
ClientConnectionId:b5626876-cde5-40f0-9325-bb539c7640be
Error Number:3960,State:5,Class:16
   --- End of inner exception stack trace ---
   at Dotmim.Sync.SyncAgent.SynchronizeAsync(String scopeName, SyncSetup setup, SyncType syncType, SyncParameters parameters, IProgress`1 progress, CancellationToken cancellationToken)

I can just retry and it usually works, but with increasing amount of synchronized clients the exception becomes more and more frequent.

I have tracked down the SQL that is causing the exception to the one that is generated by SqlChangeTrackingScopeBuilder, method GetUpdateScopeInfoClientCommand: link to the source

I think the source is updating sync_scope_last_clean_timestamp column for all of the scopes, not just the one that is being updated - it is causing conflicts if a lot of clients are trying to update at the same time.

                    IF EXISTS (SELECT t.name FROM sys.tables t WHERE t.name = N'{this.ScopeInfoTableNames.Name}')
                    BEGIN
                        DECLARE @maxVersion bigint;
                        SELECT @maxVersion = MAX(CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id)) 
                        FROM sys.tables T 
                        WHERE CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id) is not null;
                        
                        UPDATE {this.ScopeInfoTableNames.QuotedFullName} SET sync_scope_last_clean_timestamp = @maxVersion;
                    END

Is there any reason why the update is done for all the scopes, and not just currently updated one?

I have tried to adjust the code myself by using custom scope builder like this:

    public class MySqlChangeTrackingScopeBuilder : SqlChangeTrackingScopeBuilder
    {
        public MySqlChangeTrackingScopeBuilder(string scopeInfoTableName) : base(scopeInfoTableName)
        {
        }

        public override DbCommand GetUpdateScopeInfoClientCommand(DbConnection connection, DbTransaction transaction)
        {
            var result = base.GetUpdateScopeInfoClientCommand(connection, transaction);

            var commandText = result.CommandText;

            var pattern = @"SET\s+sync_scope_last_clean_timestamp\s*=\s*@maxVersion\s*;";
            var replacement = "SET sync_scope_last_clean_timestamp = @maxVersion WHERE sync_scope_name = @sync_scope_name;";

            var match = Regex.Match(commandText, pattern, RegexOptions.Compiled);
            if (!match.Success)
            {
                throw new InvalidOperationException(
                    $"Expected SQL fragment was not found in GetUpdateScopeInfoClientCommand. SQL:\n{commandText}");
            }

            commandText = Regex.Replace(
                commandText,
                pattern,
                replacement,
                RegexOptions.IgnoreCase);

            result.CommandText = commandText;
            return result;
        }
    }

    public class MySqlSyncChangeTrackingProvider : SqlSyncChangeTrackingProvider
    {
        public MySqlSyncChangeTrackingProvider(string connectionString) : base(connectionString)
        {
        }

        public MySqlSyncChangeTrackingProvider(SqlConnectionStringBuilder builder)
            : base(builder)
        {
        }

        public override DbScopeBuilder GetScopeBuilder(string scopeInfoTableName)
        {
            return new MySqlChangeTrackingScopeBuilder(scopeInfoTableName);
        }
    }

and it looks like it makes the exception go away, but I wonder if my change doesn't break anything. Any thoughts?

[Mimetis]

Hey @slawomirpiotrowski
Sorry for delay (hollidays).

There is a reason why tracking tables record are not tight to each scope: Legacy / Performances.

You are using the tracking changes feature from SQL Server, and there is no relation beetween the value from CHANGE_TRACKING_MIN_VALID_VERSION() and the scope info

IF you are using the classic SqlSyncProvider, with triggers and tracking tables, the reason is exactly the same: Let me explain:

Each time a record has changed (or is deleted) the tracking table records the state of this record, for all scopes involved.
There is no mention of any scope in the tracking table.

In your case, if you are updating the sync_scope_last_clean_timestamp for only one scope, all the others scope will have a wrong value.

Let's try to explain by a quick example:

Each time you are retrieving the CHANGE_TRACKING_MIN_VALID_VERSION(), you need to understand that a record with a value inferior is outdated and can't be sync (because we don't have the tracking state anymore)

Imagine: CHANGE_TRACKING_MIN_VALID_VERSION has a value of T0. All scopes have this value:

• Scope 1: T0
• Scope 2: T0
• Scope 3: T0

10 days later, you are making a new sync for Scope 1, and then automatically updating the last cleanup timestamp ONLY for this scope (has you are doing in your code)

• Scope 1: T1
• Scope 1: T0
• Scope 3: T0

Scope 2 and 3 are wrong as we don't have the state for all records changed between T0 and T1

Now your problem is coming from this exeception:

Snapshot isolation transaction aborted due to update conflict. 
You cannot use snapshot isolation to access table 'dbo.scope_info' directly or indirectly in database '<database name>' to update, delete, or insert the row that has been modified or deleted by another transaction. 
Retry the transaction or change the isolation level for the update/delete statement.

I don't undersand why you are hitting this kind of error
Are you creating the scope_info table each time you are making a sync ?
Are you using DMS in a classic way or are you doing something specific ?
Are you able to reproduce the error easily ? (if Yes, can you share how ?)

I'll do some search on my side, but I never had this kind of error so far.

[slawomirpiotrowski]

Hello and thanks for your answer.

My code for setting up the synchronisation is non standard in one way. I am enabling snapshot isolation level for source side. I am also using download only direction of synchgronisation and I am configuring separate scope for each destination of synchronisation.

Code looks like this (MySqlSyncChangeTrackingProvider has only the changes shown above, nothing else):

            var sourceMainConnection = new MySqlSyncChangeTrackingProvider(
                _configuration.GetConnectionString("main")
            );
            sourceMainConnection.IsolationLevel = IsolationLevel.Snapshot;
            var destinationMainConnection = new MySqlSyncChangeTrackingProvider(
                string.Format(_configuration.GetConnectionString("mirror"), _serverName)
            );

            var safeServerName = _serverName.Replace('.', '_').Replace('-', '_');
            var mainScopeName = "main_" + safeServerName;
            var mainSetup = new SyncSetup(syncMainTables);
            foreach (var table in syncMainTables)
            {
                mainSetup.Tables[table].SyncDirection = SyncDirection.DownloadOnly;
            }
            var mainAgent = new SyncAgent(destinationMainConnection, sourceMainConnection);

Of couse I also need to enable snapshot isolation support in the database and I need to disable snapshot isolation transaction for first time synchronisation (where scope_info table is created, as altering table structure is not allowed from within snapshot isolation transaction).

The reason why I'm using snapshot isolation is using a lot of frequent, simultanous one way synchronisations (single source, many destinations) with poor connectivity to destination databases. Net effect is always a few synchrosations are ongoing and without snapshot I often block writes to source database. Snapshot works nicely (each and every synchronisation is seeing database's state as it was when given synchronisation transaction started).

Unfortunatelly when sync_scope_last_clean_timestamp is updated updating transaction is done in the same transaction as synchrosation was done, so comparision is done to the state when synchronisation started. It heavily increases a window when update conflict can happen comparing to not using snapshot isolation.

Going back to your example of incorrect data in the database. Yes, after 10 days if I only update scope 1, then scope 2 and scope 3 would have incorrect information. But is that a problem? When is this information used? I mean right before synchronisation of scope 1 (let's say after 9 days, 23 hours and 59 minutes), scope 2 and scope 3 would also have incorrect information (and scope 1 too). I think this information would be updated as soon as those scopes get synchronised next time and the fact that information used to be outdated shouldn't matter? Or is it used somewhere and this can cause problems?

[Mimetis]

Are you able to reproduce the problem , easily, like a small github sample I can use to see the problem, and help you ?

[slawomirpiotrowski]

It's not easily reproducable with a small code sample as I need to make sure that few simultanous synchronisations go out to separate databases and communication channels need to be slow enough to allow for transaction update conflicts. I am not able to reproduce it on a single computer (running both test application and ms sql server).

[slawomirpiotrowski]

@Mimetis let me know if you can reproduce using this: https://github.com/slawomirpiotrowski/Dotmim.Sync-download-only-parallel-test-harness I have used your CreateAdventureWorks.sql adding some extra code to create 10 extra destination databases. The real application doesn't need to synchronise start of synchronisation as data copying takes more time, but in order to make it fail using single computer I had to synchronise start of 10 synchronisations.

[Mimetis]

thanks @slawomirpiotrowski, you made a really good example, and I was easily reproducing the error.
I made a fix by overriding the isolation level using the table hint WITH (READCOMMITTED) for the scope info tables.

I've created the issue here: #1340

I will create a prerelease in the upcoming weeks, but you can test the solution by using the source code directly

Let me know if it's ok for you

Thanks for discovering that issue !!

[slawomirpiotrowski]

Thanks. Your fix works for me. I have managed to cause another problems by merging only changes in GetUpdateScopeInfoClientCommand and skipping updates in GetUpdateScopeInfoCommand. After correctly merging all of the changes it works. Thanks!