How bulk operations work

[maprado-inetum]

Hi all.
I don't understand how DMS works to determine which stored procedure (bulk or not bulk) to use when updating N records.
I use sql server change tracking.
For example:
I update 5 fields and according to the log, I see q does a bulkupdate first and then the 5 updates individually.
Al I am not understanding well...

[Mimetis]

Hi @maprado-inetum

When a batch is applied, sometimes, we can have an error during the bulkupdate step (for any reason, Primary Key Violation, Foreign Key Error, Null Exception and so on ...)

Since it's a "All the Batch or Nothing", we don't know which row causes the error...
That's why if we have an error on a batch update, DMS tries again this batch using a per line update.

During the per line update, we will know which row has an error preventing the bulk update to happen.

The thing I don't understand in your screenshot is that it misses a LOG INF saying that DMS is fallback to per line update as we can see in the source code:

Dotmim.Sync/Projects/Dotmim.Sync.Core/Orchestrators/ApplyChanges/BaseOrchestrator.ApplyChanges.cs

Line 425 in 490bb41

this.Logger.LogInformation($@"[InternalApplyTableChangesAsync]. Using per line apply since we had an error on batch mode : {{errorException}}", errorException.Message);

You seem to have a situation where there is no error from the bulkupdate, but DMS still tries to make a per line update ....

1. Which DMS version are you using? (I'm wondering if a previous version may not have the LOG INF in the source code)
   • If you are not using the last version, can you try with the latest version ?
2. Are you able to easily reproduce the bug?
   • If yes, are you able to create a sample repro, and share it with me?

Let me know

[maprado-inetum]

Hi Sébastien.
I'm utilizing v0.9.7 of DMS.
I prepare this example for you but I'm not able to reproduce it, sorry.
The first sync example is in Production enviroment and is not possible share with anyone.

BulkUpdates.Example.zip

[Mimetis]

Ok, let me know if the problems persists.
If yes, we need a way to reproduce it

[maprado-inetum]

The problem is reproduced in production again....
But I don't know why.

[maprado-inetum]

Yes... And it's very slowly.
I supose the problem is with CHANGE TRACKING. What do you think?

[Mimetis]

I don't know
I've published a new beta version 0.9.8 that is basically the same as actual production 0.9.7, but with more details in the logs and errors.
You should try it and see if you have more information.
As the messages returned from errors are different (and error type as well) be sure it won't break anything for you and test it

[Mimetis]

When DMS fallbacks to single per line row, we will have the error message in the logs

[maprado-inetum]

Nothing, nothing.
I found.

[Mimetis]

use the 0.9.8 beta u see in the screenshot ;)

[maprado-inetum]

I update my project with DMS v0.9.8 and when the application runs it breaks with this exception:

These are the nugets updates that my project needs to update to DMS v0.9.8:

My project is NET Core 5 and with DMS v0.9.7 run 'ok'.

What change?

[Mimetis]

I dealt with the same kind of problems recently.
I don't know why, but it was resolved by adding TrustServerCertificate=True; to the connection string.

[Mimetis]

I think it comes from here : https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/breaking-changes#encrypt-true (not sure, just an idea)

[Mimetis]

Looking at your stacktrace, I see you are using EF, so I guess this is the problem you encountered.

[Mimetis]

If trust server certificate does not work, remove TrustServerCertificate=True and replace with Encrypt=False in your connection string

[vunder]

The problem is not exactly in EF. It just triggered it. There is "end of life" for "old" ssl protocols. TLS 1.0 and 1.1 are deprecated. A lot of services were already updated. EF is not the exception. MSSQL 2014 cannot use TLS 1.2 that is why you cannot connect to it.
@maprado-inetum you need either update you database service or, as suggested @Mimetis , use security weak connection by declaring TrustServerCertificate=True in connection string

[maprado-inetum]

Great!
I think with this version bulkupdates works fine.

[Mimetis]

Ok, let me know if the last issue appears again.
I guess logs will be more verbose with more info

[maprado-inetum]

Hello again.
I try to explain what I have seen with a sicronization made in a production environment this morning.
The first synchronization performed with a high number of records (4M) has finished successfully using bulkupdates of about 10,000 in 10,000 records.

