Transaction per batch. Solving long running write transactions per scope

[slagtejn]

I know I have asked this before and a lot has been done to mitigate the problem of long running transactions/table locks with the read/write enum, but it is still an issue. In the current solution it is not possible to sync a database with 1 scope that contains 10+ gigs of data on SQL server. The transaction becomes so large in SQL server that disk space becomes an issue and if there is a problem where a rollback is needed it will never rollback because of the size. The database will go into recovery mode and become useless.

The issue is there is only 1 write transaction per scope. If a scope contains large amounts of data it's not possible to break into smaller parts. Sync framework has a setting called 'ApplicationTransactionSize' on the sql provider which was able to commit transactions based on the configured KB value. This kept the sql database log file size low and the possibility of the database going into recovery mode low. I know I keep going back to sync framework, but it had features that are needed for enterprise use.

Would it be possible to implement a new setting called 'TransactionPerBatch' so sync can commit in smaller chunks? And then the follow up questions....

• If the scope is able to sync 1/2 the data and then fails, the 'scope_last_server_sync_timestamp' & 'scope_last_sync_timestamp' would not be updated because the scope has not fully synced. On the next sync it will get all data again, but will the already inserted/updated data per batch be considered conflicts?
• Would it be possible to update the 'scope_last_server_sync_timestamp' & 'scope_last_sync_timestamp' so all the data is not downloaded/uploaded again if the scope syncs 1/2 the data and then fails? This will be hard since the batches don't contain this data....

[VagueGit]

It would be nice in the case of a batch failing, if we could switch sync to 1 row per batch to allow the faulting row to be isolated.

If there is already a way to do this, I've missed it.

[slagtejn]

I agree but that is in another discussion and a different issue that you already commented on

#783

[Mimetis]

Indeed, it's answered here : #783 (comment)

[Mimetis]

I'm not a big fan of "chunking" transactions, but we can discuss it.

I will not be able to implement a ApplicationTransactionSize, because it really depends on each provider.
What I can do is pretty similar (and will be easier to implement)

Today, we can fix the max size of each batch file with SyncOptions.BatchSize (default is 5Mo)
A batch file can contain only one table
A table can be contained in multiple batch files (if tables change excess BatchSize)

Do you think it can be a good idea to implements an options fixing the transaction behavior, like

public enum TransactionBehaviour {

   /// Default transaction mode: Everything is rollbacked if any errors occur during apply changes
   AllOrNothing,

   /// Each batch file (configured with BatchSize) has its own transaction
   PerBatch,

   /// No transaction: Really risky if any problems occur during apply changes
   None

}

If you are in a PerBatch or None mode, and a problem occurs, the Timestamp will bot be updated.
So far, the next sync will try to resync some already applied rows, but it will not raise any conflict / error (a part if you are making an update on these rows, manually)

Can it be a solution ? (once again, not a big fan)

[Mimetis]

I made a quick modification of the base code when applying changes to handle a new TransactionMode, that you can set from the SyncOptions instance
If you have a scenario where you can test it and make feedbacks, would be cool.

https://github.com/Mimetis/Dotmim.Sync/tree/TransactionMode

[slagtejn]

Wow that was fast. Yes I will be able to test this and let you know the outcome.

I guess the timestamp per table would be an enhancement later. That would make DMS powerful if not all data needs to be downloaded on failure

[Mimetis]

Yes I've already think about making a timestamp per table, but it would be a major upgrade with a lot of breaking changes.
I guess, it will be the DMS V2 version ;)
For now let's focus on having a V1 version before end of the year ;)

[slagtejn]

I just tested the changes and they work great! It's actually syncing faster, less table locks, and the log file went from 1gb to 28mb when syncing 350mb worth of data. That is a huge difference! Also tested to make sure the records don't conflict if sync stops 1/2 through. This works as well and there are not any conflicts.

[Mimetis]

faster ???
I don't see why it's faster honestly !

Be careful, the TransactionMode branch is not fully tested yet, and I still working on it, please don't use it in production until it's merged in the master branch !
I'm in vacation and I don't have everything on my laptop to fix the bugs on mysql / mariadb for now.

I guess the three majors new features (transaction mode, on error occured, on get command) will be merged in the new version in early september