Retrying on transient errors on server provider - Any best practices?

[gentledepp]

Hello!

We have an older version of dotmim.sync in use.
When lots of clients (~100) sync the same table, deadlocks can occur.
These happen when multiple sp_select_changes are running while another user tries to update records.

These, hoewver do not take long and could be retried using Polly

We are currently doing this in the WebProxyServerProvider.HandleRequestAsync

Dotmim.Sync.SyncException:
at Dotmim.Sync.CoreProvider+d__95.MoveNext (Dotmim.Sync.Core, Version=0.2.1.0, Culture=neutral, PublicKeyToken=null)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at Dotmim.Sync.Web.Server.WebProxyServerProvider+d__78.MoveNext (Dotmim.Sync.Web.Server, Version=0.2.1.0, Culture=neutral, PublicKeyToken=null)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at Dotmim.Sync.Web.Server.WebProxyServerProvider+d__70.MoveNext (Dotmim.Sync.Web.Server, Version=0.2.1.0, Culture=neutral, PublicKeyToken=null)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at Dotmim.Sync.Web.Server.WebProxyServerProvider+d__62.MoveNext (Dotmim.Sync.Web.Server, Version=0.2.1.0, Culture=neutral, PublicKeyToken=null)
Inner exception System.Data.SqlClient.SqlException handled at Dotmim.Sync.CoreProvider+d__95.MoveNext:
at System.Data.SqlClient.SqlConnection.OnError (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Data.SqlClient.TdsParser.TryRun (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Data.SqlClient.SqlDataReader.TryReadInternal (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Data.SqlClient.SqlDataReader.Read (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at Dotmim.Sync.CoreProvider+d__100.MoveNext (Dotmim.Sync.Core, Version=0.2.1.0, Culture=neutral, PublicKeyToken=null)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
at Dotmim.Sync.CoreProvider+d__95.MoveNext (Dotmim.Sync.Core, Version=0.2.1.0, Culture=neutral, PublicKeyToken=null)

Question: For such transient errors, is there a good/recommended way of dealing with them in dotmim.sync?

[Mimetis]

Hello @gentledepp !

Some useful tips:
One thing that can potentially trigger locks, is the options DisableConstraintsOnApplyChanges = true
Not sure, but I think you are not using a recent version where this options was introduced.

Disabling the constraints on a table, during a transaction, will lock the entire table, even if we are doing only read selection.
If you are sure that you won't have any constraints conflicts during a sync, you can remove the constraints disabling.

You can have a look here, where we discussed this point: #430 (comment)

Another thing that can potentially remove some locks is to use ALTER DATABASE ADVWORKS SET READ_COMMITTED_SNAPSHOT ON
(More info here : https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server)

In the last commit of DMS, I have introduced a public virtual IsolationLevel IsolationLevel that you can override to set a default isolation level for each transaction (but once again, not sure you have the last version in production)

The sp_select_changes is only making some select in different tables, but if you are using a custom stored procedure, be sure you are only using seek indexes, to lock only rows and not the full table.

Last thing you can set is to set the nolock option on the tables used in the sp_select_changes. This is equivalent to Read Uncommited but on the stored procedure scope only

I guess we can continue the discussion, these are just basic ideas

[gentledepp]

Thank you for the input. I was on holidays for 2 weeks - so please apoligize the late reply.

Your tips are useful, however only for the latest Dotmim.Sync version which we are still not running on unfortunately.

We did analyze the deadlocks already and they come from the fact that we have a quite complicates custom sp_select changes with additional joins.

So there are essentially only two options for us:

1. use the isolationlevel feature (if we figure out how to do it with the old DMS version) - because the old sync framework used snapshot isolation as well, which should reduce the deadlocks to zero
2. use an interceptor to retry on those "SqlExceptions" that indicate a deadlock.

I just wanted to know, if the new DMS already figured out a better way to do this ;-)

Thanks!