However, the next synchronization of about 148 records, the problem has been reproduced again as I show below.
The only difference between the first synchronization and the second one, is that in this second one other clients were also synchronizing in parallel at the same time against the same server.

Could it be that the server decides to switch from bulkpudate to singleupdate when there are many interlocks?

[Mimetis]

Maybe.
Problem here is that you still don't have the error message.
Are you sure you 've migrated all the clients / server to the last beta version 0.9.8 ?

[maprado-inetum]

No, I don't use DMS v0.9.8 on all clients, I thought it was only necessary to use it on one client to reproduce the error.
Getting it to or all production clients is going to take me some time.
I think the best thing to do is to prepare a development environment and try to reproduce it again.
It's going to take me time to do it...

[Mimetis]

The error log is coming from which side ? a client (with version 0.9.8) or the server (which version ?)

[Mimetis]

I'm not sure, but I guess the screenshot is from the server side, right ?
If so, you don't need to upgrade clients, as the problem is coming from the server, right ?

If yes, you just need to upgrade the server as the version 0.9.8 is a minor version, fully compatible with 0.9.7

[maprado-inetum]

Hello everyone.

I would like to take up this problem again, as it happens to me continuously in a productive environment.

Basically, what happens to me is what I showed previously.
When several records in the table are updated and the synchronization is launched, I see in the logs that in the target database is executed first the mass update stored procedure "TableName_ScopeName_bulkupdate" but then (without any error or exception), is executed the unit update stored procedure "TableName_ScopeName_update".

This causes the synchronization of thousands of records to take forever and leads to crashes with the other synchronization consoles.

Has this happened to anyone?
Thanks!

[Mimetis]

Bulk Update tries to update a batch of rows in one single instruction, using TVP and stored procedures.

If an error occurs during the batch operation, SQL Server will not returns the error row that causes the error.
That's why, if an error is making the batch failing, DMS gets all the rows from the batch, and tries to apply them 1 by 1 to catch the error.

The code is here (see fallback line ~~420):

Dotmim.Sync/Projects/Dotmim.Sync.Core/Orchestrators/ApplyChanges/BaseOrchestrator.ApplyChanges.cs

