Use SQLite savepoints instead of BEGIN transactions

[amertlich]

Because CRUD operations on on the SQLite database always use the upsert method to update the operations queue

i.e.

azure-mobile-apps/sdk/dotnet/src/Microsoft.Datasync.Client/Offline/Queue/InsertOperation.cs

Line 65 in 8d95576

await store.UpsertAsync(TableName, new[] { item }, false, cancellationToken).ConfigureAwait(false);

This makes it hard to perform bulk operations on the local store that you may want to wrap inside a transactions in your own app, since a BEGIN transaction is used here.

Could SQLite save points be used instead and this would allow app developers to control the transactions usage better as save points support nested transactions.

UPDATE: noticed this was being discussed here: #406 (comment)

[adrianhall]

We’ve taken a look at save points before. I don’t remember the details, but we decided against it because of specific implementation challenges.

Transaction support will get a lot better once we switch over to EF Core on the client. That work is happening now, and I expect the release to be around BUILD next year. It won’t be AT BUILD, but it will be either just before or just after the event.

[amertlich]

Glad to hear transaction support will be better in v8 but I'm not actually needing transactions to ensure atomicity. I need them to facilitate quicker bulk inserts of the data locally, as well as an ability to revert a set of changes locally before they get pushed up to the sever.

To be a little more specific, we have a feature that allows users to import data. This data isn't interdependent and can get uploaded gradually in the background. However, before the user initiates the sync/upload they have a chance to validate the imported data is correct, and if desired, they can revert the import of the data. The problem is that we'd like to use save points to facilitate this, but we cannot do this because internally transactions are being used by the Data sync SDK.

If you switched to using save points for the upsert -- which functionally would be the same as the begin/commit/rollback you have now -- then we could use save points as well as they support nesting.