Lines 384 to 467 in 3d8a2fa

	foreach (var syncRow in localSerializer.GetRowsFromFile(fullPath, schemaChangesTable))
	{
	rowsFetched++;
	// Adding rows to the batch rows
	if (batchRows.Count < this.Provider.BulkBatchMaxLinesCount)
	{
	if (applyType == SyncRowState.Modified && (syncRow.RowState == SyncRowState.RetryModifiedOnNextSync || syncRow.RowState == SyncRowState.Modified))
	batchRows.Add(syncRow);
	else if (applyType == SyncRowState.Deleted && (syncRow.RowState == SyncRowState.RetryDeletedOnNextSync || syncRow.RowState == SyncRowState.Deleted))
	batchRows.Add(syncRow);
	else if (syncRow.RowState == SyncRowState.ApplyModifiedFailed || syncRow.RowState == SyncRowState.ApplyDeletedFailed)
	errorsRows.Add((syncRow, new Exception("Row failed to be applied on last sync")));
	if (rowsFetched < batchPartInfo.RowsCount && batchRows.Count < this.Provider.BulkBatchMaxLinesCount)
	continue;
	}
	if (batchRows.Count <= 0)
	continue;
	command.Connection = runner.Connection;
	command.Transaction = runner.Transaction;
	var (rowAppliedCount, conflictSyncRows, errorException) = await this.InternalApplyBatchRowsAsync(context, command, batchRows, schemaChangesTable, applyType, message, dbCommandType, syncAdapter,
	runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
	if (errorException == null)
	{
	// Add applied rows
	appliedRows += rowAppliedCount;
	// Check conflicts
	if (conflictSyncRows != null && conflictSyncRows.Count > 0)
	foreach (var conflictRow in conflictSyncRows)
	conflictRows.Add(conflictRow);
	}
	else
	{
	// we have an error in the entire batch
	// try to fallback to row per row
	// and see if we can still continue to insert rows (excepted the error one) and manage the error
	this.Logger.LogInformation($@"[InternalApplyTableChangesAsync]. Using per line apply since we had an error on batch mode : {{errorException}}", errorException.Message);
	// fallback to row per row
	syncAdapter.UseBulkOperations = false;
	(command, isBatch) = await this.InternalGetCommandAsync(scopeInfo, context, syncAdapter, dbCommandType,
	runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress);
	cmdText = command.CommandText;
	foreach (var batchRow in batchRows)
	{
	if (batchRow.RowState == SyncRowState.ApplyModifiedFailed || batchRow.RowState == SyncRowState.ApplyDeletedFailed)
	{
	errorsRows.Add((batchRow, new Exception("Row failed to be applied on last sync")));
	continue;
	}
	if (applyType == SyncRowState.Modified && batchRow.RowState != SyncRowState.RetryModifiedOnNextSync && batchRow.RowState != SyncRowState.Modified)
	continue;
	if (applyType == SyncRowState.Deleted && batchRow.RowState != SyncRowState.RetryDeletedOnNextSync && batchRow.RowState != SyncRowState.Deleted)
	continue;
	command.CommandText = cmdText;
	command.Connection = runner.Connection;
	command.Transaction = runner.Transaction;
	var (singleRowAppliedCount, singleErrorException) = await this.InternalApplySingleRowAsync(context, command, batchRow, schemaChangesTable, syncAdapter, applyType, message, dbCommandType,
	runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
	if (singleRowAppliedCount > 0)
	appliedRows++;
	else if (singleErrorException != null)
	errorsRows.Add((batchRow, singleErrorException));
	else
	conflictRows.Add(batchRow);
	}
	// revert back bulk operation
	syncAdapter.UseBulkOperations = true;
	}
	batchRows.Clear();
	}

If you are using the last pre release version, you may have the error in the logs

[maprado-inetum]

I understand that I should see a log trace like the one on line 425.

In my productive environment I don't see that trace and we have configured the SyncProgressLevel = Sql

The version of Dotmim.Sync is 0.9.8-beta-0994.

Yesterday log for example:

[Mimetis]

Is it log from the server side or from the client side ?
What is the default strategy for resolving conflict ? ServerWins or ClientWins ?
Do you have conflict resolution count after this huge update batch ?

[maprado-inetum]

These log traces are server-side.

I think at the time you proceed to execute the stored procedures on the server.

We use a ClientWins strategy.

And no, it does not produce a conflict count.

[Mimetis]

Interesting ...
I don't see, in the code, where the Update call can be made without a log..
That's strange and weird ....

Are you able to reproduce the bug easily or is it nearly impossible for you to provide a sample ?

[maprado-inetum]

I could provide you with a test scenario, but it is impossible for me to reproduce it.

In the test/development scenario, I work with local DBs and I think this hides what might be happening...

Dotmim.Sync - #935 Q&A.zip

[maprado-inetum]

One question.
How do the locks work between the different transactions when executing the stored procedures in this case on the server?

[Mimetis]

depends if you have the options DisableConstraintsOnApplyChanges or not

If DisableConstraintsOnApplyChanges==true then the entire table is locked during the operation (because we are disabling the constraints for all rows)

If DisableConstraintsOnApplyChanges==false then only the rows updated / deleted are locked (well, the leaf pages in reality)

[maprado-inetum]

Good night!

@Mimetis I think that you can reproduce my problem with this scenario.

All necesary is in the ZIP file. View README first.

Thanks for all!

https://we.tl/t-gTdSAOVKLl

[Mimetis]

can you share the console source code ?

[Mimetis]

I made the test and did not notice anything strange...
The logs you are outputting on the console makes everything very slow, but at least it worked.
Will take a look at the source code if you can share it with me.

[maprado-inetum]

Good morning.

This is the source code of the console.

Dotmim.Sync.Console(Source).zip

Making massive inserts of 100000 records in the 10 clients at the same time.

You can see how from executing the procedure of massive inserts/updates, it uses the unitary one.

View of SQL Server instance activity monitor

[Mimetis]

I'm working on it.
I found what the problems are:

• Connection Timeout : Seems connection timeout was not applied on the bulkupdate command. It's fixed (but it was not your problem)
  

• Deadlocks : Deadlocks makes the batch fail
  

The problem with deadlocks is that it really depends on a lot of factors.
I'm working on resolving the issue, but I'm facing others strange behaviors.

What I can suggest so far:

• Makes smaller bulk insert batch: 50 000 is too much and it will lock too much leaf pages. Use between 5 000 and 10 000
• Use Full Transaction on Server side (not batch)
• Use Snapshot isolation on server side (not from DMS but from the databse itself):

Alter database [{dbName}] SET ALLOW_SNAPSHOT_ISOLATION ON;  
Alter database [{dbName}] SET READ_COMMITTED_SNAPSHOT ON;

Try to limit logs to INFORMATION as the other one will grab the query (and then will slow dramatically the time to apply a batch)

I'm still working on it but will not be available this week.
Will continue to work on it early February

[maprado-inetum]

Thank you very much, Sébastien.

I will follow your advice to minimize problems in the production environment.

We will continue in February ;)

[maprado-inetum]

Hello.

I minimized problems with this configuration:

• In DMS, when configuring the server provider, isolation level is ReadUncommitted
• I used per batch transactions on the serve side.

I used this configuration because in production enviroment it's not possible use snapshot isolation.

with this the production synchronizations are stable but very slow.

My biggest problem is still the unit inserts.

I need a solution for DMS to use bulk insertion (stored procedures 'bulkupdate' and 'bulkdelete').

Regards.

[Mimetis]

Hey @maprado-inetum
I'm currently working on a solution where transient errors are handled correctly (like deadlocks for instance)
In that case, you will have the information in the logs, and even an interceptor.
The transaction will be rerun a few seconds later (5 times) before raising an error.

I need a little bit more time to stabilize and test everything, but I guess I should have something available until the end of January.
Will update you as soon as I have something to share.

[Mimetis]

hey @maprado-inetum
I've release a new pre-release version 0.9.8-beta-1375 that's handle transient errors (see PR #949)

You should see less fallback to line per line inserts / updates due to timeout or deadlocks.

In my opinion, you still should to:

• Makes smaller bulk insert batch: 50 000 is too much and it will lock too much leaf pages. Use between 5 000 and 10 000
• Batch mode or Full transaction on server side is ok now.
• Try to limit logs to INFORMATION

In logs you will have now more information when deadlocks happening or why DMS fallbacks to 1 line per 1 line.

You don't need to update your clients to the last version as it seems your problem is coming from server side.
Just update your server side with last pre-release version (1375)

Let me know if it's making a difference.

[maprado-inetum]

Hi @Mimetis.

The tests I have performed in my development environment seem satisfactory.

I still see a lot of deadlocks when the 'bulkupdate' procedure is executed on the server but at least the update operation/transaction is always retried with this bulk procedure.

As I say, this is in the development environment. This week I will deploy this version to the production environment and I will comment here the results.

Thanks as always!

Regards.

[maprado-inetum]

Hi @Mimetis.

Unfortunately the tests in the production environment are not being satisfactory.

It is observed that the execution of the bulk procedure on the server takes too long. Even more than with the previously used DMS version.

I have configured a PerBatchs synchronization and with a maximum of 1000 add/updates.

Now, I see that the problem may be in the instance of the server dbdd and how the lock is kept in the stored procedure.

Do you know any way to optimize this?

[maprado-inetum]

Thirty days of retention period.

[Mimetis]

I think it's too much in my opinion.
Maybe your bulkupdate is slow because your tracking table is too heavy (and scan takes too much time)
You should reduce the retention period and make a manual cleanup, and see if updates are still slow after

[maprado-inetum]

Hi @Mimetis.
Finally I manually clean and reduce de retention policy of change tracking and sync works fine.
Thanks for all.

[Mimetis]

did you gain a lot of performances since your clean up ?
I mean how percentage time did you gain?

[maprado-inetum]

My production configuration is:

• 8 MB batch size
• 2500 max batch lines
• Transactions per batch
• On server side, Read.Uncommited insolation and 7 days change tracking retention policy
• On client side, Read.Commited insolation and 30 days change tracking retention policy

I believe that with this configuration and after cleaning the change tracking the improvement has been more than 60-70%.

With these results, I have a stable and valid synchronization within the production infrastructure.

Thanks for everything.